VBA search multiple textbox values

ratherton9835 · Sep 4th 2017

Hi, please could anyone help add to the following code so that it will search depending on which textbox, out of three search options is chosen to define the search term. Basically, I'd like to get it to search column A of the worksheet for "pID", column B for "p.code" or column C for "p.title". Any help greatly appreciated.

Code

Private Sub cmbFind_Click()
Set ws = Worksheets("Project Proposals")
Set MyData = ws.Range("a2").CurrentRegion
Dim strFind As String 'what to find
Dim FirstAddress As String
Dim f As Integer
strFind = Me.pIDs.Value 'what to look for
With MyData
.AutoFilter
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
With Me 'load entry to form
.pID.Value = c.Offset(0, 0).Value
.pcode.Value = c.Offset(0, 1).Value
.ptitle.Value = c.Offset(0, 2).Value
.pdescription.Value = c.Offset(0, 3).Value
.ptheme.Value = c.Offset(0, 4).Value
.plead.Value = c.Offset(0, 5).Value
.psponsor.Value = c.Offset(0, 6).Value
.pcoordinator.Value = c.Offset(0, 7).Value
.paims.Value = c.Offset(0, 8).Value
.pgroup.Value = c.Offset(0, 9).Value
.pthirdparties.Value = c.Offset(0, 10).Value
.ptimescale.Value = c.Offset(0, 11).Value
.poutputs.Value = c.Offset(0, 12).Value
.prisks.Value = c.Offset(0, 13).Value
.presources.Value = c.Offset(0, 14).Value
.pstatus.Value = c.Offset(0, 15).Value
.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
r = c.Row
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1 'count number of matching records
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
Case vbOK
FindAll
Case vbCancel
'do nothing
End Select
End If
Else: MsgBox strFind & " not listed. Please check ID number and try again" 'search failed
If ws.FilterMode Then ws.ShowAllData
End If
End With
End Sub

Display More

Share