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,

Have a look here

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

Mike

"jswalsh33" wrote:

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

"jswalsh33" wrote:

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

"FSt1" wrote:

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" wrote:

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

Thanks for your response.

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

Jim Walsh

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" wrote:



"FSt1" wrote:

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" wrote:

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

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

jswalsh33 wrote:

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

--

Dave Peterson

ps. Make sure you spell .visible correctly!

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

Dave Peterson wrote:

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

jswalsh33 wrote:

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

--

Dave Peterson

--

Dave Peterson

"Dave Peterson" wrote:

ps. Make sure you spell .visible correctly!

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

Dave Peterson wrote:

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

jswalsh33 wrote:

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

--

Dave Peterson

--

Dave Peterson

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

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 wrote:

"Dave Peterson" wrote:

ps. Make sure you spell .visible correctly!

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

Dave Peterson wrote:

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

jswalsh33 wrote:

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

--

Dave Peterson

--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

"Dave Peterson" wrote:

ps. Make sure you spell .visible correctly!

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

Dave Peterson wrote:

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

jswalsh33 wrote:

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

--

Dave Peterson

--

Dave Peterson

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

Thanks Dave,

The name was the problem.

Jim Walsh