Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
Ok, here's my answer. This code is for a command button created on a
worksheet to display a userform. When the Userform is displayed the command button associated with the userform is Greyed out. Private Sub CommandButton1_Click() Application.ScreenUpdating = False With Client CommandButton1.Enabled = False Show End With End Sub HTH Charles --- Message posted from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
Tom - the lines that set the Enabled and ColorIndex properties by themselves
give me just about everything I need (except for the white hand on the cursor, which appears to be uncontrollable). What does the TextFrame.Characters method or property do? "Tom Ogilvy" wrote in message ... 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? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
I have a question, as well. I see that Tom is having to refer to the same
button in two ways - as a shape and as a button - in order to get at different properties. Is there some bigger context/explanation that could help one remember the distinction, i.e., what are the properties of a shapes(button) versus those of a button? Thanks, Doug "Tom Ogilvy" wrote in message ... 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? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
Well, you don't have to go with both objects. This works as well:
Sub EnableButton(btn As Button) With btn .Enabled = True .Characters(Start:=1, Length:=Len(.Caption)) _ .Font.ColorIndex = xlAutomatic End With End Sub Sub DisableButton(btn As Button) With btn .Enabled = False .Characters(Start:=1, Length:=Len(.Caption)) _ .Font.ColorIndex = 16 End With End Sub Sub BtnToggle() Dim btn As Button Set btn = ActiveSheet.Buttons("Button 2") If btn.Enabled Then DisableButton btn Else EnableButton btn End If End Sub Sub btnMsg() Dim btn As Button Set btn = ActiveSheet.Buttons( _ Application.Caller) MsgBox btn.Name & " has a caption of " & _ btn.Caption End Sub Sub SetOnAction() ActiveSheet.Buttons("Button 3") _ .OnAction = "btnToggle" ActiveSheet.Buttons("Button 2") _ .OnAction = "btnMsg" End Sub -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... I have a question, as well. I see that Tom is having to refer to the same button in two ways - as a shape and as a button - in order to get at different properties. Is there some bigger context/explanation that could help one remember the distinction, i.e., what are the properties of a shapes(button) versus those of a button? Thanks, Doug "Tom Ogilvy" wrote in message ... 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? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
this is all one line:
.TextFrame.Characters( _ Start:=1, Length:=Len(btn.Caption)) _ .Font.ColorIndex = 16 But for a simpler implementation, see my answer to Doug in this thread. -- Regards, Tom Ogilvy "Paul James" wrote in message ... Tom - the lines that set the Enabled and ColorIndex properties by themselves give me just about everything I need (except for the white hand on the cursor, which appears to be uncontrollable). What does the TextFrame.Characters method or property do? "Tom Ogilvy" wrote in message ... 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? |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
disabling a command button
Tom,
Thanks, you've relieved my syntactic burden. Doug "Tom Ogilvy" wrote in message ... Well, you don't have to go with both objects. This works as well: Sub EnableButton(btn As Button) With btn .Enabled = True .Characters(Start:=1, Length:=Len(.Caption)) _ .Font.ColorIndex = xlAutomatic End With End Sub Sub DisableButton(btn As Button) With btn .Enabled = False .Characters(Start:=1, Length:=Len(.Caption)) _ .Font.ColorIndex = 16 End With End Sub Sub BtnToggle() Dim btn As Button Set btn = ActiveSheet.Buttons("Button 2") If btn.Enabled Then DisableButton btn Else EnableButton btn End If End Sub Sub btnMsg() Dim btn As Button Set btn = ActiveSheet.Buttons( _ Application.Caller) MsgBox btn.Name & " has a caption of " & _ btn.Caption End Sub Sub SetOnAction() ActiveSheet.Buttons("Button 3") _ .OnAction = "btnToggle" ActiveSheet.Buttons("Button 2") _ .OnAction = "btnMsg" End Sub -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... I have a question, as well. I see that Tom is having to refer to the same button in two ways - as a shape and as a button - in order to get at different properties. Is there some bigger context/explanation that could help one remember the distinction, i.e., what are the properties of a shapes(button) versus those of a button? Thanks, Doug "Tom Ogilvy" wrote in message ... 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? |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
thanks to Tom and the other contributors to this thread
Tom - thanks so much for providing a way to control the button's Enabled
property and font color so thoroughly. Thanks also the all the other contributors to this subject: Bob, Doug, Charles and Vasant. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disabling the minimise button in Excel workbook | Excel Programming | |||
Command Button vs Control Button | Excel Programming | |||
disabling cells based on cmd button | Excel Programming | |||
Disabling font color button | Excel Programming | |||
Command Button vs Form Button | Excel Programming |