Ok so I am still trying to allow the userForm to allow the user to search using 1 of 3 textboxes. I will only be entering data into 1 not all 3 but I want to choose which one of the 3. Here is some code. It doesn't work, all it does is popup the msg box. This is a reference maybe it will help you understand what I am looking for. As you can tell I am new to this lol.
Code:
Private Sub cmbFind_Click()
Dim strFind As String 'what to find
Dim strFind1 As String
Dim strFind2 As String
Dim FirstAddress As String
Dim f As Integer
Dim g As Integer
Dim h As Integer
strFind = Me.TextBox1.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
.TextBox2.Value = c.Offset(0, 1).Value
.TextBox3.Value = c.Offset(0, 2).Value
.TextBox4.Value = c.Offset(0, 3).Value
.TextBox5.Value = c.Offset(0, 4).Value
.TextBox6.Value = c.Offset(0, 5).Value
.TextBox7.Value = c.Offset(0, 6).Value
.TextBox8.Value = c.Offset(0, 8).Value
.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbAdd.Enabled = False 'don't want to duplicate record
If c.Offset(0, 7).Value = "Yes" Then .optYes = True
If c.Offset(0, 7).Value = "No" Then .optYes = True
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
Me.Height = frmMax
End If
Else: MsgBox strFind & " not listed" 'search failed
End If
End With
strFind1 = Me.TextBox2.Value
With MyData
.AutoFilter
Set d = .Find(strFind1, LookIn:=xlValues)
If Not d Is Nothing Then
With Me 'load entry to form
.TextBox2.Value = d.Offset(0, 1).Value
.TextBox3.Value = d.Offset(0, 2).Value
.TextBox4.Value = d.Offset(0, 3).Value
.TextBox5.Value = d.Offset(0, 4).Value
.TextBox6.Value = d.Offset(0, 5).Value
.TextBox7.Value = d.Offset(0, 6).Value
.TextBox8.Value = d.Offset(0, 8).Value
.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbAdd.Enabled = False 'don't want to duplicate record
If d.Offset(0, 7).Value = "Yes" Then .optYes = True
If d.Offset(0, 7).Value = "No" Then .optYes = True
r = d.Row
g = 0
End With
FirstAddress = d.Address
Do
g = g + 1 'count number of matching records
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address <> FirstAddress
If g > 1 Then
Select Case MsgBox("There are " & g & " instances of " & strFind1, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
Case vbOK
FindAll
Case vbCancel
'do nothing
End Select
Me.Height = frmMax
End If
Else: MsgBox strFind & " not listed" 'search failed
End If
End With
strFind2 = Me.TextBox6.Value
With MyData
.AutoFilter
Set e = .Find(strFind2, LookIn:=xlValues)
If Not e Is Nothing Then
With Me 'load entry to form
.TextBox2.Value = e.Offset(0, 1).Value
.TextBox3.Value = e.Offset(0, 2).Value
.TextBox4.Value = e.Offset(0, 3).Value
.TextBox5.Value = e.Offset(0, 4).Value
.TextBox6.Value = e.Offset(0, 5).Value
.TextBox7.Value = e.Offset(0, 6).Value
.TextBox8.Value = e.Offset(0, 8).Value
.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbAdd.Enabled = False 'don't want to duplicate record
If e.Offset(0, 7).Value = "Yes" Then .optYes = True
If e.Offset(0, 7).Value = "No" Then .optYes = True
r = e.Row
h = 0
End With
FirstAddress = e.Address
Do
h = h + 1 'count number of matching records
Set e = .FindNext(e)
Loop While Not e Is Nothing And e.Address <> FirstAddress
If h > 1 Then
Select Case MsgBox("There are " & h & " instances of " & strFind2, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
Case vbOK
FindAll
Case vbCancel
'do nothing
End Select
Me.Height = frmMax
End If
Else: MsgBox strFind & " not listed" 'search failed
End If
End With
End Sub