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

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

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

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