Conditionally Disabling Optionbuttons

[kgriba]

I have two option buttons (from the Control Toolbox in Excel 2003) named
"OptionButton18" and "OptionButton19", both of which have the GroupName
"nameWallTile."

OptionButton18 also has what I am considering a subgroup of 3 option
buttons, all of which have the GroupName "nameWallTileBKSP"

I would like the group "nameWallTileBKSP" of OptionButton18 to be disabled
when OptionButton19 is selected.

Any suggestions? I'm not very familiar with VB, but am not afraid of
learning something new, especially about Excel!

Thanks!

 

[Dave Peterson]

You could just use the names of the 3 "sub-optionbuttons" and disable them. But
if you add more optionbuttons to that group "nameWallTileBKSP", then you'll have
to update the code for those names.

Another way is to loop through all the OLEobjects on the sheet and look at the
groupname and decide to enable/disable it based on the "checkedness" of
optionbutton19.

This goes under the worksheet module that owns those optionbuttons:

Option Explicit
Private Sub OptionButton18_Click()
Call CheckSubGroup
End Sub
Private Sub OptionButton19_Click()
Call CheckSubGroup
End Sub
Sub CheckSubGroup()

Dim OLEObj As OLEObject
Dim myEnabled As Boolean

myEnabled = Not (Me.OptionButton19.Value)

For Each OLEObj In Me.OLEObjects
If TypeOf OLEObj.Object Is MSForms.OptionButton Then
If LCase(OLEObj.Object.GroupName) = LCase("nameWallTileBKSP") Then
OLEObj.Enabled = myEnabled
'OLEObj.Object.Value = False
End If
End If
Next OLEObj

End Sub

 

[kgriba]

Hi Dave... thanks... gave that a try and probably due to my lack of
understanding, got an error message popup, "Invalid Use of the Me Keyword."
I've learned in the editor that it's likely having to do with my incorrect
placement of your code into "Module1".

I wasn't really sure what "This goes under the worksheet module that owns
those optionbuttons:" meant, so I guessed. I copy-pasted your code into the
only module I can see in the editor, that would be "Module1".

Do I need to create a module with a name associated with the GroupNames or
OptionButton names?

Thanks again,
Kristine

 

[Dave Peterson]

Select the worksheet with the optionbuttons.

Rightclick on the worksheet tab and select View Code.

Paste the code here.

And clean up that code you put in a general module. It doesn't belong there.

 

[kgriba]

Brilliant! Worked like a charm! There's no way I would have figured that one
out on my own. And, I've cleaned up the general module as well (actually did
that after the error message pop-up.)

Thanks again,
Kristine