Is there any way to use VBA to disable or "gray-out" a command button in a
worksheet that was created from the Forms toolbar?

I tried using

worksheets("sheetName").shapes("cmdName").enabled = false

but I get an "object doesn't support this property or method" example.

I know I can turn the visible property on and off, but I'd rather have it
grayed out when it's not available so it's presence is still displayed.

Is there a way to accomplish this?

Thanks in advance,

Paul

You create buttons from the forms toolbar

worksheets("sheetName").Buttons("cmdName").enabled = false

you create commandbuttons from the Control toolbox toolbar

worksheets("sheetName").OleObjects("cmdName").enab led = false

--
Regards,
Tom Ogilvy


"Paul James" wrote in message
...
Is there any way to use VBA to disable or "gray-out" a command button in a
worksheet that was created from the Forms toolbar?

I tried using

worksheets("sheetName").shapes("cmdName").enabled = false

but I get an "object doesn't support this property or method" example.

I know I can turn the visible property on and off, but I'd rather have it
grayed out when it's not available so it's presence is still displayed.

Is there a way to accomplish this?

Thanks in advance,

Paul

Thanks, Tom.

Yes,

worksheets("sheetName").Buttons("cmdName").enabled = false

works fine, because this button was created from the Forms toolbar.
However, I'm still looking for a way to "gray-out" the button, or provide
some other visual cue when it's disabled. When I only set the enable
property to false, the button's appearance doesn't change in any way, and
the cursor still changes to the white hand when it's placed over the button,
implying that it's active, even though it isn't.

Do you know of a way to provide a visual cue that the button is disabled
without making it invisible?

Manage the caption color yourself.

Sub DisAbleButton()
Dim shpbtn As Shape
Dim bnt As Button
Set btn = ActiveSheet.Buttons("Button 2")
Set shpbtn = ActiveSheet.Shapes("Button 2")
With shpbtn
.ControlFormat.Enabled = False
.TextFrame.Characters( _
Start:=1, Length:=Len(btn.Caption)) _
.Font.ColorIndex = 16
End With
End Sub


Sub EnableButton()
Dim shpbtn As Shape
Dim bnt As Button
Set btn = ActiveSheet.Buttons("Button 2")
Set shpbtn = ActiveSheet.Shapes("Button 2")
With shpbtn
.ControlFormat.Enabled = True
.TextFrame.Characters( _
Start:=1, Length:=Len(btn.Caption)) _
.Font.ColorIndex = xlAutomatic
End With
End Sub
"Paul James" wrote in message
...
Thanks, Tom.

Yes,

worksheets("sheetName").Buttons("cmdName").enabled = false

works fine, because this button was created from the Forms toolbar.
However, I'm still looking for a way to "gray-out" the button, or provide
some other visual cue when it's disabled. When I only set the enable
property to false, the button's appearance doesn't change in any way, and
the cursor still changes to the white hand when it's placed over the
button,
implying that it's active, even though it isn't.

Do you know of a way to provide a visual cue that the button is disabled
without making it invisible?

Paul,

Use this

worksheets("sheetName").oleobjects("cmdName").obje ct.enabled=false


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul James" wrote in message
...
Is there any way to use VBA to disable or "gray-out" a command button in a
worksheet that was created from the Forms toolbar?

I tried using

worksheets("sheetName").shapes("cmdName").enabled = false

but I get an "object doesn't support this property or method" example.

I know I can turn the visible property on and off, but I'd rather have it
grayed out when it's not available so it's presence is still displayed.

Is there a way to accomplish this?

Thanks in advance,

Paul

Thanks for the reply, Bob.

The button I'm working with was created from the Forms toolbar, so I would
need to use the Button object.

I'm still looking for a way to "gray-out" the button, or provide some other
visual cue when it's disabled. Any idea how to accomplish this?


"Bob Phillips" wrote in message
...
Paul,

Use this

worksheets("sheetName").oleobjects("cmdName").obje ct.enabled=false


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul James" wrote in message
...
Is there any way to use VBA to disable or "gray-out" a command button in
a
worksheet that was created from the Forms toolbar?

I tried using

worksheets("sheetName").shapes("cmdName").enabled = false

but I get an "object doesn't support this property or method" example.

I know I can turn the visible property on and off, but I'd rather have
it
grayed out when it's not available so it's presence is still displayed.

Is there a way to accomplish this?

Thanks in advance,

Paul

worksheets("sheetName").Buttons("cmdName").enabled = false

--
Regards,
Tom Ogilvy

"Paul James" wrote in message
...
Thanks for the reply, Bob.

The button I'm working with was created from the Forms toolbar, so I would
need to use the Button object.

I'm still looking for a way to "gray-out" the button, or provide some
other
visual cue when it's disabled. Any idea how to accomplish this?


"Bob Phillips" wrote in message
...
Paul,

Use this

worksheets("sheetName").oleobjects("cmdName").obje ct.enabled=false


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul James" wrote in message
...
Is there any way to use VBA to disable or "gray-out" a command button
in
a
worksheet that was created from the Forms toolbar?

I tried using

worksheets("sheetName").shapes("cmdName").enabled = false

but I get an "object doesn't support this property or method" example.

I know I can turn the visible property on and off, but I'd rather have
it
grayed out when it's not available so it's presence is still
displayed.

Is there a way to accomplish this?

Thanks in advance,

Paul

I'm sorry, Tom, I didn't mean to cause you to have to resend your solution.
I had written a reply to your first message saying that your solution worked
fine, but for some reason that message hasn't shown up on the newsgroup.

I have experimented with the code you sent me:

worksheets("sheetName").Buttons("cmdName").enabled = false

and it does indeed disable the command button. However, it doesn't gray out
the button, or provide any other visual cue that the button is not active.
Moreover, the cursor still changes to the white hand when it moves over the
button, implying that the button is active, even though it isn't.

I'd prefer not to set the visible property to false so you can't see it, and
the ideal would be to have a command that will gray out the text on the
button when it's disabled. Do you know of any way to do this?

Forms toolbar controls have very little flexibility. I don't believe what
you want can be done. You can programmatically change the font color to
gray, but I think you're out of luck with the hand-shaped cursor.

--

Vasant



"Paul James" wrote in message
...
I'm sorry, Tom, I didn't mean to cause you to have to resend your
solution.
I had written a reply to your first message saying that your solution
worked
fine, but for some reason that message hasn't shown up on the newsgroup.

I have experimented with the code you sent me:

worksheets("sheetName").Buttons("cmdName").enabled = false

and it does indeed disable the command button. However, it doesn't gray
out
the button, or provide any other visual cue that the button is not active.
Moreover, the cursor still changes to the white hand when it moves over
the
button, implying that the button is active, even though it isn't.

I'd prefer not to set the visible property to false so you can't see it,
and
the ideal would be to have a command that will gray out the text on the
button when it's disabled. Do you know of any way to do this?

I can't do anything about the hand, but you can manage the caption yourself:

Sub DisAbleButton()
Dim shpbtn As Shape
Dim bnt As Button
Set btn = ActiveSheet.Buttons("Button 2")
Set shpbtn = ActiveSheet.Shapes("Button 2")
With shpbtn
.ControlFormat.Enabled = False
.TextFrame.Characters( _
Start:=1, Length:=Len(btn.Caption)) _
.Font.ColorIndex = 16
End With
End Sub


Sub EnableButton()
Dim shpbtn As Shape
Dim bnt As Button
Set btn = ActiveSheet.Buttons("Button 2")
Set shpbtn = ActiveSheet.Shapes("Button 2")
With shpbtn
.ControlFormat.Enabled = True
.TextFrame.Characters( _
Start:=1, Length:=Len(btn.Caption)) _
.Font.ColorIndex = xlAutomatic
End With
End Sub

--
Regards,
Tom Ogilvy


"Paul James" wrote in message
...
I'm sorry, Tom, I didn't mean to cause you to have to resend your
solution.
I had written a reply to your first message saying that your solution
worked
fine, but for some reason that message hasn't shown up on the newsgroup.

I have experimented with the code you sent me:

worksheets("sheetName").Buttons("cmdName").enabled = false

and it does indeed disable the command button. However, it doesn't gray
out
the button, or provide any other visual cue that the button is not active.
Moreover, the cursor still changes to the white hand when it moves over
the
button, implying that the button is active, even though it isn't.

I'd prefer not to set the visible property to false so you can't see it,
and
the ideal would be to have a command that will gray out the text on the
button when it's disabled. Do you know of any way to do this?