Userform ComboBox Search WS and Populate Userform

LavendarRabbit

New Member
Joined
Apr 19, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a MultiTab UserForm, and want to focus on the "Check" tab. Currently when the user types in the WO number into the ComboBox (ComboBox7), then clicks the command button with the magnifying glass (CommandButton3), it will search the "Paste Log" worksheet and pull the first line that matches the WO and pull in all of the corresponding information into the UserForm.

Since the "Paste Log" worksheet has duplicate WOs, I would like all of the other ComboBoxes on the Userform to include the applicable data from all of the lines that match the WO number.

Here are my 2 codes:
VBA Code:
Private Sub ComboBox7_Change()

Dim i As Long
Dim lr As Long

If ComboBox7 <> "" Then
    With Sheets("Paste Log")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 2 To lr
        If InStr(1, .Cells(i, "B"), ComboBox7) > 0 Then
            ComboBox7.AddItem .Cells(i, "B")
        End If
    Exit For
    Next i
    End With
End If
End Sub

VBA Code:
Private Sub CommandButton3_Click()
Dim Res As Long
Dim Found As Range
Dim lr As Long
Dim i As Long
Dim lngCounter As Long

Set Found = Sheets("Paste Log").Range("B:B").Find(What:=ComboBox7.Value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    With Sheets("Paste Log")
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        
        
            For i = 2 To lr
                If .Cells(i, "B") = ComboBox7 Then
                    Res = i
                    Exit For
                End If
        Next i
    End With

If Not Found Is Nothing Then
    Res = Found.Row
    If Res <> 0 Then
        ComboBox11.Value = Sheets("Paste Log").Range("A" & Res).Value
        ComboBox8.Value = Sheets("Paste Log").Range("C" & Res).Value
        ComboBox9.Value = Sheets("Paste Log").Range("D" & Res).Value
        ComboBox10.Value = Sheets("Paste Log").Range("E" & Res).Value
        ComboBox6.Value = Sheets("Paste Log").Range("H" & Res).Value
        TextBox12.Value = Sheets("Paste Log").Range("I" & Res).Value
        TextBox7.Value = Sheets("Paste Log").Range("J" & Res).Value
        
    Else
        MsgBox "WO does not exist. Verify WO is typed in correctly, otherwise contact supervisor", , ""
    End If
End If
End Sub

WO# = ComboBox7 = Col B
Magnifying Glass Command Button = CommandButton3
Type = ComboBox11 = Col A
Lot Code =ComboBox8 = Col C
IDH Code = ComboBox9 = Col D
Batch Code = ComboBox10 Col E
Paste Consistency = ComboBox16 = Col H
Note = TextBox12 = Col I
Employee = TextBox7 = Col J
Excel Spreadsheet Screenshot.png
Excel Screenshot.png


Thank you in advance!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,716
Members
417,107
Latest member
derekMG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top