Forms Toolbar Button question

[reddog9069]

I have a button on my worksheet created from the forms toolbar.

How do I access the button through VBA?

sheets(1).Item???

Also I would just like to make it invisible and/or disable it.
Which property would I use?

Thanks

 

[optionbase1]

the button you have created is a Shape and can be referenced as such.
(see Excel Help for Shapes collection to find the available properties.
(your code is in general :

Sheet(1).Shapes("button1").

i.e. to set as 'not visible'

ActiveSheet.Shapes("Button 1").Visible = False

not sure that you can disable these shapes but you could handle this
requirement in the macro that is called via the 'OnAction' property

You could also create a button from the Control Toolbox toolbar; in
this case you would have all of the functionality but with the benefits
of being able to directly access the properties associated with a contol
rather than the slightly more complex code associated with a shape.
These Controls are also locked when not in Design Mode so that you can
prevent user changes (to formatting etc). these Controls do have an
Enabled property that can be set to false.

find the Controls Toolbox by displaying the Visual Basic Toolbar and
click on the Control Toolbox button (or other routes...).

Control Buttons on worksheets can be seen as a bit of a hammer to crack
a nut but the access to properties and events is generally useful.
Rather than assigning a macro to run when the button is clicked
{onAction}, right-click on the button in Design Mode and select Code
from the menu; this displays the default event ( _Click) - place your
code here; or call another routine using the Call statement ...

Have fun.

 

[JE McGimpsey]

One way:

To access the button:

Sheets(1).Buttons("myButton").Caption = "My Caption"

Invisible:

Sheets(1).Buttons("myButton").Visible = False

Disabled:

Sheets(1).Buttons("myButton").Enabled = False

 

[reddog9069]

Thanks for all your help

 

[optionbase1]

thanks for that (JE McGimpsey); I didn't know that object existed & when
I search for the object in Help it shows :

"Hidden Language Element
You have requested Help for a language element that is hidden, and
therefore unavailable for programmatic access. "

any idea why?

Are there other objects that are 'hidden' in this way and if so how do
I access information on them and their properties / methods / events?

Thanks for the help anyway!