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

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


"Corey" wrote in message
...
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

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

"Corey" wrote in message
...
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

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

--
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
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

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....
"Tom Ogilvy" wrote in message
...
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

--
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
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