Looping through Cells for Vba userform

[jparfitt87]

hi all,

I'm currently creating a survey in excel and want it to be more user managable without the user having to go into VBA coding.

i have a page called "questions" within here is where the questions are and what type of answer is needed.

this is then picked up through vba code to plave it into the userform, see below:

LabelQ1AV.Caption = Range(FrmScope.LabelQ1AV.Name)
'This shows what the question is on the questions page......

If LabelQ1AV.Caption = "" Then
LabelQ1AV.Visible = False
Q1.Visible = False
CmbQ1.Visible = False
End If
'This hides the title, question and answer box if theres no question on the Q Sheet

If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_1" Then
FrmScope.CmbQ1.List = Worksheets("Answers").Range("Answer_Set_1").Value
Else
If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_2" Then
FrmScope.CmbQ1.List = Worksheets("Answers").Range("Answer_Set_2").Value
Else
If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_3" Then
FrmScope.CmbQ1.List = Worksheets("Answers").Range("Answer_Set_3").Value
End If
End If
End If
'This picks up the answers that are needed on the answers page, eg very happy, excellent, etc...

now this above code works perfectly for 1 question........ but i have at least 40 of them!! and dont want to have to repeat this code 40 times!

I need to know if there is a way to loop through the questions page and the user form???

I hope that makes sense.

Thanks in advance

 

[jparfitt87]

hi all,
I'm currently creating a survey in excel and want it to be more user managable without the user having to go into VBA coding.

i have a page called "questions" within here is where the questions are and what type of answer is needed.

this is then picked up through vba code to plave it into the userform, see below:

LabelQ1AV.Caption = Range(FrmScope.LabelQ1AV.Name)
'This shows what the question is on the questions page......
If LabelQ1AV.Caption = "" Then
LabelQ1AV.Visible = False
Q1.Visible = False
CmbQ1.Visible = False
End If

'This hides the title, question and answer box if theres no question on the Q Sheet

If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_1" Then
FrmScope.CmbQ1.List = Worksheets("Answers").Range("Answer_Set_1").Value
Else
If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_2" Then
FrmScope.CmbQ1.List = Worksheets("Answers").Range("Answer_Set_2").Value
Else
If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_3" Then
FrmScope.CmbQ1.List = Worksheets("Answers").Range("Answer_Set_3").Value
End If
End If
End If

'This picks up the answers that are needed on the answers page, eg very happy, excellent, etc...

now this above code works perfectly for 1 question........ but i have at least 40 of them!! and dont want to have to repeat this code 40 times!
I need to know if there is a way to loop through the questions page and the user form???
I hope that makes sense.
Thanks in advance

Ok So I have figured out how to tidy up the first half here it is:

Dim ctrl As Control, r As Integer, Tmpstr As String, P As Integer
For r = 0 To 9
For Each ctrl In FrmScope.MultiPage1.Pages(r).Controls
If TypeName(ctrl) = "Label" Then
If Left(ctrl.Name, 5) = "Label" Then ctrl.Caption = Range(ctrl.Name).Value
End If
Next ctrl
Next r
'This picks up the question set out within the named cell range (In this case the cell is uniquley named "LabelQ1AV") and places it within each Label name the same.

For P = 0 To 9
For Each ctrl In FrmScope.MultiPage1.Pages(P).Controls
If TypeName(ctrl) = "Label" Then
If Left(ctrl.Name, 5) = "Label" Then
Debug.Print (ctrl.Caption)
If ctrl.Caption = "" Then
Debug.Print Right(ctrl.Name, 4)
If Len(ctrl.Name) = 9 Then Tmpstr = Mid(ctrl.Name, 7, 3) Else Tmpstr = Mid(ctrl.Name, 7, 4)
Debug.Print "CmbQ" & Tmpstr & "Q" & Tmpstr & "LabelQ" & Tmpstr
FrmScope.Controls.Item("CmbQ" & Tmpstr).Visible = False
FrmScope.Controls.Item("Q" & Tmpstr).Visible = False
End If
End If
End If
Next ctrl
Next P

'This part of the code check if the label has come back blank, as there is no question. so hides the the title and answer box for that question only.

My next challenge now is to shrink down the Answers code as this is currently repeated 50 times.

Now the only thing that will change within the code each time is "ResponceQ1AV" and "CmbQ1"

The Q1 will go up to 10 and the AV will change also for example AV questions 1 to 10, CF questions 1-10.

Any help on this would be greatly appreciated, this simple survey has turned out more complicated than i expected but its going to be used every year.