Dynamic range that change variables

[Bengoboy]

Hi
I´ve been searching for a solution for this problem for a long time now and hope that you guys can help me out. I´m just a beginner in VBA so it may be easy.

I have a sheet with a column of Products, to each Product I have different values in other columns on the same row. Rows are deleted and added.
I want to have a userform with a combobox that are populated from the Product column and depending on wich choice the user makes I want to get values from other columns on the same row.
I guess it must be a way to have a dynamic range to populate the combobox and then use the Offset funktion to get values from the other columns but can´t figure it out. Could anybody help me with this ? I would really appreciate it !!
Many thanks

[Aussiebear]

Please attach a sample workbook, by clicking on Go Advanced, scroll down to Manage Attachments and follow the prompts from there.

[raji2678]

1) Create a sheet which has a master list of products
2) Create a named range for that list
3) Select the cell you want to have a dropdown. In the Data Validation, select List in the first dropdown and type =RangeName in the second dropdown.

Hope this helps

[raji2678]

If it is a user form you can use the RowSource property and give the name of the range. Kindly refer to this site
http://support.microsoft.com/kb/161598

[Bengoboy]

Maybe I wasn´t clear enough, I have no problem populate a combobox from a cell range, the problem is to use an dynamic range to the combobox that refers to the adjacent cells in the row. Hope my file describes it self.
Thanks again!

[Bengoboy]

No one have any ideas on this?? I really been searching a lot on this everywhere and I think it should be an quite easy task, but maybe not?

[mdmackillop]

[VBA]Private Sub ComboBox1_Change()

Dim r As Range
Set r = Columns(1).Find(ComboBox1).Resize(, 3)

Label6.Caption = r(1)
Label7.Caption = r(2)
Label8.Caption = r(3)

End Sub[/VBA]

[Bengoboy]

Haven´t tried it yet but I´m really happy to get an answer and it seem that simple that I wanted it to be! Will try it , thanks a bunch !!

[Bengoboy]

Thank you so very much !!! Works exactly as i wanted, easy to add and delete rows without changes in the code!!
Thanks again !!!

[Bengoboy]

How do I mark the thread as solved?

[Bengoboy]

Hi, I tried to use this method but it seems to be some problem with it, some times when I shall make the choice in the combobox I get Runtime error 91, is there an solution to this problem?

B.R / Bengt

[Bob Phillips]

What value are you selecting in the combobox?

[Bengoboy]

I have a sheet with products in column A, which I populate the combobox with, to each product I have 16 different values in other columns that belongs to that product. So I want to select a product in the combobox and get values to variables from that choice. What is strange is that sometimes it works fine but sometimes I get the runtime error??

[Bob Phillips]

Your example workbook seemed problem free, so post the real book and tell us how to re-create the error.

[Bengoboy]

This is under construction but I made different tests and some works and some not. To populate the combobox, press "Maskin1" or "Maskin2" button
B.R/ Bengt

[jolivanes]

If you would change

Set r = Columns(1).Find(ComboBox1).Resize(, 17)

To

Set r = Sheets("Data1").Columns(1).Find(ComboBox1).Resize(, 17)

Would that make a difference?

[Bengoboy]

Looks good so far! Aah, hope it was that simple! Thanks a lot!
B.R/Bengt