disable pushbutton on spreadsheet
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.
--
Regards,
OssieMac
"Robert Crandal" wrote:
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
"Dave Peterson" wrote in message
...
Option Explicit
Private Sub CommandButton2_Click()
'cancel button on the userform
ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
ThisWorkbook.Worksheets("sheet1").Buttons("button 1").Enabled = False
End Sub
.
|