need help on how to grey out one option button in one group box based on the selection of another op

[George]

Hi, Everyone:

I would appreciate if you can help me on this!


I have two group box (Form) --- Group1 and Group2 --- on a regular
excel spreadsheet. There are 3 option buttons in Group1 and 7 option
buttons in Group2. I must select ONE AND ONLY ONE from each group
box.
What I am trying to do is: If option button 1 in Group1 is clicked,
then ONE AND ONLY ONE of all 7 option buttons in Group2 is able to be
clicked. However, if option button 2 in Group1 is clicked, then ONE
AND ONLY ONE of two option buttons in Group2 is able to be clicked
(the other 5 option buttons will be greyed out). Option button 3 in
Group 1 is similiar to Option button 2.


Thank you,

 

[Guest]

Hi George
How about something like:
In design mode change Group2 optbuttons to Enabled = False

Then in code (untested)
Dim ctrl
If Group1.optbtn1 = True Then
For each ctrl in Group2.Controls
ctrl.Enabled = True
Next ctrl
Elseif Group1.optbtn2 = True or Group1.optbtn2 = True then
Group2.optbtn1 = True
Group2.optbtn2 = True
End if

hth

Geoff

 

[George]

Hi George
How about something like:
In design mode change Group2 optbuttons to Enabled = False

Then in code (untested)
Dim ctrl
If Group1.optbtn1 = True Then
For each ctrl in Group2.Controls
ctrl.Enabled = True
Next ctrl
Elseif Group1.optbtn2 = True or Group1.optbtn2 = True then
Group2.optbtn1 = True
Group2.optbtn2 = True
End if

hth

Geoff








- Show quoted text -

Thanks, could you please show me how to do it in more details? Your
logic looks right, just not sure the details.

 

[merjet]

Are these option buttons from the Forms toolbar or the Control Toolbox
toolbar?
The VBA code would depend on which.

Merjet

 

[Guest]

Hi George
one way:
Set up your form with 10 optbtns. GroupName btns 0 to 2 as Group1 and
optbtns 3 to 9 as Group2 or whatever. Then still in design mode change btns
3 to 9 from default Enabled = True to Enabled = False.
Then in the form code put:

Option Explicit

Private ctrl As Object, i As Integer

Private Sub optbtn1_Click()

Set ctrl = UserForms(0).Controls
For i = 3 To 9
ctrl(i).Enabled = True
Next

End Sub

Private Sub optbtn2_Click()

Set ctrl = UserForms(0).Controls
For i = 3 To 9
ctrl(i).Enabled = False
Next

optbtn4.Enabled = True
optbtn5.Enabled = True

End Sub

Private Sub optbtn3_Click()

Set ctrl = UserForms(0).Controls
For i = 3 To 9
ctrl(i).Enabled = False
Next

optbtn7.Enabled = True
optbtn8.Enabled = True

End Sub

hth

Geoff

 

[George]

Hi George
one way:
Set up your form with 10 optbtns. GroupName btns 0 to 2 as Group1 and
optbtns 3 to 9 as Group2 or whatever. Then still in design mode change btns
3 to 9 from default Enabled = True to Enabled = False.
Then in the form code put:

Option Explicit

Private ctrl As Object, i As Integer

Private Sub optbtn1_Click()

Set ctrl = UserForms(0).Controls
For i = 3 To 9
ctrl(i).Enabled = True
Next

End Sub

Private Sub optbtn2_Click()

Set ctrl = UserForms(0).Controls
For i = 3 To 9
ctrl(i).Enabled = False
Next

optbtn4.Enabled = True
optbtn5.Enabled = True

End Sub

Private Sub optbtn3_Click()

Set ctrl = UserForms(0).Controls
For i = 3 To 9
ctrl(i).Enabled = False
Next

optbtn7.Enabled = True
optbtn8.Enabled = True

End Sub

hth

Geoff






- Show quoted text -

Thank you so much for your work. However, my option buttons are all
"Forms" Under View \ Toolbars once your open an excel spreadsheet. It
looks like you are talking about Userforms. Please clarify. Thanks
again and have a nice day!

 

[George]

Are these option buttons from the Forms toolbar or the Control Toolbox
toolbar?
The VBA code would depend on which.

Merjet

Thanks, all optionbuttons and group boxes are from Forms Toolbars.

 

[Dave Peterson]

One way:

Option Explicit
Sub testme01()
With ActiveSheet
.OptionButtons.Enabled = False
.GroupBoxes.Enabled = False
End With
End Sub

You could use .visible = false, too.

 

[merjet]

The following is for 2 Option Buttons (#2 & #3) in Group1 and 4 in
Group2 (#5-#8), but it should be easy to adapt. Button5's value is set
to 1 in case one of the Buttons to be disabled is checked. It won't
"grey out", but it will disable.

Public Sub ControlOptions()
Dim ID As String
Dim OptBtn As Shape

ID = Application.Caller
Set OptBtn = ActiveSheet.Shapes(ID)

Select Case OptBtn.Name
Case Is = "Option Button 2"
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
True
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
True
Case Is = "Option Button 3"
ActiveSheet.OptionButtons("Option Button 5").Value = 1
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
False
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
False
End Select

End Sub

Hth,
Merjet

 

[merjet]

The following is for 2 Option Buttons (#2 & #3) in Group1 and 4 in
Group2 (#5-#8), but it should be easy to adapt. Button5's value is set
to 1 in case one of the Buttons to be disabled is checked. It won't
"grey out", but it will disable.

Public Sub ControlOptions()
Dim ID As String
Dim OptBtn As Shape

ID = Application.Caller
Set OptBtn = ActiveSheet.Shapes(ID)

Select Case OptBtn.Name
Case Is = "Option Button 2"
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
True
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
True
Case Is = "Option Button 3"
ActiveSheet.OptionButtons("Option Button 5").Value = 1
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
False
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
False
End Select

End Sub

Hth,
Merjet

 

[George]

The following is for 2 Option Buttons (#2 & #3) in Group1 and 4 in
Group2 (#5-#8), but it should be easy to adapt. Button5's value is set
to 1 in case one of the Buttons to be disabled is checked. It won't
"grey out", but it will disable.

Public Sub ControlOptions()
Dim ID As String
Dim OptBtn As Shape

ID = Application.Caller
Set OptBtn = ActiveSheet.Shapes(ID)

Select Case OptBtn.Name
Case Is = "Option Button 2"
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
True
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
True
Case Is = "Option Button 3"
ActiveSheet.OptionButtons("Option Button 5").Value = 1
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
False
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
False
End Select

End Sub

Hth,
Merjet

Hi, Merjet:

Thank you so much for your time and efforts in helping me out! I put
your code under a "Module1". However, when I press Alt + Q and click
OptionButton3 in groupbox1, I can still click any one of
OptionButton5, OptionButton6, OptionButton7, and OptionButton8. It
looks like the code doesn't work. Please correct me if I am wrong.

I look forward to hearing from you soon!

Thanks again,

George

 

[merjet]

My code does not depend on pressing Alt+Q. Both Option Button 2 and
Option Button 3 are linked to the macro ControlOptions, so it executes
automatically if either Button is clicked. I can send the Excel file
to your e-mail address if you want.

Merjet

 

[George]

My code does not depend on pressing Alt+Q. Both Option Button 2 and
Option Button 3 are linked to the macro ControlOptions, so it executes
automatically if either Button is clicked. I can send the Excel file
to your e-mail address if you want.

Merjet

Hi, Merjet:

From what you said, you are pretty sure that your code is working

properly. If you don't mind, please send your excel file to me. My e-
mail address is (e-mail address removed)

I am looking forward to hearing from you soon!

Thanks again for your time and efforts in helping me out!

Sincerely,

George

 

[merjet]

I found the way, but it took a while. Programming for Forms Toolbar
controls is much harder than for Control Toolbox Toolbar controls.
Enhanced code below.

Hth,
Merjet


Public Sub ControlOptions()
Dim ID As String
Dim OptBtn As Shape

ID = Application.Caller
Set OptBtn = ActiveSheet.Shapes(ID)

Select Case OptBtn.Name
Case Is = "Option Button 2"
'enable any disabled option buttons and make them white
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
True
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
True
ActiveSheet.OptionButtons("Option Button
6").ShapeRange.Fill.Visible = msoFalse
ActiveSheet.OptionButtons("Option Button
7").ShapeRange.Fill.Visible = msoFalse
Case Is = "Option Button 3"
'make sure option buttons to be disabled are not selected
ActiveSheet.OptionButtons("Option Button 5").Value = 1
'disable some option buttons and make them grey
ActiveSheet.Shapes("Option Button 6").ControlFormat.Enabled =
False
ActiveSheet.Shapes("Option Button 7").ControlFormat.Enabled =
False
'make the disabled option buttons grey
With ActiveSheet.OptionButtons("Option Button
6").ShapeRange.Fill
.Visible = msoTrue
.Transparency = 0.7 'lower is darker
.ForeColor.SchemeColor = 22
End With
With ActiveSheet.OptionButtons("Option Button
7").ShapeRange.Fill
.Visible = msoTrue
.Transparency = 0.7 'lower is darker
.ForeColor.SchemeColor = 22
End With
End Select

End Sub