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.

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

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