MrExcel Publishing
Your One Stop for Excel Tips & Solutions

commandbuttons collection?

Posted by John on August 17, 2001 12:55 PM

Hey folks-

I have a worksheet which has rather a lot of commandbuttons on it, and I would like to make them either visible or invisible depending on the user's selection. Unfortunately, I cannot think of a way of doing this short of individually refering to each button...
commandbutton365000.visible = false
commandbutton365001.visible = false, etc.

OK so it's not this bad, but I do have almost 200 buttons (and please don't ask why, it made sense at the time)

If this was on a form, I could just use the controls collection, but this doesn't appear to apply to worksheets. Is there some collection that applies to the commandbuttons so that I could write code like:

For each my_button in commandbuttons
my_button.visible = false
next my_button

Any advice offered would be greatly appreciated.
Thanks in advance


Posted by Robb on August 17, 2001 6:32 PM


Try this:

For Each sh In Worksheets("SheetName").Shapes
sh.Visible = True
Next sh

Any help?


Posted by John on August 20, 2001 6:16 AM

Bingo! Thanks Robb

Like the heading says, "Bingo! Thanks Robb"