View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default disabling a command button

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?