Userform checkbox caption to hide work sheet

Hi All,

I'm finding it difficult to get the correct syntax for referencing a userform checkbox caption to hide a worksheet. The userform initializes with checkboxes, each one captioned from each sheet name in the workbook. The user checks the sheets they would like to use in the next step of the process and leaves sheets they would like hidden, unchecked. When the command button on the userform is pressed to continue, my code should populate a list on a new sheet called Email Data and hide those sheets that remain unchecked.

The code so far works perfectly except for the part that should hide the unwanted sheets!

My code is this with the problem code highlighted red:

Please Login or Register  to view this content.

You can see that I am tyring to use the checkbox caption variable as the sheet name so that the worksheet can be hidden in the active workbook but this line errors out no matter what combination I use. I've googled extensively but have not been able to find anything to assist me. Does anyone have an idea what I am doing wrong?

Many thanks!

What error(s) are you getting?

How are you setting the captions of the checkboxes?

PS Have you considered using a listbox to list the sheet names? If you used one it could be set up so each row has a checkbox.

Hi Norie, thanks for the reply.

I'm getting Object doesn't support this property or method. I guessed this was to do with the way I was referencing the checkbox caption.

The checkbox captions are set up with this code:

Please Login or Register  to view this content.

1 To LastRow is a list of usernames. Each username has a corresponding sheet named the same.

If there is no way to adjust my original code then I will investigate the listbox method but it's taken me three days to get this far! I am not familiar with listboxes yet but will get to know them if I have to!

Well, sometimes its right under your nose. I substituted Hidden for Visible and it worked!

Please Login or Register  to view this content.

Norie, thanks for the hint about listboxes. I will explore this one day!

Thanks,

I think the problem isn't really with the checkboxes, more likely to be because worksheets don't have a Hidden property.

Try this.

Please Login or Register  to view this content.

PS If you add a listbox to the userform this is how you could populate it and set it up to have checkboxes for each row.

Please Login or Register  to view this content.

Hi Norie! Got it at the same time!

Big thanks for the start up code for listboxes for me as well. This is really helpful and much appreciated!