updating dynamic via drop down list

[dsmdude]

http://www.gfcproducts.com/purchase_order_log.xls


I would like to be able to add to my dynamic ranges in the data sheet via the drop down boxes.
If the company is not in the range on my data sheet, i would like to be able to just type in a new one in the drop down box and it will automatically update the data sheet column for company.

I have read alot of other people's efforts on making this work, and I did exactly what was recommended for the other people, and it did not work for me.

If anyone can help out with this, that would be great!

Also, if anyone has any better ideas on how to setup this purchase order log, I would much appreciate it!

 

[jeffreybrown]

Hi dsmdude,

Try this...right click the sheet tab (~ Purchase Order Log ~) and paste this code on the right side of the screen.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub 'Limits code to column "C"
    If IsEmpty(Target) Then Exit Sub
    If WorksheetFunction.CountIf(Sheets("Data").Range("company"), Target) = 0 Then
    lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
    If lReply = vbYes Then
    Sheets("Data").Range("company").Cells(Sheets("Data").Range("company").Rows.Count + 1, 1) = Target
    End If
    End If
End Sub