auto add to list

[Gizmo]

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

 

[atpgroups]

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.

 

[JLGWhiz]

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]

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.

 

[atpgroups]

atpgroups suggested I remove the quotes from my named ranges and also remove
the Range() functions:
This doesn't work either.

What are ModSN and cbModSN? Range objects? Strings? Named Ranges?

 

[Gizmo]

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.

 

[atpgroups]

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.

I confess that I didn't read your original message with the care I
should have done.
If you are trying to do what i think you are tryig to do, you want to
have a userform that you type things in to, and have that data added
to a worksheet column?
The problem with what you were doing is that the "Change" event isn't
relevant to a userform or a combobox. If you look at the top of the
code entry window you wll see two drop-down boxes, the left hand one
is all the objects you have available, and the right hand one shows
all the events that can be raised by the object. (in this case the
UserForm and the Combobox are objects).
Furthermore the "Target" range is only relevant to worksheet objects.
If you pick your events from the code window drop-downs you will get a
pre-formatted bit of code with correctly listed parameters.

What you probably want to do is trap the pressing of the "Enter" key
in the combobox. Every key press raises 3 events (KeyDown, KeyUp and
Keypress). Generally you want to ignore them.

This bit of code will do what I describe, it might not do what you
want (I am not clear if you are ising the ModSN range for anything.
Paste this in your userform
(note that the Google Groups interface tends to put in spurious line-
breaks, so some fiddling might be required)

Private Sub cbModSN_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
If KeyCode <> 13 Then Exit Sub
If Worksheets("Lists").Range("H:H").Find(cbModSN.Text) Is Nothing Then
Worksheets("Lists").Range("H65535").End(xlUp).Offset(1, 0).value =
cbModSN.Text
cbModSN.Text = ""
Else
' entry already exists. Perhaps add a message
End If
End Sub

Incidentally, I see no reason to use a combobox, a simple textbox
would work just as well.

 

[Gizmo]

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.

 

[atpgroups]

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.

You need to add the newly entered data into the .List property field
of the combobox. Unfortunately that will be transient, anything added
will not be there next time you open the spreadsheet. One solution is
to keep the data in a hidden sheet. In the example below I have used
Sheet1, but you almost certainly want to change that. Hidden sheets
are a good place to keep data that VBA Macros need to work with.
The secret is in the combobox.List property and the Combobox.Add
method.
I have assumed two buttons, bAdd and bDel which add and remove entries
from the list. You could, however, put the bAdd code in the
ComboBox.Keydown event to add on Enter=key press as in the earlier
example.

In frmProcessDataEntry you need the following code (again, beware of
line breaks)

Private Sub bAdd_Click()
Dim i As Integer
For i = 0 To cbModSN.ListCount - 1
If cbModSN.List(i, 0) = cbModSN.Text Then 'entry already exists
Beep
Exit Sub
End If
Next
cbModSN.AddItem cbModSN.Text
cbModSN.Text = ""
cbModSN.SetFocus ' move the focus back to the box
End Sub


Private Sub bDel_Click()
Dim i As Integer
For i = 0 To cbModSN.ListCount - 1
If cbModSN.List(i, 0) = cbModSN.Text Then 'entry found
cbModSN.RemoveItem (i)
cbModSN.Text = ""
cbModSN.SetFocus ' move the focus back to the box
Exit Sub
End If
Next
Beep ' entry not found
End Sub


Private Sub UserForm_Activate()
UserForm2.ComboBox1.List = Sheet1.Range("A1",
Sheet1.Range("A65535").End(xlUp)).Value
End Sub


Private Sub UserForm_Deactivate()
'Store the values
Sheet1.Cells.Clear
Sheet1.Range("A1").Resize(cbModSN.ListCount + 1, 1).Value =
cbModSN.List
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
'Store the values
Sheet1.Cells.Clear
Sheet1.Range("A1").Resize(cbModSN.ListCount + 1, 1).Value =
cbModSN.List
End Sub


And then in your other userform (Userform2)

Private Sub UserForm_Activate()
UserForm2.ComboBox1.List = Sheet1.Range("A1",
Sheet1.Range("A65535").End(xlUp)).Value
End Sub