check ocontrols within frames within userform

[Susan]

dear gurus -
i'm in waaaay over my head. i THINK i understand this theory...... to
explain:

i have a userform w/a 3-page multipage.
each multipage has (approx.) 4 frames on it
each frame has 3-5 option buttons in it.
the purpose of the userform is to incrementally add "1" to each
evaluation question on the worksheet.

Question 1 (frame name is Q1)
5 (optionbutton name is Q1Opt5)
4 (optionbutton name is Q1Opt4)
3 (name is Q1Opt3)

Question 2 (frame name is Q2)
5 (name is Q2Opt5)
4 (name is Q2Opt4)
3 (name is Q2Opt3)

and so forth. the worksheet ranges correspond:
Set r1 = ws.Range("d8:j13")
Set r2 = ws.Range("d18:j23")
Set r3 = ws.Range("d28:j33")

so, i want to go thru each frame, find the option button that is
selected, find THAT # option button in the corresponding range, find
the correct row, and add 1 to the correct column in the correct row in
the correct range. i'm using iCtr as the numeral for each frame.
this is what i've got so far..............

Dim iCtr As Long
'this is how i can incrementally check each frame
For iCtr = 1 To 13
If TypeOf oControl Is msforms.Frame Then
oControl = "Frame" & iCtr
'now i need to see which option button = true within each frame...
For Each oControl In oControl
If oControl("Q" & iCtr & "Opt1").Value = True Then
sRange = "r" & iCtr
sCaption = oControl("Q" & iCtr & "Opt1").Caption
'the captions of each option button are 1, 2, 3, etc.
Set rFound = myRange.Find(What:=sCaption, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If rFound Is Nothing Then
MsgBox "Caption not found in range"
End If
myRow = rFound.Row
'column i contains the numeric that needs incrementing
Set myRange = ws.Range("i" & myRow)
myRange.Value = myRange.Value + 1
'example: Range("C2") = 26
'myRange("C2").value (26) = 26 + 1
End If
Next oControl
End If

Next iCtr

as i usually do, i suspect this is coded way more difficult than it
needs to be.

any help or suggestions?
thank you!
susan

 

[Susan]

once more, with emphasis............ & then i'll leave you alone!

susan

 

[merjet]

The following works for 2 Frames with 4 Option Buttons in each. Each
Option Button has it's Groupname property set to "Frame1" or "Frame2".
You probably want to put the code in a different Sub.

Hth,
Merjet


Private Sub UserForm_Click()
Dim iCtr As Long
Dim oControl As Control
Dim ws As Worksheet
Dim myRange As Range
Dim rFound As Range
Dim sCaption As String

Set ws = Sheets("Sheet2")
'this is how i can incrementally check each frame
For iCtr = 1 To 2
For Each oControl In Me.Controls
If TypeName(oControl) = "OptionButton" Then
If oControl.GroupName = "Frame" & iCtr Then
'now i need to see which option button = true within
each frame...
If oControl.Value = True Then
Set myRange = ws.Range("d8:j23")
sCaption = oControl.Caption
'the captions of each option button are 1, 2, 3,
etc.
Set rFound = myRange.Find(What:=sCaption, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If rFound Is Nothing Then
MsgBox "Caption not found in
range"
End If
'column i contains the numeric that needs
incrementing
ws.Range("i" & rFound.Row) = ws.Range("i" &
rFound.Row) + 1
End If
End If
End If
Next oControl
Next iCtr
End Sub

 

[Susan]

thank you merjet, i will give it a try!
looks like i was close, but i couldn't get my brain around it.
i'll let you know how it turns out.
susan

 

[Susan]

ok, one hang up on this line:

Set myRange = ws.Range("d8:j23")

this range has to be able to increment thru each
question's range using the frame's iCtr number.
if i set it as a static range, then it won't move down
for each question.

but if i try to set the range as:
Set myRange = "r" & iCtr

where i've already set each individual range as r1, r2, r3,
it won't do it, because "r" & iCtr is a string, not a range.
what do you think? set it as a string & then convert it to
a range? i don't know how to do that but i could probably
find it in the newsgroup cuz i've seen it before.......

just for info, where do you find the groupname property on
a control? i couldn't find it in the properties box. do you
have to set it programatically?

thanks a lot!
susan

 

[Susan]

just for info, where do you find the groupname property on
a control? i couldn't find it in the properties box. do you
have to set it programatically?

never mind about this one - i found it. duh.

 

[merjet]

Set myRange = ws.Range("d8:j23")

this range has to be able to increment thru each
question's range using the frame's iCtr number.
if i set it as a static range, then it won't move down
for each question.

I set it to cover questions in all frames, assuming every question
unique. If that's not the case, then set myRange dynamically. You
could use a Select Case structure or create Range Names on the
worksheet and use the Names.

Merjet

 

[Susan]

I set it to cover questions in all frames, assuming every question
unique. If that's not the case, then set myRange dynamically. You
could use a Select Case structure or create Range Names on the
worksheet and use the Names.

Merjet

no, all the questions are not unique. questions 1-6 have a rating
scale of 5 - 1. so therefore if frame 1's answer is "5" & frame 2's
answer is also "5" then it gets added 2x to #5 frame 1.

i was just searching the newsgroup because i was trying a different
approach:

Public myRange As Range
Public sRange As String

sRange = "r" & iCtr 'this is a string (i.e., r2)
myRange = sRange 'range = string (i.e., r2 = r2)

i thought i could "trick" vba into reading the range from the string,
but it's not working. (that darn vba is just too smart!). i looked
into "redimming", but that seems to work with arrays.

so i had just come to the same conclusion you did, that i need to do a
case structure with each iCtr number setting it's individual range.
i hate case structures, though, i've never been able to get one to
work properly (i've always falling back on multiple if statements).
but it guess the time has come to tackle it!!!.
thanks very much for your help, merjet!
susan