Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Using Case to do a multi-textbox search

  Asked By: Matilda    Date: Oct 29    Category: MS Office    Views: 1467
  

I would like to use case to do a search from any textbox

I know it can be done with an if statement. But from this point I can
only do searches for one textbox at a time. I just can't seem to
understand how to do a search where I can use any information typed in
any textbox to do a search.

Then show that information in a listbox with the name always being first.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Ava Campbell     Answered On: Oct 29

We're going to need more information, including the code you already have.

"Case" does not search, so I don't know what you are wanting to do. Case is
a conditional statement  - not hugely different to IF. More specifically,
anything you can do with a Case you can also do with a chained IF.

 
Answer #2    Answered By: Brenda Fischer     Answered On: Oct 29

Here is my code. I am using strFind to let me search  for the value in
textbox1. I am using Case to fill in the info on the form. I would
like to change the search from just one textbox  into a search from any
textbox. I did get help from Paul but I do not know how to implement
the code. It is at the bottom of this reply. Thankyou.



Private Sub cmbFind_Click()
Dim strFind, FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Dim RecCnt As Integer
Dim i As Integer, n As Integer, e As Integer

Set rSearch = Sheet1.Range("a6", Range("q65000").End(xlUp))
strFind = Me.TextBox1.Value


Dim f As Integer
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
With Me 'load entry to form
.ComboBox1.Value = c.Offset(, 7).Value
.ComboBox2.Value = c.Offset(, 9).Value
.ComboBox3.Value = c.Offset(, 13).Value
.ComboBox4.Value = c.Offset(0, 14).Value
.ComboBox5.Value = c.Offset(, 11).Value
For n = 1 To 12
Select Case n
Case 1 To 7
.Controls("TextBox" & n).Value =
c.Offset(i, n - 1).Value
Case 8
.Controls("TextBox" & n).Value =
c.Offset(i, 10).Value
Case 9
.Controls("TextBox" & n).Value =
c.Offset(i, 12).Value
Case 10
.Controls("TextBox" & n).Value =
c.Offset(i, 8).Value
Case 11 To 12
.Controls("TextBox" & n).Value =
c.Offset(i, n + 4).Value
End Select
Next
.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbAdd.Enabled = False 'don't want to duplicate
record
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
MsgBox "There are " & f & " instances of " & strFind
Me.Height = 456
End If
Else: MsgBox strFind & " not listed" 'search failed
End If
End With
End Sub




hdr_row = 6
RecCnt = application.worksheetfunction.counta(range(cells(hdr_Row +
1,1),cells(65000,1) 'or whatever the range is for I = 1 to RecCnt
'(keep in mind that this is a count of records, not rows!
SelectFlag = false
if (txtbox1.value = "") or (instr(1,cells(i,1),textbox1.value) > 0)
then
if (txtbox2.value = "") or (instr(1,cells(i,2),textbox2.value) >
0) then
selectflag = true
end if
end if
if selectflag then
'Add line to ListBox
end if
next I


 
Answer #3    Answered By: Tasha Wheeler     Answered On: Oct 29

Are you saying that:
You have a spreadsheet that you wish to search.
You have a userform with several textboxes, and a listbox.
When the user makes entries in any of the textboxes,
you want the listbox  to contain the records from the spreadsheet
that contain these values.

Does that sum it up?

You could use a change event for each of the textboxes.
Have the change event pass the value of the textbox  to a
String_Search function. That way you're not duplicating the search
code. You can even pass the fieldname.
Say you have a textbox called TxtFirstName and TxtLastName.
You can then create the change events:
'--------------------------
Private Sub TxtFirstName_Change()
stat = Search_String("FirstName", txtFirstName.Text)
End Sub
Private Sub TxtLastName_Change()
stat = Search_String("LastName", txtLastName.Text)
End Sub
'------------------
then create the function:
'------------------------
Function Search_String(ByVal FieldName, ByVal FieldVal)
Select Case UCase(FieldName)
Case "FIRSTNAME"
' insert find code here.
' either update an array and return to change event
' to update the listbox,
' or update the listbox from here.
Case "LASTNAME"
Case "ADDRESS"
Case Else
End Select
End Function

If the user has values in multiple textboxes, do you want to search
for records that contain ALL the values?
That would be a little trickier, but could be done.
You could still use the change event, but if there is a value in the
listbox, search  through those values instead of going back to the
original list.

Does this sound like it's leading down the path your heading?

 
Answer #4    Answered By: Gilbert Moore     Answered On: Oct 29

I have a spreadsheet which I wish to search  when the user makes an entry
into any of the textboxes or a selection from a combo box.



Then I want the listbox  to contain the records found from the search.



Though I want them ordered always in the following way



Name email address city zip telephone



Just like they are in the raw data spreadsheet.



So your first assestment is correct.



So this code would all go in the sub clickFind()??



'--------------------------
Private Sub TxtFirstName_Change()
stat = Search_String("FirstName", txtFirstName.Text)
End Sub
Private Sub TxtLastName_Change()
stat = Search_String("LastName", txtLastName.Text)
End Sub
'------------------
then create the function:
'------------------------
Function Search_String(ByVal FieldName, ByVal FieldVal)
Select Case UCase(FieldName)
Case "FIRSTNAME"
' insert find code here.
' either update an array and return to change event
' to update the listbox,
' or update the listbox from here.
Case "LASTNAME"
Case "ADDRESS"
Case Else
End Select
End Function

 
Answer #5    Answered By: Adalwolfa Fischer     Answered On: Oct 29

What I'm suggesting it to create a function in a module:

Function Search_String(ByVal FieldName, ByVal FieldVal)

Then, for each of the textboxes, create a change event.
oops.. just realized (and tested) the change event would execute for each
letter typed.
You should use either an Update event, or an Exit event.
The difference is: the Exit event would execute whenever you select
something OUTSIDE of the textbox. The Update event would execute after you
exit but only if you changed something in the textbox.

In the Exit or Update event, test to see if the textbox  has a value
(in case  they CLEARED the textbox)
like:
if (len(txtLastName.Text) > 0) then

then call the function, passing it the name of the textbox (or some other
indicator)
and the value in the textbox.

Then, in the function, begin by clearing the contents of the listbox, then
search through the worksheet for the textbox value, and load the listbox.

Your next decision is this: Do you want ALL textbox values to be searched
or just the latest?

For example,
If I enter "Oul" in the First Name box, the listbox  will show  all names
with "Oul" as a first name. If I then enter "Schreiner" in the last Name
box, even though "Oul" is still in the FirstName box, the listbox will
show all "Schreiner's" even if the first name isn't "Oul". If you want
all criteria to be matched, what you should do is:
Check to see if the listbox has contents. If it's empty, search  the
worksheet and populate the listbox. If the listbox has a value, search
through the listbox rows and remove any that do not match the current
criteria. That way, you're "filtering" the previously selected list,
instead of going to the worksheet every time...

 
Answer #6    Answered By: Rebekah Walker     Answered On: Oct 29

I am unable to see quickly what your code is supposed to be doing. It is
too long and unexplained to be a reasonable quote in a message. And you
really are not saying where your problem is.

Please (1) cut down your code until it demonstrates your problem, and
nothing else, (2) explain exactly what your problem is, (3) provide
step-by-step examples of what needs to happen.

However, some general comments ...

> Dim strFind, FirstAddress As String

This code is wrong. strFind will be declared as variant, not string.
Please only put one declaration on each Dim statement. It's safer and also
easier to read.

> I am using strFind to let me search  ...

strfind is the name of a search function in MATLAB. You don't appear to be
using it. You have defined a local variable called strFind to hold
something from a text box, that you then use in a Find call.

> I am using case  to fill in the info on the form.

This is sort of true, but actually the Case statement  isn't achieving
anything.

You have a For loop from 1 to 12, then immediately use a Case statement to
break the For loop back out into separate statements again.

From what I can see, you could (and should) do this with 12 separate
assignment statements - one for each of the 12 text boxes. In fact, your
loop is 14 lines, so using separate assignment statements would not only be
easier to read, and quicker - it'd also be shorter.

> I would like to change the search from just one textbox
> into a search from any textbox.

The loop and the case statement are associated with putting things into
textboxes. They are not associated with your search, so I'm not sure why
they form a part of your description.

What do you mean by a search from any textbox? Do you mean that you want to
combine the information  from the textboxes in some way and use that as a
search key? Do you mean that you want the user to be able to specify which
text box to take the search string from? Do you mean that you want to try
the information from one textbox  after another until you get a find?

 
Didn't find what you were looking for? Find more on Using Case to do a multi-textbox search Or get search suggestion and latest updates.




Tagged: