Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto fill or auto search from a list or drop-down list??????? | Excel Discussion (Misc queries) | |||
Auto-List-Pop-Up | Excel Discussion (Misc queries) | |||
Auto filtering vs Date List Create List | Excel Worksheet Functions | |||
Auto check list box | Excel Programming | |||
Scrolling through Auto List members list | Excel Programming |