You guys are awesome!! Thx Gord & Dave.
Here's what you have enabled me to create:

All Dynamic DV dropdowns now can be added to per "error message click ok"
code. These are used with Employee, Carrier, Service, Description.

Employee email auto fills using =INDEX/MATCH formula.

I added a pop up calendar for the date cell with a comment informing the
user to "right click for calendar", picks the date and calendar dissapears.

Of course I have the VLookup tool to auto fill the address lines simply by
picking the company from a dropdown + Database Name box Sort on "Company"
tool to update list.

and a =IF formula forcing users to select either "Business" or "Personal" &
"Prepaid" or "Collect" in shipment type. Anything else or empty cell causes
error message.

I have been using XL for about 2 months now so thank you again Mr. Dibben,
Mr. Peterson, Mr. Phillips & Debra aka Contextures for helping me with this.

Happy Holidays!

"Dave Peterson" wrote:

I think you should try it yourself. If you're ever going to support changes to
the workbook, you're gonna want to do it yourself.

In my code, A1 used a list from Sheet2 named List1
B9 used List2
C3 used List3

The code itself goes behind the worksheet with the data|validation cells.

Just rightclick on that worksheet's tab and choose View code. You'll see a code
window open and you'll just paste the code there.



cjtj4700 wrote:

Thanks Dave. I am learning so much. If I sent you my actual named ranges,
sheet names and all pertinent locations of my spreadsheet could you plop them
into your code? Then I imagine I could paste it. Or I could email you my
spreadsheet?
I really appreciate your and everyones help.

"Dave Peterson" wrote:

First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
for me) "List1".

I defined that name using this:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
So that it would expand and contract based on the number of entries in column A
of Sheet2.

Debra Dalgleish has instructions:
http://www.contextures.on.ca/xlNames01.html#Dynamic

Then I applied data|validation to A1 of sheet1.
I chose List and used List1 as the range for the list.

But on the Error alert tab of the Data|Validation dialog, I chose:
Style: Warning
Title: New Entry!
Error Message: New entry will be added to list if you click ok.

Then I used a worksheet_change event that waited for a change to A1. Note that
this will not work in xl97. (But Debra shows away around it using a button near
the dropdown.)

This was the code behind sheet1:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myList As Range

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1,b9,c3")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

Set myList = Nothing
Select Case LCase(Target.Address(0, 0))
Case Is = "a1"
Set myList = Me.Parent.Worksheets("sheet2").Range("list1")
Case Is = "b9"
Set myList = Me.Parent.Worksheets("sheet2").Range("list2")
Case Is = "c3"
Set myList = Me.Parent.Worksheets("sheet2").Range("list3")
'etc
End Select

If myList Is Nothing Then
Exit Sub
End If

If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
'already there, do nothing
Else
With myList
.Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
Set myList = .Resize(.Rows.Count + 1, 1)
End With

With myList
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
End If

End Sub

It seemed to work ok.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm


cjtj4700 wrote:

Hi~
I have been hooked up with a very nice VLookup tool for my companies
shipping request form (thank you Gord!). Several "Lists" of data is stored in
Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
would like to know if anyone out there would be willing to send me the code
or let me send them my workbook to allow new entries into any dropdown cell
to be auto added to its source list, alphabetically. Per Contextures.com,
there is a macro that enables this and VB does a fly by on me. The sample
spreadsheet I was looking at was "Update Multiple Validation Lists".

Thanks as always!

--

Dave Peterson


--

Dave Peterson