Hide a command button

  • Thread starter Thread starter jswalsh33
  • Start date Start date
J

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
 
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
 
FSt1 said:
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
Click to expand...

Thanks for your response.

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

Jim Walsh
 
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
 
ps. Make sure you spell .visible correctly!

ActiveSheet.Buttons(Button_Name).Visible = False
(added an i)
 
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
 
Dave Peterson said:
ps. Make sure you spell .visible correctly!

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

Dave said:
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
Click to expand...
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
Click to expand...
 
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.
 
Dave Peterson said:
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.
Click to expand...
Thanks Dave,

The name was the problem.

Jim Walsh
 
Back
Top