VB to activate list drop-down boxes when data is entered in a cell

All, I recorded a macro and adapted the Worksheet code to include the recorded macro. This will activate the Status dropdown only when data is in column 'A'. Going to try the same thing for the defined list.

Please Login or Register  to view this content.

The Named Range drop down is more difficult than I thought. I can not seem to get it recorded so the actual drop down appears. Can anyone assist?

Also, I need some code to automatically clear data and drop down boxes from any row that has no data in column 'A'. Currently, if I delete data in column 'A', the Status drop down box still appears and I have to delete the rest of the data manually. The drop down box can not be deleted but must be overwritten with an unformatted cell.

Any help is appreciated!!

Andrew

Bump, no response

Hello all. I am having problems working out the code for the Named Range drop down.

After perusing the forum I found the attached workbook that looks like it might do what I am looking for. The problem is the code refers to OLEObjects which I know nothing about and am not sure I need.

Can someone help me adapt this code:

Please Login or Register  to view this content.

which works in the 'drop downs with named ranges' workbook, to code that just opens a drop down in row 'E' using the named range 'Products' found on the Totals sheet?

Thanks!!

Andrew

Does anyone know how to do this??

Thanks,
Andrew

I have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made.

Andrew - what exactly do you mean by 'activate'? What do you want to happen when these things happen as I'm not entirely clear?

Sorry, still not quite clear. Isn't that what you have? The last few cells in col E are blank and when you select them the validation drop down appears.

Ok, if I understand what you are having trouble with, it is that you have a dynamic range of cells with a name (let's say "listname") and every time things are changed you want the dropdown list that references that range to add in the new cell values?

If so, then this is the validation code you want. It looks like you have the code you need for the rest of it. The important change is highlighted in red.

Please Login or Register  to view this content.

Just change 'listname' in the code to whatever your dynamic range is named. Hope this helps.

Sorry Stephen. Those are just copied down from above. I do not want these to be copied down since I would have to copy down all 65,000+ rows. I am looking for something along the lines of:

Please Login or Register  to view this content.

where Products in the code will open a drop down with a list of the products from the named range. The above code will not work and I have been trying variations with no success.

Ziilch, I will try your revision. It looks similar to the above so hopefully that will do the trick.

Thanks,
Andrew

The code change did not work. I received a 'variable not defined' error and although I tried to define it several different ways, I could not get

Please Login or Register  to view this content.

to refer to the named range 'Products' on the Totals sheet and just changiing the code to

Please Login or Register  to view this content.

resulted in the same error. I only found 1 reference to it in the forum but it did not help me understand what I needed to do. I am looking through Google now...

Andrew

Sorry about that. The code I gave would work if you had a variable "listname" in your code that contained the named range. This code should validate for a range named "Products" in your spreadsheet.

Please Login or Register  to view this content.

It's the double equals sign that's important.

Thanks Ziilch. I was able to use

Please Login or Register  to view this content.

so that section of the code is working!!

Andrew