drop down additions

[stevesunfold]

hi guys if i have a drop down list of say 10 names

and i want to add a name to that drop down list is there anyway i can do this by just putting it in the input cell

eg if my dropdown list is in a1 and i need to select the name mark
mark isnt in the drop down list
i can manually type this as i have unchecked input message and error message
but is there anyway i can get a code to add that to the dropdown validation list the next time i open up
i ask because i dont want people entering names on the sheet and having the name range changed

[mrice]

This event driven macro should do the trick. Paste it into the tab for the appropriate sheet in the VBA editor (ALT F11). It works by checking the new value against the existing validation list and appending if new.

Please Login or Register  to view this content.

[stevesunfold]

hi martin thanks for the reply but i cant seem to get this to work
im probably doing something wrong
is there any way you could attach a sample sheet please so i can get my head round it?
thanks

[mrice]

Try this attachment. You'll see the code on sheet1.

[stevesunfold]

hi martin
thanks for that
when i drag the drop down list by the fill handle
ie a1 to a10
it doesnt change

[mrice]

Steve

Slightly confused here. I hadn't picked up that you wanted to be able to transfer the validation by use of the fill handle from your original message.

Before proceeding, can I check my understanding. If you enter a name that is not on the drop down list in any of the cells A1 to A10, you would like the new value to appear in the dropdown list for all ten cells? Have I got this right?

[stevesunfold]

spot on martin
and thanks for coming back to this

[mrice]

Steve

I can see that you've replied but the web interface isn't letting me see the detail. Can you try again or post to the link on my home page?

[mrice]

Steve

Try this version of the macro. It assumes that some sort of validation is already present in the MyRange cells.

Please Login or Register  to view this content.

[stevesunfold]

thanks martin
thats terrific
job done
well done