Try this (not tested).
Private Sub BSearch_Click()
Call Clearall
Dim gui As Worksheet
Dim AR As Worksheet
Dim getfind As String
Dim qq As Range
Dim x As Long
'For cv = 1 To 40
'1 cbv(cv) = ""
'Next cv
'get value from search
'MsgBox gui.OLEObjects("InputSearch").Object.Value
getfind = gui.OLEObjects("InputSearch").Object.Value
If getfind = "" Then MsgBox "Please enter a name to search for.", vbExclamation, "Invalid Search": Exit Sub
Set gui = Sheets("GUI")
'Set AR = Sheets("Employee")
For Each AR In Worksheets
If Not AR Is gui Then
With AR.Range("D:D")
Set qq = .Find(what:=getfind, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)
If Not qq Is Nothing Then
firstAddress = qq.Address
Do
x = x + 1
'Caption'
gui.OLEObjects("cb" & x).Object.Caption = x & ". " & qq.Offset(0, -2).Value & " " & qq.Offset(0, -1).Value & " (" & qq.Offset(0, -3).Value & ")"
'unhide combobbox
gui.OLEObjects("cb" & x).Visible = True
'unhide textbox
gui.OLEObjects("TextBox" & x).Visible = True
Set qq = .FindNext(qq)
Loop While qq.Address <> firstAddress
End If
End With
End If
Next AR
If x = 0 Then MsgBox "No match found for '" & getfind & "'", vbInformation, "Search Complete"
End Sub
Bookmarks