Data Validation List or Combo Box or...?

[VBA_Newbie]

Hi folks,

I have a macro which generates a list of unique price points. Each time the macro runs it could generate any number of unique price points - from as low as 10 all the way up to 500 or more. What I'm trying to do is create a drop down box in cell that automatically fills with these unique price points.

Ideally, the user would select a particular unique price point that has been generated by the macro and I'll set up some form of Vlookup formula to show some relevant information about that particular price point.

Any tips or strategies you could offer me? I think data validation lists have a limit on the number of items you can place in them...as well...the range would always change (and is located on another worksheet).

Many thanks,
Mike

 

[Norie]

Mike

How and where does this code create the list of unique items?

 

[VBA_Newbie]

Hi Norie,

The macro is not something I wrote (it's way beyond me), I'm just modifying it. It creates the unique price values in column O on sheet called 'Data'. In columns P and Q are other values associated with the price value in column O. This sheet is a hidden from the user as they should not be able to mess with it.

On another sheet I want the user to be able to select any of the unique price points that have been generated and I will do some sort of Vlookup to show the other values associated with that particular price point --those values will in turn be put into graph (easy stuff).

I just need them to be able to select from a list of all unique values in column O on the data worksheet.

Thanks,
Mike

 

[Norie]

Mike

Well I can think of 2 options.

1 Create a dynamic named range based on column O, which can then be used in data validation or for a combobox. Try a board search for more info.

2 Create code that populates a combobox with unique values, this could be based on the existing code and might actually allow you to eliminate the need to store the unique values anywhere.

 

[VBA_Newbie]

Thanks Norie,

I used your first suggestion and things seem to have worked out! Much appreciated!