Validation List (please help)

[John Kitchens]

I need some step by step help. I have a workbook with three sheets.

What I need is to type a customer name and ID# in cell G3 on Sheet1. I would
like for this info to go to Sheet 3 cell A1.

At cell G3 on sheet 1 I need a dropdown that will allow me to see all of the
previous entries so I can select them when the same customer returns. (I
would prefer to do this using a combobox from the forms list if I could so
that I could make it fit without having to adjust the actual size of the
entire column.) I also need for this list to be sorted alphabetically after
each new entry is made.

I found one example of this on the net. This is the address:

http://www.contextures.com/DataValListAddSort.xls

This is the decription:

Update Validation List -- type a new value in a cell that contains data
validation, and it's automatically added to the source list, and the list is
sorted; a macro automates the list updates. DataValListAddSort.xls 30 kb

Can someone please help me?

John Kitchens

 

[Debra Dalgleish]

Create a range named "NameList", as described in the sample workbook.
For a list on Sheet1, your formula would be:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Then, change the code to reference your cell, and sheet name:

'=========================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Sheet1")
If Target.Address = "$G$3" Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub

'===============================