Below is some code i have to populate some listboxes.
The principal is to be able to Search/View any job by narrowing down the Job by Customer -
Department - Job Number (also dates etc) :

Listbox1 = customer names

Listbox2 = customer specific department name

Listbox3 = job number

Clicking on Listbox1 will populate Listbox2 with values related to the Listbox1 Customer

Clicking on Listbox2 will populate Listbox3 with a list of Job numbers(internal use)

Then if the user clicks on a listbox3 (job number), this populates some other data such as job date
and comments into some textboxes.
Then if the user clicks the commandbutton while a listbox3 value is selected, all the related data
is input into a separate worksheet for viewing/printing.


What i found is that sometimes there is only 1 job for a customer and No department name, and
therefore the Listbox3 will Not populate because there is no value in Listbox2.

So how can i use a variable line of code to directly populate listbox3 IF there is NO listbox2
value?
I have a textbox (textbox6) above the Listbox2 to display how many department
names(Listbox2.ListCount) are populated in Listbox2 already.

Private Sub ListBox1_Change()
Application.ScreenUpdating = False
If ListBox2.ListCount 0 Then ListBox2.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
ListBox2.AddItem .Cells(myrow, 60)

Err.Clear
End If
End If
End If
Next
End With
TextBox6.Value = ListBox2.ListCount ' <=== IF TEXTBOX6.VALUE = "0" THEN SKIP LISTBOX2 AND POPULATE
LISTBOX3 WITH JOB NUMBER(myrow,1)
Application.ScreenUpdating = True
End Sub


Private Sub ListBox2_Click()
Application.ScreenUpdating = False
If ListBox3.ListCount 0 Then ListBox3.Clear
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row

With ActiveWorkbook.Worksheets("Data")
..Select
For myrow = 1 To LastCell
If .Cells(myrow, 1) < "" Then
If .Cells(myrow, 1).Offset(0, 59).Value = ListBox2.Value Then
ListBox3.AddItem .Cells(myrow, 1)
End If
End If
Next
End With
TextBox5.Value = ListBox3.ListCount
Application.ScreenUpdating = True
End Sub


Private Sub ListBox3_Click()
Application.ScreenUpdating = False
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
..Select
For myrow = 1 To LastCell
If .Cells(myrow, 1) < "" Then
TextBox1.Value = ListBox3.Value
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Corey....