mercredi 5 septembre 2018

Dynamically Checking for Checkboxes with the same name as Worksheets -VBA Excel

I am on excel, I have about 30 UserForm checkboxes, each of the checkboxes are located on a page called "Summary" in a big column. Each of which corresponds to a worksheet. I am attempting to loop through these checkboxes to see which are ticked and not ticked. I have put some code within a loop of all worksheets, this is the bit that is not working:

...
If cbtrue = True Then
If ActiveWorkbook.Sheets("Summary").Shapes("ws.Name").Value = False Then
                ClearContent = True
End If
...

cbtrue is just a variable to see if the checkbox exists, hence if it does it will then go to the if statement, at which point it will determine whether that checkbox is ticked or not, depending on this it saves the ClearContent variable (which I use later on in the code).

The problem is when it comes to 'Shapes("ws.Name")', ws.name is simply the name of the worksheet on each loop. So on the first round of the loop, it will be "Summary"... However, I think it is physically searching for the sheet "ws.name" which obviously doesn't exist. I have tried removing it from the quotation marks and also various other methods such as 'Checkboxes("ws.Name")' but it seems they all have the same problem.

I am posting to see if someone could offer me another method, or perhaps show me where I have gone wrong, as I think I am not fully understanding the syntax.

Any Help is appreciated. Thanks in advance :)




Aucun commentaire:

Enregistrer un commentaire