Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Object buttons, based on value of other object buttons
I have two groups of object buttons. The first group is Object 7 and Object
button 10. The second group is Object button 8 and Object button 9. What I want to do is if Object button 7 is = TRUE, allow the user to be able to select values in object buttons 8 and 9. If Object button 10 = TRUE, then they cannot select options in 8 and 9. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Object buttons, based on value of other object buttons
Hello Jeffbert, I am guessing your Object Buttons are Forms Option Buttons. If that is the case then the following macro should give you the results you want. First copy this code to the clipboard using Ctrl + C. Second add a standard VBA Module to your Workbook and paste the code into it. Right Click each Option Button and assign this macro to it. Adding a Module: 1) Open your Workbook and press Alt + F11 to launch the VBA Editor. 2) Press Alt + I to drop down the Insert Menu. 3) Press M to add the Module to the Workbook. 4) Press Ctrl + V to paste the macro code into it. 5) Press Ctrl + S to Save the changes. 6) Press Alt + Q to Quit the VBA Editor and return to Excel Macro Code: Code: -------------------- Public Sub ControlOptions() Dim ID Dim OptBtn As Shape ID = Application.Caller Set OptBtn = ActiveSheet.Shapes(ID) With ActiveSheet.Shapes Select Case OptBtn.Name Case Is = "Option Button 7" .Item("Option Button 8").ControlFormat.Enabled = True .Item("Option Button 9").ControlFormat.Enabled = True Case Is = "Option Button 10" .Item("Option Button 8").ControlFormat.Enabled = False .Item("Option Button 9").ControlFormat.Enabled = False End Select End With End Sub -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=547617 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Object buttons, based on value of other object buttons
Leith
Thank you for the response. However, I am using the option buttons from the active x toolbar, and not the forms toolbar. When I try to run the macro, I am getting the following error: Run time error '13': Type Mismatch and the debugger highlights this line of code "Set OptBtn = ActiveSheet.Shapes(ID)" Thanks again Jeff "Leith Ross" wrote: Hello Jeffbert, I am guessing your Object Buttons are Forms Option Buttons. If that is the case then the following macro should give you the results you want. First copy this code to the clipboard using Ctrl + C. Second add a standard VBA Module to your Workbook and paste the code into it. Right Click each Option Button and assign this macro to it. Adding a Module: 1) Open your Workbook and press Alt + F11 to launch the VBA Editor. 2) Press Alt + I to drop down the Insert Menu. 3) Press M to add the Module to the Workbook. 4) Press Ctrl + V to paste the macro code into it. 5) Press Ctrl + S to Save the changes. 6) Press Alt + Q to Quit the VBA Editor and return to Excel Macro Code: Code: -------------------- Public Sub ControlOptions() Dim ID Dim OptBtn As Shape ID = Application.Caller Set OptBtn = ActiveSheet.Shapes(ID) With ActiveSheet.Shapes Select Case OptBtn.Name Case Is = "Option Button 7" .Item("Option Button 8").ControlFormat.Enabled = True .Item("Option Button 9").ControlFormat.Enabled = True Case Is = "Option Button 10" .Item("Option Button 8").ControlFormat.Enabled = False .Item("Option Button 9").ControlFormat.Enabled = False End Select End With End Sub -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=547617 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable All Cmd Buttons Except Those Meeting Criteria | Excel Programming | |||
VBA: Disable Frame and Radio Buttons based on Another Radio Button Being True | Excel Worksheet Functions | |||
disable close and shrink buttons | Excel Programming | |||
Disable Min Max restore buttons | Excel Programming | |||
Programmatically adding buttons to a worksheet (Shape Object) | Excel Programming |