Dynamic source list to auto expand

[cjtj4700]

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]

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

 

[Gord Dibben]

Chris

Dave's code will work for the three DV dropdowns you have. Just change the
addresses.

Let me qualify the above...........Will work fine for "Carriers" and
"Services" which are of irregular length and sorted independently.

BUT.......being able to add to and sort the "Company" column won't do you much
good without adding all the other pertinent data that goes with each new
Company you add.

i.e. Addressone, Addresstwo, City, State, Country, Postal/ZIP Code, Contact,
Phone No.

How do you propose to add those with each new Company name?

I would suggest you abandon the idea of adding through the DV dropdown for the
Company and its associated columns and use the standard Data Form for entering
new items across the board.

Select columns D:L and name it Database.

Go to Data>Data Form and your Database will be selected. Click on "New" and
add all the pertinent information in the appropriate dialog boxes.

Close the Form then click on Name Box and "Database". Data>Sort>Sort on
"Company" will sort all columns together in that range.


Gord

 

[cjtj4700]

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.

 

[cjtj4700]

Hi Gord~
Thx. I will plug away at this and see what happens Regarding new entries,
users not finding their "Company", must input all of that new pertinent info
you apeak of. I was under the impression that when doing this a new completed
company entry would get added to the VLoookup table??

Bear with my greeness again, Dave or Gord, do I just copy and paste that
code? Words like "range", "target" and "address" spook me. It goes on the
assumption that the requestor knows what he is doing! LOL

I just want to be done with this crazy thing. Everyday, I learn something
new from you guys and it keeps going and going......

 

[Dave Peterson]

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.

 

[Gord Dibben]

If user does not find their Company name and chooses to enter/add it in the DV
dropdown, that's all that will be added ro the VLOOKUP table....just the name.

What about all the other details that have to go with that name?

How/where do you propose to enter that?

You would need about 10 DV dropdowns and 10 Select Case statements in the code
plus 10 distinct dynamic ranges.

PLUS...........You don't want the data in D:L to be sorted independently. You
want it sorted together. That's how a VLOOKUP table operates. If each column
is sorted independently, the table will not stay together and be useless.

The DV dropdowns are for filling in the cells that feed the VLOOKUP formulas,
not for adding data

That's why I recommend using the Data Form.

If user doesn't find their Company in the DV dropdown, open the data form and
fill it in under "New". You can record a macro for opening the Form and
assign to a button for users to click.

The newest data will fill in below the last entry in the VLOOKUP table.

You can then sort that Database by Company column.


Gord

 

[Gord Dibben]

Chris

Or just do away with the VLOOKUPS completely and have your users fill in from
10 or 12 DV dropdowns with the ability to add to and sort each of these.

Quite unwieldy in my estimation.

If you look at Dave's code he has provided for 3 cases using 3 dynamic ranges.
You would have to provide for 10-12 cases and chase your users around from
dropdown to dropdown when they wanted to add new information in all 10-12
columns.


Gord

 

[cjtj4700]

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]

Glad you got it working (I'll speak, er, write for the others, too!)

Enjoy the season!

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!

 

[Gord Dibben]

Thanks for the feedback Chris.

Happy to hear you got it sorted.

Doing all the finishing touches as you have gives you ownership of the project
and the ability to maintain the workbook, which is where we were gently
nudging you.

Good work on quite a complex project.

Happy Holidays to you and yours.


Gord

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!