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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
On 15 May, 16:29, Gizmo wrote:
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
On 19 May, 16:19, 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. 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).Offs et(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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto add to list
On 19 May, 18:29, Gizmo wrote:
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 |
Reply |
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 |