Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating list
Hi Everyone,
I have a combo box in a userform (Complaint.Verified). The box is populated with an initial list by the following code: With Workbooks("Service test by thi.xls").Worksheets("Sheet2") Set ComplaintRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'using the entire range of values in the column A starting with A2 Me.Complaint_verified.List = ComplaintRng.Value If a new value was entered in the combo box (Me.complaint_verified) that is not part of the list, how do I add this new value to sheet2 under row A for future use. I have something like: For Each ComplaintRng.Value In ComplaintRng If Me.Complaint_verified < ComplaintRng.Value Then .Additem Me.Complaint_verified.Worksheets("sheet2").range(1 :4) End If Next ComplaintRng But of course this code doesn't work. Any suggestions. thanks! tracktraining -- Learning |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating list
tracktraining;243238 Wrote: Hi Everyone, I have a combo box in a userform (Complaint.Verified). The box is populated with an initial list by the following code: With Workbooks("Service test by thi.xls").Worksheets("Sheet2") Set ComplaintRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'using the entire range of values in the column A starting with A2 Me.Complaint_verified.List = ComplaintRng.Value If a new value was entered in the combo box (Me.complaint_verified) that is not part of the list, how do I add this new value to sheet2 under row A for future use. I have something like: For Each ComplaintRng.Value In ComplaintRng If Me.Complaint_verified < ComplaintRng.Value Then .Additem Me.Complaint_verified.Worksheets("sheet2").range(1 :4) End If Next ComplaintRng But of course this code doesn't work. Any suggestions. thanks! tracktraining -- Learning Hello tracktraining, This update of your code includes error checking. It operates by checking the entry after the user hits the enter key in the combo box. The code extends the worksheet range by adding the entry and the combo box list, if the entry isn't found. Empty entries, including an entry of spaces are ignored. Copy this code into your UserForm for the combo box you named complaint_verified. '---------------------------------------------- Private Sub complaint_verified_AfterUpdate() Dim Data As String Dim Ret As Variant Dim Rng As Range Dim RngEnd As Range With Workbooks("Service test by thi.xls").Worksheets("Sheet2") Set Rng = .Range("A2") Set RngEnd = .Cells(Rows.Count, Rng.Column).End(xlUp) Set RngEnd = IIf(RngEnd.Row < Rng.Row, Rng.Row, RngEnd.Offset(1, 0)) Set ComplaintRng = .Range(Rng, RngEnd) End With On Error Resume Next Data = Me.complaint_verified.Value If Trim(Data) < "" Then Ret = WorksheetFunction.VLookup(Data, ComplaintRng, 1, False) If Err = 1004 Then ComboBox1.AddItem Data RngEnd.Value = Data Err.Clear End If End If On Error GoTo 0 End Sub '---------------------------------------------- -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67808 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating list
Add a command button to your UserForm and put this code in the form code module. When a value is added to the ComboBox, click the command button. Private Sub CommandButton1_Click() '??? Set srcRng = Sheets("Sheet2") _ .Range("A2", Cells(Rows.Count, 1).End(xlUp)) If WorksheetFunction. _ CountIf(srcRng, Me.Complaint_Verified.Value) = 0 Then Sheets("Sheet2").Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0) = Me.ComboBox1.Value End If End Sub "tracktraining" wrote: Hi Everyone, I have a combo box in a userform (Complaint.Verified). The box is populated with an initial list by the following code: With Workbooks("Service test by thi.xls").Worksheets("Sheet2") Set ComplaintRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'using the entire range of values in the column A starting with A2 Me.Complaint_verified.List = ComplaintRng.Value If a new value was entered in the combo box (Me.complaint_verified) that is not part of the list, how do I add this new value to sheet2 under row A for future use. I have something like: For Each ComplaintRng.Value In ComplaintRng If Me.Complaint_verified < ComplaintRng.Value Then .Additem Me.Complaint_verified.Worksheets("sheet2").range(1 :4) End If Next ComplaintRng But of course this code doesn't work. Any suggestions. thanks! tracktraining -- Learning |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating list
thanks Leith and JLGWhiz. I implemented JLGWhiz code and it codes perfectly.
I actually already had a button that i wanted the code to be part of so your code works like a charm. -- Learning "JLGWhiz" wrote: Add a command button to your UserForm and put this code in the form code module. When a value is added to the ComboBox, click the command button. Private Sub CommandButton1_Click() '??? Set srcRng = Sheets("Sheet2") _ .Range("A2", Cells(Rows.Count, 1).End(xlUp)) If WorksheetFunction. _ CountIf(srcRng, Me.Complaint_Verified.Value) = 0 Then Sheets("Sheet2").Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0) = Me.ComboBox1.Value End If End Sub "tracktraining" wrote: Hi Everyone, I have a combo box in a userform (Complaint.Verified). The box is populated with an initial list by the following code: With Workbooks("Service test by thi.xls").Worksheets("Sheet2") Set ComplaintRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'using the entire range of values in the column A starting with A2 Me.Complaint_verified.List = ComplaintRng.Value If a new value was entered in the combo box (Me.complaint_verified) that is not part of the list, how do I add this new value to sheet2 under row A for future use. I have something like: For Each ComplaintRng.Value In ComplaintRng If Me.Complaint_verified < ComplaintRng.Value Then .Additem Me.Complaint_verified.Worksheets("sheet2").range(1 :4) End If Next ComplaintRng But of course this code doesn't work. Any suggestions. thanks! tracktraining -- Learning |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Source List | Excel Discussion (Misc queries) | |||
Updating List | Excel Discussion (Misc queries) | |||
updating a list | Excel Programming | |||
list box updating itself | Excel Programming | |||
Updating a list | Excel Discussion (Misc queries) |