Not ALL values are populating to Combobox for some reason. Why ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Corey - 19 Feb 2007 04:40 GMT

I am trying to set the Selection(row"E") of a Listbox value(by clicking it) to Populate the Combobox
with Values that are in the Same Row("BH")

Private Sub ListBox1_Click()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem Cells(myrow, 60) ' I am not getting ALL values listed
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

But i ONLY seem to get 1 or 2 values, wher ther are more than that.

Not sure why?

Corey

Reply to this Message

Alan - 19 Feb 2007 05:08 GMT

You got all your End If statements in. I'm not quite sure what the problem
is. Your code did show ".select" on a separate line, you might check that.
Other than that, you'll have to wait for one of the gurus to chime in.

Regards,

Alan

Private Sub ListBox1()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data").Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = Range("H1").Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow,
60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem Cells(myrow, 60) ' I am not getting ALL
values listed
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

>I am trying to set the Selection(row"E") of a Listbox value(by clicking it)
>to Populate the Combobox
[quoted text clipped - 30 lines]
>
> Corey

Reply to this Message

Mike Fogleman - 19 Feb 2007 11:14 GMT

Check your last statement...
ComboBox1.AddItem Cells(myrow, 60)

You are still in the With...End With block, so should not that line be with
Dot Cells??

ComboBox1.AddItem .Cells(myrow, 60)

Mike F

>I am trying to set the Selection(row"E") of a Listbox value(by clicking it)
>to Populate the Combobox
[quoted text clipped - 30 lines]
>
> Corey

Reply to this Message

Tom Ogilvy - 19 Feb 2007 14:09 GMT

You have to clear the error to check the next value. Adjustments made to
your code.

Private Sub ListBox1_Click()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem .Cells(myrow, 60)
else
err.clear
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Signature

Regards,
Tom Ogilvy

> I am trying to set the Selection(row"E") of a Listbox value(by clicking it) to Populate the Combobox
> with Values that are in the Same Row("BH")
[quoted text clipped - 29 lines]
>
> Corey

Reply to this Message

Corey - 19 Feb 2007 22:08 GMT

Tom you are a true gentleman.

I am going to analyse my code to yours nd see where i went wrong.
Your code posted works spot on.

Thank You

Corey....
You have to clear the error to check the next value. Adjustments made to
your code.

Private Sub ListBox1_Click()
Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then ComboBox1.Clear
Dim LastCell As Long
Dim myrow As Long
Dim nodupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set nodupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
nodupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ComboBox1.AddItem .Cells(myrow, 60)
else
err.clear
End If
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Signature

Regards,
Tom Ogilvy

"Corey" wrote:

> I am trying to set the Selection(row"E") of a Listbox value(by clicking it) to Populate the
> Combobox
[quoted text clipped - 30 lines]
>
> Corey

Reply to this Message