Creating a Cell List using VBA not Validation

[pmcleod]

It's been years since I have used this site. I am very happy to see that it is still doing so well.

Can someone help me with the code, or an example, on how to create a macro that lists data - then apply that list to the necessary cells?

I do not want to use the validation function.

Thank you in advance

Perry McLeod, PMP

 

[unmarkedhelicopter]

Sorry, your requirements are not clear to me.
Where do you want the 'list' to reside ?
Apply to what cell ? The selected cell ?
How is the Macro to be triggered ?
Why don't you want to use validation ?

 

[pmcleod]

WAIT! I am so sorry I totally forgot that I can name the cell range in another worksheet then use =range_name in the source validation.

So sorry .... been a while since I've had to use cell validation.

NOW .... if I had a combo-box control that would change the list depending on what was selected in the combo-box ... that would be very cool indeed.

 

[unmarkedhelicopter]

You mean if one combo-box selection affected the list in another combo-box ?
Like Countries and Cities ?
box 1 has all the countries and box 2 has all the cities but if you pick (say) Portugal in box 1, then only Portuguese Cities are available in box 2 ?

 

[pmcleod]

Yep ... bull's eye

 

[unmarkedhelicopter]

It's do-able care to post some data and we'll see what can be done and without any code too.

 

[qafself]

Dependant drop down lists

Hi,

Check out this site - it's excellent

http://www.contextures.com/xlDataVal02.html

Ed

 

[pmcleod]

sure thing ... here you go. totally non normalized. I'm just putting the data together for a staff assessment. later I'm sure someone will create some tables.

see attached

 

[pmcleod]

I had a look at ...lDataVal02.html. Thanks for the tip. Good site ... but what I'd want is one combo-box at the top of the sheet that controls the choices for a given column.

Example - choose 'applications' and all the validation cells in column D change to data related to that category.

Like a said - a database is the right solution. I just don't have the resources (myself included) to have someone spend time on that.

thoughts?

 

[shades]

Okay, I am not sure you have all the info yet, that you need. I am attaching an example. On the Control worksheet, there are four named ranges. Three of them are dynamic (I included the formulas used in naming them so you can see how they are dynamic. To check, go to Insert > Names > Define, and click on a named range, and you will see the formula in the box below.

Notice that whatever is in the first named range has to have its own column with options. In the example, in column A are High, Medium, Low. Then each of those is the title for the next list (B1, C1, D1).

Now on the Work worksheet, in A3, use Data Validation and you will see that the defined name is used, Grade. Now in B3, notice that Data Validation gives the formula:

=INDIRECT(A3). Thus, whenever you change the name in A3, the list of possible names change to reflect what is in A3 List. You can add others.

===========

That means when you select something, it will have to have its own list. But you don't have it set up that way in your LISTS worksheet.
________
Dodge 50 Series

 

[shades]

Further reflection... it happens to us old codgers.

Anyway, it seems like you are trying to do everything on the worksheet with the data. It is better practice to separate the data, from the data setup, and display, each on the its own worksheet (or if very large, own workbook). This has an added advantage in that if you have a change in the source data it does not affect the setup or display. Likewise, if you have to change the display, you don't have to change anything with data, and only a little in data set up.
________
Jacques Nasser

 

[pmcleod]

ah ... very cool. yes i do get it. thank you very much. this will be very helpful indeed.

thank you for taking the time. have a great Monday!