Need assistance to populate sheet from Userform data
The first section of code populates Combobox2 for me:
It populates the list with values in Column C in the InspectionData Sheet.
I can get the Code 2(Combobox3) list to Populate with ALL the Values in the
Same ROW as the ALL the values in the List in Combobox2, But
i ONLY want the 1(ONE) value that is in the same ROW as the Selected value
in the Combobox2.
Is there a way to do this ????
~~~~~~~ Code 1 ~~~~~~~~~~~~~~~~~
Private Sub ComboBox2_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox2.ListCount 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) < "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text =
Sheet5.Range("B2").Value And .Cells(myrow, 1).Offset(0, 0).Value =
ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Value < "" Then
ComboBox2.AddItem Cells(myrow, 3).Offset(i, 0)
ComboBox2.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 3).Offset(i,
0).Address
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
~~~~~~~~ End ~~~~~~~~~~~~~~~~~
~~~~~~~~ Code 2 ~~~~~~~~~~~~~~
Private Sub ComboBox3_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox3.ListCount 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) < "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text =
Sheet5.Range("B2").Value And .Cells(myrow, 1).Offset(0, 0).Value =
ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 10).Offset(i, 0).Value < "" Then
ComboBox3.AddItem Cells(myrow, 10).Offset(i, 0)
ComboBox3.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 10).Offset(i,
0).Address
If .Cells(myrow, 3).Offset(0, 0).Value = UserForm11.ComboBox2.Value Then
ComboBox3.Value = .Cells(myrow, 3).Offset(0, 7).Value
End If
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
~~~~~~~~ End ~~~~~~~~~~~~~~~
|