excel2003

I got this from ozgrid.
Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "$D$1" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0
Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo +
vbQuestion)
If lReply = vbYes Then

Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

How do I use this on a User Form

I created the list on "Lists" sheet in H9:H10
List name is ModSN
I substituted my range and list name in the above code and refers to
formula, but it doesn't work. I used my textbox's name in place of the
address.

Here's what I have placed in the module where the ComboBox code is.
(Doubleclicked the combo box and pasted the code and modified)

Private Sub frmProcessDataEntry_Change(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count 1 Then Exit Sub
If Target.Address = "cbModSN" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("ModSN"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo +
vbQuestion)
If lReply = vbYes Then
Range("ModSN").Cells(Range("ModSN").Rows.Count +
1, 1) = Target
End If
End If
End If
End Sub

On 14 May, 22:30, Gizmo wrote:

Dim lReply As Long

* * If Target.Cells.Count 1 Then Exit Sub
* * * * If Target.Address = "cbModSN" Then
* * * * * * If IsEmpty(Target) Then Exit Sub
* * * * * * * * If WorksheetFunction.CountIf(Range("ModSN"), Target) = 0 Then
* * * * * * * * * * lReply = MsgBox("Add " & Target & " to list", vbYesNo +
vbQuestion)
* * * * * * * * * * * * If lReply = vbYes Then
* * * * * * * * * * * * * * Range("ModSN").Cells(Range("ModSN").Rows.Count +
1, 1) = Target
* * * * * * * * * * * * End If
* * * * * * * * End If
* * * * End If
End Sub

(I don't have Excel on this computer so can't try these)
Are the ranges (modSN and cbmodSN) named ranges or range objects? In
either case, try them without the quote marks and Range() functions.