Pinkster69
New Member
- Joined
- Jun 19, 2012
- Messages
- 48
Hi Guys,
I have a form with 4 TextBoxe's "TexBox1,2,3 etc" and a ListBox1 were the whole idea is that you enter a value in TexBox1 and the VBA code looks for any and all items on your data "respective worksheet" and lists any/all data that it finds "corresponding to the value in TextBox 1" in the ListBox1, just like a database of sorts. Also note the other TextBoxe's 2,3,4 have info in them as well corresponding to the value you put in TextBox1 using the offset function.
Please note: The code I have WORKS for the condition above but what I am having problems with is that I want it to work for all the TextBoxes respectively!
What I mean by this is that when ever I enter data in TextBox 1 or 2 or 3 or 4 that the results will be listed in the ListBox1. The reason I need this is, if the info I require for TextBox1 isn't available I can search for the info using the other criteria in the other TextBoxes.
I have attached the code I have already so please feel free to modify it. If you require the test worksheet please let me know and I will email it on to you, this might be better to give you an idea to see it working.
Really could use your help guys??
PS: forgot to mention that when the code finds entries that relate to TextBox1 it comes up with a message window with the number of entries it found... just in case you were wondering about the code at the bottom of the cmdFind method.
I have a form with 4 TextBoxe's "TexBox1,2,3 etc" and a ListBox1 were the whole idea is that you enter a value in TexBox1 and the VBA code looks for any and all items on your data "respective worksheet" and lists any/all data that it finds "corresponding to the value in TextBox 1" in the ListBox1, just like a database of sorts. Also note the other TextBoxe's 2,3,4 have info in them as well corresponding to the value you put in TextBox1 using the offset function.
Please note: The code I have WORKS for the condition above but what I am having problems with is that I want it to work for all the TextBoxes respectively!
What I mean by this is that when ever I enter data in TextBox 1 or 2 or 3 or 4 that the results will be listed in the ListBox1. The reason I need this is, if the info I require for TextBox1 isn't available I can search for the info using the other criteria in the other TextBoxes.
I have attached the code I have already so please feel free to modify it. If you require the test worksheet please let me know and I will email it on to you, this might be better to give you an idea to see it working.
Really could use your help guys??
PS: forgot to mention that when the code finds entries that relate to TextBox1 it comes up with a message window with the number of entries it found... just in case you were wondering about the code at the bottom of the cmdFind method.
Code:
Dim MyData As Range
Dim c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range
Dim oCtrl As MSForms.Control
Private Sub cmbFind_Click()
Dim strFind As String 'what to find
Dim FirstAddress As String
Dim rSearch As Range 'range to search
Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))
Dim f As Integer
strFind = Me.TextBox1.Value 'what to look for
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
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
'BBBBBBBBBBBBBBBBBBBBB
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" 'search failed
End If
End With
If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter
End Sub
Sub FindAll()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Sheet1.Range("a8", Range("d65536").End(xlUp))
Set rng = Sheet1.Range("a7", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A8").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.ListBox1.Clear
For Each c In rng
With Me.ListBox1
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Offset(0, 1).Value
.List(.ListCount - 1, 2) = c.Offset(0, 2).Value
.List(.ListCount - 1, 3) = c.Offset(0, 3).Value
.List(.ListCount - 1, 4) = c.Offset(0, 4).Value
End With
Next c
End With
End Sub
ray:
