Looping thru sub-level of controls on userform

[Susan]

on this multipage, i have textboxes that i loop thru to make sure
something's in them. Ok, fine.

then i have a bunch of frames. within the frames are option buttons.
i want to make sure one option button is selected in each frame.

in a previous project somebody gave me the code to do this BUT there
were ONLY frames with option buttons, so i just looped through all the
option buttons. i could do it that way again, by labeling option
buttons with tags (some are hidden, only to be used sometimes, and are
not in this looping counting business. this is what i've got so far
but my theory is wrong somehow.........

====================

'make sure an option button is checked in each
'frame that's visible & used

Dim oControl As Control
Dim bControl As Control
Dim i as Integer

i = 0

For Each oControl In Me.Controls
If TypeOf oControl Is msforms.Frame Then
If oControl.Tag = "CountMe" Then
For Each bControl In oControl
If TypeOf bControl Is msforms.OptionButton Then
If bControl Then
i = i + 1
End If
End If
Next bControl
End If
End If
Next oControl

If i <> 5 Then 'something's missing
MsgBox "Every frame must have a selected option button!" _
& vbCrLf & _
vbCrLf & _
"Please go back and answer all the questions." _
, vbOKOnly + vbExclamation
Exit Sub
End If
=========================
any ideas on how to make this theory work? or perhaps it wont & i'll
have to tag each option button individually..........
thanks!
susan

 

[JohnH]

Hi Susan

I have found it to be good practice to always 'pre-select' a default option
button, however if this is not possible the following code (an adaptation of
yours) will check through each frame with the 'countme' tag and show an
error if any don't have 5 option buttons wholse value is FALSE.

HTH
John


Private Sub Check()
Dim oControl As Control
Dim bControl As OptionButton
Dim lngControl As Long
Dim lngNotSelected As Long

For Each oControl In Me.Controls
If TypeOf oControl Is msforms.Frame Then
If oControl.Tag = "CountMe" Then
For lngControl = 0 To oControl.Controls.Count - 1
If TypeOf oControl.Controls.Item(lngControl) Is
msforms.OptionButton Then
If oControl.Controls.Item(lngControl).Value = False
Then
lngNotSelected = lngNotSelected + 1
End If
End If
Next

If lngNotSelected <> 5 Then 'something's missing
MsgBox "Every frame must have a selected option button!"
_
& vbCrLf & _
vbCrLf & _
"Please go back and answer all the questions." _
, vbOKOnly + vbExclamation
Exit Sub
Else
lngNotSelected = 0
End If
End If
End If
Next oControl

End Sub

 

[Susan]

john -
thank you very much for the help............ i'll give it a try. if
it doesn't work then i'll tag each option button that i want checked
(the visible ones) & then just loop thru them.

susan

 

[Susan]

worked great!!!!
i also like the idea of pre-selecting a default. thanks for the idea.
susan

 

[Susan]

ummmm, worked great in testing. slight problem but i think i can work
around it. on the userform there are 5 frames, so i was searching for
a TOTAL of 5 selected option buttons to show that each frame has a
selection. your code searches thru the first frame (which happens to
have 4 option buttons in it), finds that 3 are false....... 3 <> 5 so
i get the message box. i believe then it will move onto the next
frame (not sure yet).

i think i'm going to change lngNotSelected = False to lngSelected =
True & make sure lngSelected = 1. then move on to the next frame.
yes, that worked...........

final code:

==========================
Dim oControl As Control
Dim lngControl As Long
Dim lngSelected As Long

For Each oControl In Me.Controls
If TypeOf oControl Is msforms.Frame Then
If oControl.Tag = "CountMe" Then
For lngControl = 0 To oControl.Controls.Count - 1
If TypeOf oControl.Controls.Item(lngControl) _
Is msforms.OptionButton Then
If oControl.Controls.Item(lngControl) _
.Value = True Then
lngSelected = lngSelected + 1
End If
End If
Next

If lngSelected <> 1 Then 'something's missing
MsgBox "Every frame must have a selected option
button!" _
& vbCrLf & _
vbCrLf & _
"Please go back and answer the question" _
& vbCrLf & _
vbCrLf & _
"in -- " & oControl.Caption & " -- frame." _
, vbOKOnly + vbExclamation
Exit Sub
Else
lngSelected = 0
End If
End If
End If
Next oControl

=============================

thank you very much for your help, john!
susan