Button on a sheet

[ranswrt]

Is there a way to set a button on a worksheet to enable = false like with a
commandbutton on a userform? Will the text lighten to show that it is
disabled?

 

[Rick Rothstein \(MVP - VB\)]

If the button on the worksheet was an ActiveX button control from the
Control Toolbox toolbar, then you could put this in the UserForm's
CommandButton's Click event...

Worksheets("Sheet1").CommandButton1.Enabled = False

Change the worksheet reference and name of the button on the worksheet to
match your actual conditions.

Rick

 

[ranswrt]

It was put on with the forms toolbar.

If the button on the worksheet was an ActiveX button control from the
Control Toolbox toolbar, then you could put this in the UserForm's
CommandButton's Click event...

Worksheets("Sheet1").CommandButton1.Enabled = False

Change the worksheet reference and name of the button on the worksheet to
match your actual conditions.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I think there is a direct way to do what you want with that type of button,
but this kludge method seems to work as a substitute. Put this code line in
the Click event for the CommandButton on your UserForm...

Worksheets("Sheet1").Shapes("Button 1").TextFrame. _
Characters.Font.ColorIndex = 15

Change the worksheet reference and button name to those for your own
condition. Okay, that will change the button's font color to gray. Now, put
this line as the **FIRST** line in the macro assigned to the button

If Sheet1Shapes("Button 1").TextFrame.Characters. _
Font.ColorIndex = 15 Then Exit Sub

Again, change the worksheet reference and button name to those for your own
condition. If the button's font color is color index 15, the assigned macro
will exit before any code is executed... if the color index is any other
value, the macro code will run normally. This duplicates what happens when a
control is disabled and should suffice for your needs.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I think there is a direct way to do what you want with that type of button,

That was supposed to say "I **DO NOT** think there is a direct way...

Rick

 

[randy]

Thanks I think I will switch the buttons to a control button from the toolbox
toolbar.
That seems alot easier.