Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip other codes if value is Zero
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
skip 0 average | Excel Discussion (Misc queries) | |||
using IF to skip | Excel Worksheet Functions | |||
skip rows | Excel Discussion (Misc queries) | |||
skip rows | Excel Worksheet Functions | |||
skip blanks | Excel Worksheet Functions |