Let me clarify what I am trying to do.

I have a sheet "Process Runs" that collects data from a user form
"frmProcessDataEntry".

1 of the fields that needs to be filled out by the user is "cbModSN".
This field is a combobox so it can list the available ModSN's as the user
has to enter data related to a certain ModSN many different times.

The ModSN list is also used on another user form so they can select which
ModSN's chart they wish to view.

My app works fine if I "preload" the ModSN list before the user adds data.
What I am trying to do is allow NEW ModSN's to be added by the user by
typing them into the combobox.

As it is now, they can type directly into the combobox, add their new data,
but the NEW ModSN is not added to the list for the next time the user needs
to add data for that ModSN.

"Gizmo" wrote:

ModSN is a named range. Refers to is
"=OFFSET(Lists!$H$9,0,0,CountA(Lists!$H:$H),1) ".
cbModSN is the ComboBox on form "frmProcessDataEntry". It's Row Source is
ModSN.

"Gizmo" wrote:

That is not in my original post nor in my code.

atpgroups suggested I remove the quotes from my named ranges and also remove
the Range() functions:

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(ModSN, Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo +
vbQuestion)
If lReply = vbYes Then
ModSN.Cells(ModSN.Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

This doesn't work either.


"JLGWhiz" wrote:

Don't know if it is a typo, but the symbol won't
fly in the line below. I assume that ModSN is a range
name. I think that if you remove the it might work.

Range("ModSN").Cells(Range("ModSN").Rows.Count + 1, 1) = Target


"Gizmo" wrote:

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