add new value to named range

**wpryan** · 05-25-2010, 11:53 AM

Hi All,
I have a named range that I want to be able to grow with newly added items. For example: we have a customer list in an in-cell drop down that is referenced in a named range. I would like to be able to select an existing customer or add a new customer, if the customer doesn't exist. Is there a way to do this in VBA? Thanks in advance.

**NBVC** · 05-25-2010, 11:58 AM

See here for a suggestion:

http://www.ozgrid.com/Excel/excel-va...ist-update.htm

**wpryan** · 05-26-2010, 10:11 AM

Thanks for your reply. I tried this, and it works only if the named range is in the same worksheet as the drop-down box. I need the range to be in another worksheet, therefore I suppose I need to declare the range as being in the other worksheet. I'm afraid I don't know how to do that. ...care to help?

**NBVC** · 05-26-2010, 10:33 AM

Try:

Private Sub Worksheet_Change(ByVal Target As Range)



Dim lReply As Long



    If Target.Cells.Count > 1 Then Exit Sub

        If Target.Address = "$A$1" Then

            If IsEmpty(Target) Then Exit Sub

                If WorksheetFunction.CountIf(Sheets("Sheet2").Range("MyNames"), Target) = 0 Then

                    lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)

                        If lReply = vbYes Then

                            Sheets("Sheet2").Range("MyNames").Cells(Sheets("Sheet2").Range("MyNames").Rows.Count + 1, 1) = Target

                        End If

                End If

        End If

End Sub

Replace the sheetname, Sheet2 with the sheet that has the list.

Also, replace $A$1 in Target.Address = "$A$1" with the cell containing the dropdown list.