disable pushbutton on spreadsheet

[Robert Crandal]

Is it possible to disable a pushbutton that is placed on
top of my spreadsheet?? Basically, if the button is
pressed I will display my userform object. If the
userform is visible/loaded, I want the button to be
disabled. Then, when the userform is closed out
I want to re-enable the button again.

thank u

 

[Dave Peterson]

I had this in a General module:

Option Explicit
Sub testme()
UserForm1.Show False
End Sub

This was the macro that I assigned to the button from the Forms toolbar. The
button was on Sheet1 and named "Button 1".

If you used a commandbutton from the control toolbox toolbar, then this code was
behind the worksheet that held that commandbutton:

Option Explicit
Private Sub CommandButton1_Click()
UserForm1.Show False
End Sub

The name of this button was "CommandButton1".


I didn't know what kind of button you used on the sheet.

Then my userform had a commandbutton that would cancel the userform. This was
named Commandbutton2.

This is the code that was behind the Userform.

Option Explicit
Private Sub CommandButton2_Click()
'cancel button on the userform
ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = True
ThisWorkbook.Worksheets("Sheet1").CommandButton1.Enabled = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = False
ThisWorkbook.Worksheets("Sheet1").CommandButton1.Enabled = False
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call CommandButton2_Click
End If
End Sub

Delete the pair of lines (commandbutton or "button 1") depending on what kind of
button you used.
Remember to change the sheetname and the button/commandbutton to match what you
need.

ps. When the commandbutton was disabled, it looked disabled. The button from
the Forms toolbar didn't look as nice. (You could change the code to hide/show
it or even change the colors/caption if that was a problem.)

 

[OssieMac]

Hi Robert,

I have assumed that you are using an AxtiveX command button from the
Controls Toolbox toolbar. (not a button from the Forms toolbar.)

Also assumed that the form is Modeless otherwise the button is not
accessable while the form is open anyway.

Use the following code for the command button.

Private Sub CommandButton1_Click()
Me.CommandButton1.Enabled = False
UserForm1.Show vbModeless
End Sub

Insert the following code in the forms code module.

Private Sub UserForm_Terminate()
'Edit "Sheet1" to your worksheet name
Sheets("Sheet1").CommandButton1.Enabled = True
End Sub

 

[Robert Crandal]

I do not use the CommandButton1 on my Sheet1. I am just using a regular
push button on top of my sheet which has the caption of "Button 1".

I tried using your code below, but it did not seem to work. I got a
subscript out of range error. Does my button caption need to be

"button 1" or "button_1" or something else?? I don't know what I'm
doing wrong.

thank you

 

[OssieMac]

Hi again Robert,

If you are using a forms button then you will probably be better to use
Dave's suggestion to make the button not visible because disabling a forms
button does not change its looks like graying out the caption.

If you use the following code for your Forms button click then it will hide
the button when it shows the userform.

Sub Button1_Click()

'Edit "Sheet1" to your worksheet name
ThisWorkbook.Worksheets("Sheet1") _
.Buttons("Button 1").Visible = False

UserForm1.Show vbModeless
End Sub


Then insert the following code in the forms code area and the button will be
visible again when the form is closed.

Private Sub UserForm_Terminate()

'Edit "Sheet1" to your worksheet name
ThisWorkbook.Worksheets("Sheet1") _
.Buttons("Button 1").Visible = True

End Sub

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

 

[Dave Peterson]

Is the code in the same workbook as the button?

Is the name of the sheet really Sheet1?

Did you assign the macro to show the userform to the correct button?

If this doesn't help, you may want to share the code you tried, where you put it
and the names of those objects.

 

[Dave Peterson]

Ps. It's not the caption of the button that's important to the code. It's the
name of the button that's important.

Select the button (right click on it should do fine).

Then look in the Namebox (to the left of the formula bar) to see the name that
the code has to use.

pps. I'd change the caption to something meaningful the user would understand.