Disable Form button in excel worksheet

uvaidya

New member
Joined
Aug 10, 2012
Messages
6
Reaction score
0
Points
0
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

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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.

Code:
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
Code:
ActiveSheet.Shapes("Button 2").ControlFormat.Enabled = True


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

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

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

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

uvaidya

New member
Joined
Aug 10, 2012
Messages
6
Reaction score
0
Points
0
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

New member
Joined
Aug 10, 2012
Messages
6
Reaction score
0
Points
0
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

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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.
Code:
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 .
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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.
 

Attachments

  • Buttons.xlsm
    18.4 KB · Views: 2,017

uvaidya

New member
Joined
Aug 10, 2012
Messages
6
Reaction score
0
Points
0
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

New member
Joined
Jan 8, 2013
Messages
1
Reaction score
0
Points
0
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

New member
Joined
Aug 10, 2012
Messages
6
Reaction score
0
Points
0
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.
 

bk11

New member
Joined
Apr 7, 2013
Messages
1
Reaction score
0
Points
0
Appears not to work in Excel 2013 either

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.

Has anyone tried this successfully in Excel 2013 (I run the 64-bit version and cannot get it to work)
 

cwj73

New member
Joined
Jan 4, 2014
Messages
1
Reaction score
0
Points
0
I've been looking for this solution for the past two days and it's proving to be more difficult that expected. Generally I would use PHP and MySQL for such a task but I've had specific instruction to use this Excel stuff. Now before I continue, I've tried this and many, many other options to get the buttons to disable after a cell value reaches 0. Each time the button is pressed the value decreases by 1 and then I'd prefer it to stop at 0, disabling the button so it is unable to continue.
This is so easy to write in PHP, so why have Microsoft made VBS, macros and excel so inconsistent with their versions of Office. Some code entries only work with Office 03, other 07, then 10 and 13 have different code accepted. I originally started the project using MacOffice who have since completely removed the option for VBS in Excel due to Microsoft's inconsistency of the program.

So due to the fact that this site is the only one to have members that recognize their are differences between the versions of Excel, I'm hoping that I could ask for some assistance now I've been out and purchased a disposable PC running Windows 7 Ultimate and Office 10 and Visual Basic for Application 7.0. I've been toying with both Form Buttons and ActiveX Buttons (why there are two I've no idea other than to make it more complex).

These are the codes:
Sub Hat()
Range ("Sheet2!$G$4") = Range ("Sheet2!$G$4") -1
Range ("Sheet2!$H$4") = Range ("Sheet2!$H$4") + Range ("Sheet2!$E$4").Value
If Range ("Sheet2!$G$4").Value=1 Then
MsgBox "Order More Hats!"
End If
If Range ("Sheet2!$G$4").Value=0 Then
****Problem Area****
ActiveSheet.Sheet1.Hat.Enabled=False
*******************
End If
End Sub

Error Notice
Run-Time Error '438'
Object doesn't support this property or method

I've attempted numerous different ways to write this code but nothing will disable the button once the value reaches 0

ActiveX I'm a little unsure about so code may be totally wrong

Private Sub CommandButton1_Click()
****Problem Area****
Range ("Sheet2!$G$4").Value = Range ("Sheet2!$G$4") -1
*******************
Range ("Sheet2!$H$4") = Range ("Sheet2!$H$4") = Range ("Sheet2!$E$4").Value
If Range ("Sheet2!$G$4").Value =1 Then
MsgBox "Order More Hats!"
End If
If Range ("Sheet2!$G$4").Value = 0 Then
ActiveSheet.Sheet1.Hat1.Enabled = False
End If
End Sub

Error Notice
Run-Time Error '1004'
Method 'Range' of object'_Worksheet' failed

If I could have some help with this I'd be very grateful as I'm at my wits end with this vbs stuff.
A quick heads up;
The buttons are available on Sheet1 and read/interact with information from cells located on Sheet2.
I'll be happy to provide any more information if required
 
Top