Disable Form button in excel worksheet

[uvaidya]

I have 5 Form buttons on my excelsheet. When Excel launches for the first time, I only want one of them to be active and the remaining to be disabled. After the first button is clicked, the second one should get enabled. And after that, the remaining 3 should get enabled. How can I do this

[tommyt61]

This is assuming you are using Form control buttons and not ActiveX control .


If you are not sure the type you are using:

Form Control buttons will be named Button1 etc
Activex control Buttons will be named CommandButton1 etc

You will need to adjust the name of the buttons in the code to match the ones you have.


Insert New Module and paste the code below.

Private Sub Workbook_Open()

ActiveSheet.Shapes("Button 2").ControlFormat.Enabled = False
ActiveSheet.Shapes("Button 3").ControlFormat.Enabled = False
ActiveSheet.Shapes("Button 4").ControlFormat.Enabled = False
ActiveSheet.Shapes("Button 5").ControlFormat.Enabled = False

End Sub

Put this in the code of the enabled button on startup to turn on button 2

ActiveSheet.Shapes("Button 2").ControlFormat.Enabled = True

now you just add the same code in button 2 code to turn on the last 3

ActiveSheet.Shapes("Button 3").ControlFormat.Enabled = True

ActiveSheet.Shapes("Button 4").ControlFormat.Enabled = True

ActiveSheet.Shapes("Button 5").ControlFormat.Enabled = True

[uvaidya]

Thanks very much. But please let me know how to add these in the 'start up' code. I have added a code that gets executed while I click on the button. But what is a 'start up' code?

[uvaidya]

Also Tommy, if I use this command, the button is not getting disabled. Though the code is NOT getting executed, the button is not getting greyed out

[tommyt61]

Startup code i was referring to is the code that runs in the workbook open event, or when you open the workbook this code runs automatically. you need to open the VB editor and insert a new module and paste the code below.

Private Sub Workbook_Open()ActiveSheet.Shapes("Button 2").ControlFormat.Enabled = FalseActiveSheet.Shapes("Button 3").ControlFormat.Enabled = FalseActiveSheet.Shapes("Button 4").ControlFormat.Enabled = FalseActiveSheet.Shapes("Button 5").ControlFormat.Enabled = FalseEnd Sub

Form control buttons will not gray out when you disable them .... the code just will not run . Command buttons will gray but not form buttons.

you also need to change the buttion names in the code to match the name of your buttons. There is no way i can know .

[uvaidya]

thank you . I will try this out

[tommyt61]

so you can see how this works take a look at this file.

follow these steps
1. open the file. button 1 is the only button that is enabled, but do not click it yet.
2. click on buttons 2,3,4,5 if they were enabled a macro for each button would run and display text saying the button was working.
3. now click on button 1. this now enables button 2 but 3,4,5 are still disabled.
4. click button2 , now 3,4,5 are enabled.


Remember form buttons do not gray out when they are disabled.

[uvaidya]

Thanks very much for your help. I added the below code and it is working

Private Sub Workbook_Open()
Dim btn2 As Button, btn3 As Button
Set btn2 = Worksheets("Sheet1").Buttons("Button 2")
Set btn3 = Worksheets("Sheet1").Buttons("Button 3")
ActiveSheet.Shapes("Button 2").ControlFormat.Enabled = False
ActiveSheet.Shapes("Button 3").ControlFormat.Enabled = False
btn2.Font.ColorIndex = 15
btn3.Font.ColorIndex = 15
End Sub

[mlganser3]

Using MS Excel 2010 - I found this thread because I was having the same problem.
Using the suggestions here, I am still unable to disable Form Controls on a Worksheet. I tried running the example spreadsheet offered above ("Buttons.xlsm") and all buttons were enabled on startup. I've even changed the code in Sub turn_on_345 : ...Button 3... Enabled = False. Result, Button 3 still enabled.
I've manually run the Workbook_Open sub and the buttons are all still enabled. (I'm aware from the posts above that the Form Controls will not be greyed-out when disabled; macros still run on buttons that have been 'Enabled = False' in the code)

In my own code I have the following lines:
Sheets("Main").Buttons("Button 1").Caption = "Step 1: Import Data" 'This works
Sheets("Main").Shapes("Button 1").ControlFormat.Enabled = False 'But this doesn't
Sheets("Main").Buttons("Button 1").Enabled = False 'Nor does this

Therefore I know I can modify the control (by changing the caption), but the button still run macros when clicked.
What's worse, I can't seem to open a Properties Box related to the Form Controls. In the spreadsheet I can only get the properties of the Sheet, nothing else.
I'm at a loss.

[uvaidya]

This is strange as why this will not work in Excel2010.
Did you make sure that "Button 1" is the right name of the button that you are trying to access?
Please attach your excel sheet, so that i can check.