Command Button Invisible

[JR_06062005]

Is there a way to make a command button invisible and/or disable it? This
can be done in Access, but if it can be done in Excel, I don't know how to do
it.

[Harald Staff]

Sure, commandbuttons have an Enabled property and a Visible property, both
can be set to False.

HTH. Best wishes Harald

"JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> Is there a way to make a command button invisible and/or disable it? This
> can be done in Access, but if it can be done in Excel, I don't know how to
do
> it.

[JR_06062005]

I think I didn’t phrase my question correctly. I did not want a yes/no
answer as I had already suspected there was a way to do this, but I don’t
know how to identify the command button object. I suppose the code is
something like:

Commandbutton(button identifier).visible=false ‘to make invisible
Commandbutton(button identifier).enable=false ‘to disable

I certainly would appreciate a little more help.


"Harald Staff" wrote:

> Sure, commandbuttons have an Enabled property and a Visible property, both
> can be set to False.
>
> HTH. Best wishes Harald
>
> "JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
> news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> > Is there a way to make a command button invisible and/or disable it? This
> > can be done in Access, but if it can be done in Excel, I don't know how to
> do
> > it.
>
>
>

[Tom Ogilvy]

Activesheet.Commandbutton1.Visible = False
Activesheet.OleObjects("Commandbutton1").Visible = False

on a userform

Userform1.Commandbutton1.Visible = False
Userform1.Controls("CommandButton1").visible = False

--
Regards,
Tom Ogilvy


"JR_06062005" wrote:

> I think I didn’t phrase my question correctly. I did not want a yes/no
> answer as I had already suspected there was a way to do this, but I don’t
> know how to identify the command button object. I suppose the code is
> something like:
>
> Commandbutton(button identifier).visible=false ‘to make invisible
> Commandbutton(button identifier).enable=false ‘to disable
>
> I certainly would appreciate a little more help.
>
>
> "Harald Staff" wrote:
>
> > Sure, commandbuttons have an Enabled property and a Visible property, both
> > can be set to False.
> >
> > HTH. Best wishes Harald
> >
> > "JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
> > news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> > > Is there a way to make a command button invisible and/or disable it? This
> > > can be done in Access, but if it can be done in Excel, I don't know how to
> > do
> > > it.
> >
> >
> >

[JR_06062005]

The code doesn't work. For the first set of code I get the message "Object
does not support this property or method" and for the second line of the
first set the message is "Unable to get OLE Object property of the worksheet
class"

For second set I get this message for the first line "Variable Not Defined"
and it indicates userform1 and for the second line I get the same message.
As far as I know I have not created a form, only buttons using the Forms Menu.
"Tom Ogilvy" wrote:

> Activesheet.Commandbutton1.Visible = False
> Activesheet.OleObjects("Commandbutton1").Visible = False
>
> on a userform
>
> Userform1.Commandbutton1.Visible = False
> Userform1.Controls("CommandButton1").visible = False
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JR_06062005" wrote:
>
> > I think I didn’t phrase my question correctly. I did not want a yes/no
> > answer as I had already suspected there was a way to do this, but I don’t
> > know how to identify the command button object. I suppose the code is
> > something like:
> >
> > Commandbutton(button identifier).visible=false ‘to make invisible
> > Commandbutton(button identifier).enable=false ‘to disable
> >
> > I certainly would appreciate a little more help.
> >
> >
> > "Harald Staff" wrote:
> >
> > > Sure, commandbuttons have an Enabled property and a Visible property, both
> > > can be set to False.
> > >
> > > HTH. Best wishes Harald
> > >
> > > "JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
> > > news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> > > > Is there a way to make a command button invisible and/or disable it? This
> > > > can be done in Access, but if it can be done in Excel, I don't know how to
> > > do
> > > > it.
> > >
> > >
> > >

[Tom Ogilvy]

> Is there a way to make a **command button** invisible and/or disable it?

Emphasis added.

All I can offer is code that has been tested in the proper context based on
the question asked. (such as in this case).

the button from the forms menu is not a commandbutton. It is simply a
button. If you can't ask the correct question, it would be best not to come
back in an accusatory fashion exlaiming that the code doesn't work.

Buttons from the forms toolbar are members of the buttons collection

Activesheet.Buttons("Button 1").visible = False

they are also members of the Shapes collection, but more than buttons can be
members of this collection. No, they are not members of the OleObjects
collection, but commandbuttons use OleObjects as containers, so if you had
been using a commandbutton as you stated, and the name of the commandbutton
was commandbutton1, or you were able to recognize that you should actually
use the real name of the button, then you would not have had such a problem.
as to useforms, I provided that since you did not state where you were using
the "commandbutton" and I decided to give you as complete an answer as
possible.

here is some code demo's from the immediate window in attempt to assure you
that in the proper context, the recommended code does work:

activesheet.buttons("Button 2").Visible = False
? activesheet.Buttons("button 2").Visible
False
activesheet.buttons("button 2").visible = True
? activesheet.buttons("button 2").visible
True

--
Regards,
Tom Ogilvy




"JR_06062005" wrote:

> The code doesn't work. For the first set of code I get the message "Object
> does not support this property or method" and for the second line of the
> first set the message is "Unable to get OLE Object property of the worksheet
> class"
>
> For second set I get this message for the first line "Variable Not Defined"
> and it indicates userform1 and for the second line I get the same message.
> As far as I know I have not created a form, only buttons using the Forms Menu.
> "Tom Ogilvy" wrote:
>
> > Activesheet.Commandbutton1.Visible = False
> > Activesheet.OleObjects("Commandbutton1").Visible = False
> >
> > on a userform
> >
> > Userform1.Commandbutton1.Visible = False
> > Userform1.Controls("CommandButton1").visible = False
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "JR_06062005" wrote:
> >
> > > I think I didn’t phrase my question correctly. I did not want a yes/no
> > > answer as I had already suspected there was a way to do this, but I don’t
> > > know how to identify the command button object. I suppose the code is
> > > something like:
> > >
> > > Commandbutton(button identifier).visible=false ‘to make invisible
> > > Commandbutton(button identifier).enable=false ‘to disable
> > >
> > > I certainly would appreciate a little more help.
> > >
> > >
> > > "Harald Staff" wrote:
> > >
> > > > Sure, commandbuttons have an Enabled property and a Visible property, both
> > > > can be set to False.
> > > >
> > > > HTH. Best wishes Harald
> > > >
> > > > "JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
> > > > news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> > > > > Is there a way to make a command button invisible and/or disable it? This
> > > > > can be done in Access, but if it can be done in Excel, I don't know how to
> > > > do
> > > > > it.
> > > >
> > > >
> > > >

[Dave Peterson]

When you wrote command button, the responders thought that you meant a command
button from the Control toolbox (or a button on a userform).

But if you use the button from the Forms toolbar, then you could use:

activesheet.buttons("button 1").visible = false 'true
activesheet.buttons("button 1").enabled = false 'true

Change "Button 1" to the name you need.



JR_06062005 wrote:
>
> The code doesn't work. For the first set of code I get the message "Object
> does not support this property or method" and for the second line of the
> first set the message is "Unable to get OLE Object property of the worksheet
> class"
>
> For second set I get this message for the first line "Variable Not Defined"
> and it indicates userform1 and for the second line I get the same message.
> As far as I know I have not created a form, only buttons using the Forms Menu.
> "Tom Ogilvy" wrote:
>
> > Activesheet.Commandbutton1.Visible = False
> > Activesheet.OleObjects("Commandbutton1").Visible = False
> >
> > on a userform
> >
> > Userform1.Commandbutton1.Visible = False
> > Userform1.Controls("CommandButton1").visible = False
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "JR_06062005" wrote:
> >
> > > I think I didn’t phrase my question correctly. I did not want a yes/no
> > > answer as I had already suspected there was a way to do this, but I don’t
> > > know how to identify the command button object. I suppose the code is
> > > something like:
> > >
> > > Commandbutton(button identifier).visible=false ‘to make invisible
> > > Commandbutton(button identifier).enable=false ‘to disable
> > >
> > > I certainly would appreciate a little more help.
> > >
> > >
> > > "Harald Staff" wrote:
> > >
> > > > Sure, commandbuttons have an Enabled property and a Visible property, both
> > > > can be set to False.
> > > >
> > > > HTH. Best wishes Harald
> > > >
> > > > "JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
> > > > news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> > > > > Is there a way to make a command button invisible and/or disable it? This
> > > > > can be done in Access, but if it can be done in Excel, I don't know how to
> > > > do
> > > > > it.
> > > >
> > > >
> > > >

--

Dave Peterson

[JR_06062005]

I didn't mean to be accusatory. and I do appreciate your help. I guess I
didn't realize how my reply came across. And you are exactly right I didn't
know how to ask the question.

If I understand your answer, I should use the following code :

Activesheet.Buttons("Button 1").visible = False

If that is the case, I still can't get the code to work. The error message
I get is "Unable to get buttons property of the worksheet class" Is there
something I need to configure with VP so that it can get this property or
perhaps, it may be where the code is placed. That is, does it have to be
placed in the worksheet?
Tom Ogilvy" wrote:

> > Is there a way to make a **command button** invisible and/or disable it?
>
> Emphasis added.
>
> All I can offer is code that has been tested in the proper context based on
> the question asked. (such as in this case).
>
> the button from the forms menu is not a commandbutton. It is simply a
> button. If you can't ask the correct question, it would be best not to come
> back in an accusatory fashion exlaiming that the code doesn't work.
>
> Buttons from the forms toolbar are members of the buttons collection
>
> Activesheet.Buttons("Button 1").visible = False
>
> they are also members of the Shapes collection, but more than buttons can be
> members of this collection. No, they are not members of the OleObjects
> collection, but commandbuttons use OleObjects as containers, so if you had
> been using a commandbutton as you stated, and the name of the commandbutton
> was commandbutton1, or you were able to recognize that you should actually
> use the real name of the button, then you would not have had such a problem.
> as to useforms, I provided that since you did not state where you were using
> the "commandbutton" and I decided to give you as complete an answer as
> possible.
>
> here is some code demo's from the immediate window in attempt to assure you
> that in the proper context, the recommended code does work:
>
> activesheet.buttons("Button 2").Visible = False
> ? activesheet.Buttons("button 2").Visible
> False
> activesheet.buttons("button 2").visible = True
> ? activesheet.buttons("button 2").visible
> True
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "JR_06062005" wrote:
>
> > The code doesn't work. For the first set of code I get the message "Object
> > does not support this property or method" and for the second line of the
> > first set the message is "Unable to get OLE Object property of the worksheet
> > class"
> >
> > For second set I get this message for the first line "Variable Not Defined"
> > and it indicates userform1 and for the second line I get the same message.
> > As far as I know I have not created a form, only buttons using the Forms Menu.
> > "Tom Ogilvy" wrote:
> >
> > > Activesheet.Commandbutton1.Visible = False
> > > Activesheet.OleObjects("Commandbutton1").Visible = False
> > >
> > > on a userform
> > >
> > > Userform1.Commandbutton1.Visible = False
> > > Userform1.Controls("CommandButton1").visible = False
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "JR_06062005" wrote:
> > >
> > > > I think I didn’t phrase my question correctly. I did not want a yes/no
> > > > answer as I had already suspected there was a way to do this, but I don’t
> > > > know how to identify the command button object. I suppose the code is
> > > > something like:
> > > >
> > > > Commandbutton(button identifier).visible=false ‘to make invisible
> > > > Commandbutton(button identifier).enable=false ‘to disable
> > > >
> > > > I certainly would appreciate a little more help.
> > > >
> > > >
> > > > "Harald Staff" wrote:
> > > >
> > > > > Sure, commandbuttons have an Enabled property and a Visible property, both
> > > > > can be set to False.
> > > > >
> > > > > HTH. Best wishes Harald
> > > > >
> > > > > "JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
> > > > > news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> > > > > > Is there a way to make a command button invisible and/or disable it? This
> > > > > > can be done in Access, but if it can be done in Excel, I don't know how to
> > > > > do
> > > > > > it.
> > > > >
> > > > >
> > > > >

[Tom Ogilvy]

Maybe you are using the wrong name. By default, these controls usually have
a space in the name.

to get information about your buttons, with your sheet as the activesheet:
Place this in a general module (insert=>Module in the VBE).

Sub Showbuttons()
Dim btn As Button
For Each btn In ActiveSheet.Buttons
sCell = btn.TopLeftCell.Address
If Intersect(btn.TopLeftCell, ActiveWindow.VisibleRange) Is Nothing Then
Application.Goto btn.TopLeftCell, Scroll:=True
End If
btn.Select
MsgBox "Name: " & btn.Name & vbNewLine & _
"Over cell: " & sCell & vbNewLine & _
"Caption: " & btn.Caption
Next
End Sub


Just a utility to play with. Use this in the general module of a new
workbook.

Sub AddAButton()
Dim sh As Worksheet, rng As Range
Dim btn As Button
Set sh = ActiveSheet
sh.Buttons.Delete
Set rng = sh.Range("B9")
With rng
Set btn = ActiveSheet.Buttons.Add( _
Top:=rng.Top, Left:=rng.Left, _
Width:=rng.Width, Height:=rng.Height)
End With
Range("A1").Select
MsgBox "Button " & btn.Name & _
" should be visible"
btn.Visible = False
MsgBox "Button " & btn.Name & _
" should be hidden"
sh.Buttons(btn.Name).Visible = True
End Sub

--
Regards,
Tom Ogilvy


"JR_06062005" wrote:

> I didn't mean to be accusatory. and I do appreciate your help. I guess I
> didn't realize how my reply came across. And you are exactly right I didn't
> know how to ask the question.
>
> If I understand your answer, I should use the following code :
>
> Activesheet.Buttons("Button 1").visible = False
>
> If that is the case, I still can't get the code to work. The error message
> I get is "Unable to get buttons property of the worksheet class" Is there
> something I need to configure with VP so that it can get this property or
> perhaps, it may be where the code is placed. That is, does it have to be
> placed in the worksheet?
> Tom Ogilvy" wrote:
>
> > > Is there a way to make a **command button** invisible and/or disable it?
> >
> > Emphasis added.
> >
> > All I can offer is code that has been tested in the proper context based on
> > the question asked. (such as in this case).
> >
> > the button from the forms menu is not a commandbutton. It is simply a
> > button. If you can't ask the correct question, it would be best not to come
> > back in an accusatory fashion exlaiming that the code doesn't work.
> >
> > Buttons from the forms toolbar are members of the buttons collection
> >
> > Activesheet.Buttons("Button 1").visible = False
> >
> > they are also members of the Shapes collection, but more than buttons can be
> > members of this collection. No, they are not members of the OleObjects
> > collection, but commandbuttons use OleObjects as containers, so if you had
> > been using a commandbutton as you stated, and the name of the commandbutton
> > was commandbutton1, or you were able to recognize that you should actually
> > use the real name of the button, then you would not have had such a problem.
> > as to useforms, I provided that since you did not state where you were using
> > the "commandbutton" and I decided to give you as complete an answer as
> > possible.
> >
> > here is some code demo's from the immediate window in attempt to assure you
> > that in the proper context, the recommended code does work:
> >
> > activesheet.buttons("Button 2").Visible = False
> > ? activesheet.Buttons("button 2").Visible
> > False
> > activesheet.buttons("button 2").visible = True
> > ? activesheet.buttons("button 2").visible
> > True
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "JR_06062005" wrote:
> >
> > > The code doesn't work. For the first set of code I get the message "Object
> > > does not support this property or method" and for the second line of the
> > > first set the message is "Unable to get OLE Object property of the worksheet
> > > class"
> > >
> > > For second set I get this message for the first line "Variable Not Defined"
> > > and it indicates userform1 and for the second line I get the same message.
> > > As far as I know I have not created a form, only buttons using the Forms Menu.
> > > "Tom Ogilvy" wrote:
> > >
> > > > Activesheet.Commandbutton1.Visible = False
> > > > Activesheet.OleObjects("Commandbutton1").Visible = False
> > > >
> > > > on a userform
> > > >
> > > > Userform1.Commandbutton1.Visible = False
> > > > Userform1.Controls("CommandButton1").visible = False
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "JR_06062005" wrote:
> > > >
> > > > > I think I didn’t phrase my question correctly. I did not want a yes/no
> > > > > answer as I had already suspected there was a way to do this, but I don’t
> > > > > know how to identify the command button object. I suppose the code is
> > > > > something like:
> > > > >
> > > > > Commandbutton(button identifier).visible=false ‘to make invisible
> > > > > Commandbutton(button identifier).enable=false ‘to disable
> > > > >
> > > > > I certainly would appreciate a little more help.
> > > > >
> > > > >
> > > > > "Harald Staff" wrote:
> > > > >
> > > > > > Sure, commandbuttons have an Enabled property and a Visible property, both
> > > > > > can be set to False.
> > > > > >
> > > > > > HTH. Best wishes Harald
> > > > > >
> > > > > > "JR_06062005" <JR06062005@discussions.microsoft.com> skrev i melding
> > > > > > news:24A8991B-85F8-423B-A77C-18F29BF6BE33@microsoft.com...
> > > > > > > Is there a way to make a command button invisible and/or disable it? This
> > > > > > > can be done in Access, but if it can be done in Excel, I don't know how to
> > > > > > do
> > > > > > > it.
> > > > > >
> > > > > >
> > > > > >