Using a Variable within Userform.List Attempt 2

[jparfitt87]

Ok so i've been working on this inbetween my day to day job and i still can't work it out!
I'll try and keep it simple:

I am trying to populate 3 different answer sets within a Userform Listbox or ComboBox.

The answer set displayed is dependant on what is selected on the "Answers" Sheet.

For example If i have select "Answer_Set_1" then "Excellent, Good, Neither Good or Bad etc" will be shown within the ComboBox

The Code I have so far which i can't seem to get to work is:

For X = 1 To 10 'This is the Variable to go through the worksheet range "ResponceQ1 - 10"
AStr = "CmbQ" & X & "AV" 'This is the variable to go through the ComboBox's to then pickup the answer set.
For Each ctrl In UserForm1.MultiPage1.Pages(0).Controls 'This runs through the first pages controls.
If TypeName(ctrl) = "Combobox" Then 'This determines the control type
If Left(ctrl.Name, 4) = "CmbQ" Then 'This finds the controls name


'This is the part i'm struggling with........
If Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_1" Then
'This part of the code goes through the worksheet Ranges and finds what typ of answer has been selected.
Userform1.Controls.List(AStr) = Worksheets("Answers").Range("Answer_Set_1").Value
'This part of the code is supposed to bring the answers selected into the Conbobox selected but doesn't
Else


I'm really at a loss with this, i know i'm on the right lines but can't seem to get any further.

Please help

Regards

Jamie

 

[xenou]

Hi,

This part shouldn't have an "X" in it, because it is just one answer (so you aren't supposed to be doing any 1-10 rows here)

'This is the part i'm struggling with........
If Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_1" Then

But this part probably should have your X in it, since here you do want to get the answers from rows 1 to 10:

Userform1.Controls.List(AStr) = Worksheets("Answers").Range("Answer_Set_1").Value

Probably you want something like (this is non-working pseudocode though):

IF Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_1" Then
    Userform1.Controls.List(AStr) = SomeAnswer1Range.SomeCell(X)
ELSE IF Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_2" Then
    Userform1.Controls.List(AStr) = SomeAnswer2Range.SomeCell(X)
ELSE IF Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_3" Then
    Userform1.Controls.List(AStr) = SomeAnswer3Range.SomeCell(X)
END IF

 

[jparfitt87]

Hi,

This part shouldn't have an "X" in it, because it is just one answer (so you aren't supposed to be doing any 1-10 rows here)

'This is the part i'm struggling with........
If Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_1" Then

Hi Xenou,

Thank you for your response. This is really difficult trying to explain how it should work.

The "ResponseQ & X" I think should be this way as on the Questions sheet, i have uniquley named the Cells where the answers are chosen as ResponseQ1, ResponseQ2, ResponseQ3 etc.

just to clarify If I select "Answer_Set_1" on the Questions Worksheet, the code should then pick up the named Range "Answer_Set_1" on the Answers Worksheet to bring forward the answer set withint the combo box.

I'm going to try and work with the code you provided and see what I can egt

Regards

 

[Norie]

I think xenou is on the right track but I think this,

Userform1.Controls.List(AStr) = Worksheets("Answers").Range("Answer_Set_1").Value

should be this.

Userform1.Controls(AStr).List = Worksheets("Answers").Range("Answer_Set_1").Value

 

[jparfitt87]

I think xenou is on the right track but I think this,

Userform1.Controls.List(AStr) = Worksheets("Answers").Range("Answer_Set_1").Value

should be this.

Userform1.Controls(AStr).List = Worksheets("Answers").Range("Answer_Set_1").Value

Great Scotts!!! its nearly there!! I can now get it to cycle through correctly using this:


For X = 1 To 50
AStr = "CmbQ" & X & "AV"
For Each ctrl In UserForm1.MultiPage1.Pages(0).Controls
'If TypeName(ctrl) = "Combobox" Then 'I've blanked this out as the code for some reason doesn't

If Left(ctrl.Name, 4) = "CmbQ" Then

If Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_1" Then
UserForm1.Controls(AStr).List = Worksheets("Answers").Range("Answer_Set_1").Value
Else
If Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_2" Then
UserForm1.Controls.List(AStr) = Worksheets("Answers").Range("Answer_Set_2").Value
Else
If Worksheets("Questions").Range("ResponceQ" & X) = "Answer_Set_3" Then
UserForm1.Controls.List(AStr) = Worksheets("Answers").Range("Answer_Set_3").Value
End If
End If
End If
End If
'End If
Next ctrl
Next

but i get a run time error :-/ Could not find the specified object.

I don't understand because the basic code of this works fine:

'If Worksheets("Questions").Range("ResponceQ1") = "Answer_Set_1" Then
' FrmScope.CmbQ1AV.List = Worksheets("Answers").Range("Answer_Set_1").Value
' Else
' If Worksheets("Questions").Range("ResponceQ1") = "Answer_Set_2" Then
' FrmScope.CmbQ1AV.List = Worksheets("Answers").Range("Answer_Set_2").Value
' Else
' If Worksheets("Questions").Range("ResponceQ1") = "Answer_Set_3" Then
' FrmScope.CmbQ1AV.List = Worksheets("Answers").Range("Answer_Set_3").Value
' End If
' End If
'End If

But if i use this i have to repeat it multiple times. It just stops when i try and use a variable and loop its just stops working

 

[xenou]

I don't have time to look at the moment but this site explains the debugging tools at your disposal. Nearly always I will step through code when debugging it (in fact, I never finish a project without doing a step-through).
A Day in the Life: Programming: Excel VBA Debugging for Beginners

 

[jparfitt87]

I've got it!!!

All it was that was stopping the code was my "FOR X = 1-50" Should have only been "FOR X 1-10" the rest of the code works if anyone ever needs this please feel free to use