Toggle CheckBox to Hide/Unhide worksheet

[sparky2205]

Hi folks,
I have a Setup WorkSheet in a WorkBook where I dynamically populate a list of the WorkSheet names.
Beside each name I dynamically create a CheckBox.
Currently the user can tick any CheckBox and then click a Hide Worksheets button which runs a macro to hide the selected WorkSheets.
I've been trying to change this so that a user need only tick the CheckBox and the associated WorkSheet will Hide or Unhide without having to click any button.
I have tried a number of things to get this to work but without success.
I'd like to loop through each CheckBox, check its value (TRUE or FALSE) then assign that as the value to the visible property of the associated WorkSheet.
But I don't know where to place this code. It can't be in A CheckBox click event, I think, since there is more than one CheckBox.
I'm including a screenshot of the setup in case this is required.

Any help greatly appreciated.

 

[RoryA]

If they are form checkboxes rather than activex, you can assign the same macro to all of them. Then in the macro you check which checkbox was clicked using Application.Caller and looking at your picture, you can then use TopLeftCell to figure out which row the shape is in, and read the relevant sheet name/visibility.

 

[Micron]

Why don't you just double click on the sheet name and hide it? Much simpler.

 

[sparky2205]

Why don't you just double click on the sheet name and hide it? Much simpler.

Interesting, I'm looking for simplest way possible for the user. But I have no idea how that would work. Could you expand on that a bit?

 

[Micron]

Actually that would not be the way to go if you prefer to uncheck the box and unhide it. If you went with my idea, you'd have to manually unhide, as in right click on a tab and choose unhide option.

EDIT - if you want to play, the syntax would be like
Sheets("" & Range("B13") & "").Visible = False
where B13 holds the sheet name. This is in sheet BeforeDoubleClick event of course. You'd also cancel the double click.

 

[sparky2205]

If they are form checkboxes rather than activex, you can assign the same macro to all of them. Then in the macro you check which checkbox was clicked using Application.Caller and looking at your picture, you can then use TopLeftCell to figure out which row the shape is in, and read the relevant sheet name/visibility.

Thanks RoryA, that works for me.
I have a bit more work to do with it as I want it to do some other stuff as well when I click.
But that's gotten me to where I wanted, the other stuff is just gravy.

 

[sparky2205]

Actually that would not be the way to go if you prefer to uncheck the box and unhide it. If you went with my idea, you'd have to manually unhide, as in right click on a tab and choose unhide option.

EDIT - if you want to play, the syntax would be like
Sheets("" & Range("B13") & "").Visible = False
where B13 holds the sheet name. This is in sheet BeforeDoubleClick event of course. You'd also cancel the double click.

Thanks for your time Micron.
When I get some time I'll check this out.