#1
|
|||
|
|||
UserForm Search with multiple TextBoxes
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
search on multiple word documents | Guy Roth | Word | 7 | 03-06-2017 01:31 PM |
UserForm Search, delete issues | johndough | Excel Programming | 3 | 05-11-2014 11:44 AM |
Multiple words, one search | return2300 | Word VBA | 0 | 08-30-2013 12:26 PM |
Excel - search Multiple Values (HELP!) | duskdjl | Excel | 4 | 03-28-2013 01:11 AM |
Search with multiple strings | silverspr | Excel | 7 | 03-03-2013 04:45 PM |