Linking VBA combobox to named Worksheet Cell Range

[jparfitt87]

Hi all, kinda new to VBA and have become stuck on something.....

I have a VBA Userform which i'm using as a feedback survey.

Within the excel Sheet (which will later be hidden) I have the questions linked to the Labels within the Userform, next to the questions it has a list validation for which answers are needed IE agree, Satisfied, Very Good. These are name Answer_Set_1, Answer_Set_2 etc. the different range of answers are within their own sheet called "Answers" and the ranges are dynamically named according the the list validation.

What I need to do is link the combo box within the VBA userform to show the answers depending on the list validation selected....

This is what I'm thinking......

If Worksheets("Questions").Range(ResponceQ1AV) = "Answer_Set_1" Then
FrmScope.CmbQ1 = Worksheets("Answers").Range(Answer_Set_1)
End If

But I get an application defined or object defined error.

Does any one have anything that could help, or explain to me (in simple terms) where i'm going wrong??

Thanks in advance

 

[Rick Rothstein]

Within the excel Sheet (which will later be hidden) I have the questions linked to the Labels within the Userform, next to the questions it has a list validation for which answers are needed IE agree, Satisfied, Very Good. These are named Answer_Set_1, Answer_Set_2 etc. the different range of answers are within their own sheet called "Answers" and the ranges are dynamically named according the the list validation.

If Worksheets("Questions").Range(ResponceQ1AV) = "Answer_Set_1" Then
FrmScope.CmbQ1 = Worksheets("Answers").Range(Answer_Set_1)
End If

But I get an application defined or object defined error.

Are Answer_Set_1, Answer_Set_2, etc. Defined Names on the worksheet or VBA variables? If they are Defined Names, then they need to be surrounded with quote marks inside the Range property call (otherwise VB thinks they are normal variables)...

FrmScope.CmbQ1 = Worksheets("Answers").Range("Answer_Set_1")

 

[jparfitt87]

Hi Rick, I've tried it with the quotations and now gives me a new error "Could not set the value property".

If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_1" Then
FrmScope.CmbQ1 = Worksheets("Answers").Range("Answer_Set_1")
End If

 

[Rick Rothstein]

Hi Rick, I've tried it the the quotations which gets me past the error message however the combo box doesn't have anything populate in it.

If Worksheets("Questions").Range("ResponceQ1AV") = "Answer_Set_1" Then
FrmScope.CmbQ1 = Worksheets("Answers").Range("Answer_Set_1")
End If

Okay, try using this for the assignment line instead of what you show above...

FrmScope.CmbQ1.List = Worksheets("Answers").Range("Answer_Set_1").Value

Note the specifying of the List and Value properties... those are important.

 

[jparfitt87]

this works amazingly thank you!!!

I also need help on how to loop through all the different options but i guess that would be a new thread??

 

[Rick Rothstein]

this works amazingly thank you!!!

I also need help on how to loop through all the different options but i guess that would be a new thread??

Yes, I think that would be your best bet.

 

[jparfitt87]

hi Rick I have posted on a separate thread but no replies as of yet, is this something you could help with?
http://www.mrexcel.com/forum/excel-...cells-visual-basic-applications-userform.html