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.

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:

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

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
>
>

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
>
>