prob using data validation in excel 97

[rob9740]

I'm trying to get each cell in a column to give the user a list to choose from when they click in it. But I also want them to be able to add to the list if the item isn't there. I'm using a data validation list at the moment so is there any way of adding to the data validation list through the use of the drop down list?????

This is what I'm using:

Public Sub CreateCustList()
With ActiveCell
.Validation.Delete
.Validation.Add xlValidateList, , , "=" & "CustomerList"
End With
End Sub

If it's not possible could anybody give me any other suggestions???
Cheers
rob9740

 

[Dreamboat]

Sure, put a message in the data validation (this is possible through regular means, so I'm sure it's possible through VBA) that says "the value you entered is incorrect, go to SheetName and add it to the end of the CustomerList column" OR "Hit Escape, Ctrl-G, type CustomerList, hit Enter, hit down arrow key and type the name of the item you'd like to add" OR, well, you get the picture.

When you name your CustomerList range name, just ALSO select blank cells at the bottom of the list too--however many you foresee being added in a given amount of time.

It's not pretty, tho, huh? I don't know of any other way to add to a validation list since its sole purpose is to restrict entry...

?

Hope it helps!

http://www.PiersonTech.com

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

 

[Dreamboat]

Hmmm. Perhaps you can write a macro and assign a keystroke command that goes to your customerlist range and then the xldown thing? And then just have the message tell them to hit Escape (to get out of the cell), and then hit keystroke command. But, dern, how do you get them back to the right place?

http://www.PiersonTech.com

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

 

[rob9740]

Thanks a mil. Your first post works a charm for what I'm doing at the mo.

Cheers,
rob9740

 

[Dreamboat]

Great!

http://www.PiersonTech.com

techsupportgirl@home.com
Brainbench MVP for Microsoft Word