Hide a command button

[jswalsh33]

I have a worksheet with command buttons on it. I would like to hide one or
more of these buttons depending on circumstances.

I have tried the code:
Dim Button_Name As String
Button_Name = "Edit"
With ActiveSheet.Buttons(Button_Name).Visble = False

the code is included in a macro that activates the worksheet in question.

When I run this I get an error message: "Unable to get button properties of
the worksheet class"

I am running Excel 2003.

Help?

Thanks

Jim Walsh

 

[Mike H]

Hi,

Have a look here

http://www.rondebruin.nl/controlsobjectsworksheet.htm

Mike

 

[FSt1]

hi
try something like this
Sub test()
Dim Button_Name As String
Button_Name = "Edit"
If ActiveSheet.Shapes(Button_Name).Visible = True Then
ActiveSheet.Shapes(Button_Name).Visible = False
Else
ActiveSheet.Shapes(Button_Name).Visible = True
End If
End Sub

regards
FSt1

 

[jswalsh33]

hi
try something like this
Sub test()
Dim Button_Name As String
Button_Name = "Edit"
If ActiveSheet.Shapes(Button_Name).Visible = True Then
ActiveSheet.Shapes(Button_Name).Visible = False
Else
ActiveSheet.Shapes(Button_Name).Visible = True
End If
End Sub

regards
FSt1

Thanks for your response.

When I try this I get the error message: "The item with the specified name
wasn't found."

Jim Walsh

 

[Dave Peterson]

There are two very similar buttons that you can place on a worksheet.

One is a button from the Forms toolbar and your code is almost perfect:

Drop the "with" to give this statement:
ActiveSheet.Buttons(Button_Name).Visble = False



The other is a commandbutton from the Control toolbox toolbar.

You should be able to use:
ActiveSheet.OLEObjects(Button_Name).Visible = False

 

[Dave Peterson]

ps. Make sure you spell .visible correctly!

ActiveSheet.Buttons(Button_Name).Visible = False
(added an i)

 

[FSt1]

hi
is your button named "Edit" or is that just the caption on the button. the
code is looking for a name nor a caption.

Regards
FSt1

 

[jswalsh33]

ps. Make sure you spell .visible correctly!

ActiveSheet.Buttons(Button_Name).Visible = False
(added an i)

There are two very similar buttons that you can place on a worksheet.

One is a button from the Forms toolbar and your code is almost perfect:

Drop the "with" to give this statement:
ActiveSheet.Buttons(Button_Name).Visble = False

The other is a commandbutton from the Control toolbox toolbar.

You should be able to use:
ActiveSheet.OLEObjects(Button_Name).Visible = False

Dave,

Thanks for you help.

I am using a command button from the Controls Toolbox.

When I use the code: ActiveSheet.OLEObjects(Button_Name).Visible = False I
get an error message "Unable to get the OLE Objects property of the worksheet
class."

Maybe the name I am using, "Edit" is not the name of the button. "Edit" is
what I typed in on the button face. If that is not the name, where do I find
the name of the button?

Regards,

Jim Walsh

 

[Dave Peterson]

Go into design mode (another icon on that control toolbox toolbar)
Rightclick on the commandbutton to select it.
Look at the namebox (to the left of the formula bar)
Make a note of that name.
Exit design mode (click that button again)

And change the code and test it.

 

[jswalsh33]

Go into design mode (another icon on that control toolbox toolbar)
Rightclick on the commandbutton to select it.
Look at the namebox (to the left of the formula bar)
Make a note of that name.
Exit design mode (click that button again)

And change the code and test it.

Thanks Dave,

The name was the problem.

Jim Walsh