PDA

View Full Version : Dynamic range that change variables



Bengoboy
02-23-2012, 03:21 PM
Hi
Ive been searching for a solution for this problem for a long time now and hope that you guys can help me out. Im 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 cant figure it out. Could anybody help me with this ? I would really appreciate it !!
Many thanks :beerchug:

Aussiebear
02-23-2012, 05:18 PM
Please attach a sample workbook, by clicking on Go Advanced, scroll down to Manage Attachments and follow the prompts from there.

raji2678
02-23-2012, 10:46 PM
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
02-23-2012, 10:57 PM
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
02-24-2012, 01:22 PM
Maybe I wasnt 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
02-25-2012, 11:31 AM
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?:igiveup:

mdmackillop
02-26-2012, 12:19 PM
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

Bengoboy
02-26-2012, 01:07 PM
:bow:
Havent tried it yet but Im really happy to get an answer and it seem that simple that I wanted it to be! Will try it , thanks a bunch :friends: !!

Bengoboy
02-26-2012, 01:43 PM
:bigdance2 :bigdance2
Thank you so very much !!! Works exactly as i wanted, easy to add and delete rows without changes in the code!!
Thanks again :friends: !!!

Bengoboy
02-26-2012, 01:47 PM
How do I mark the thread as solved?

Bengoboy
04-09-2012, 08:18 AM
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
04-09-2012, 08:46 AM
What value are you selecting in the combobox?

Bengoboy
04-09-2012, 11:18 AM
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
04-09-2012, 11:23 AM
Your example workbook seemed problem free, so post the real book and tell us how to re-create the error.

Bengoboy
04-09-2012, 11:38 AM
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
04-09-2012, 03:02 PM
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
04-09-2012, 08:52 PM
Looks good so far! Aah, hope it was that simple! Thanks a lot!
B.R/Bengt