MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

cmdFind for multiple Text and Combo Boxes

  Asked By: Ruben    Date: Sep 07    Category: MS Office    Views: 1217

I am trying to make my form which I have made on Excel VBA to find a
name on the sheet and display it along with all other instances of
that name. can anyone guide me in making a simple find function for my
find button Even if it is only for one textbox just to start me off I
can then add it for the rest of them.

I have trouble understanding how to code arrays and call information
to them and from them. Begginner here. I just finished the close
button and the add button.



4 Answers Found

Answer #1    Answered By: Khadeeja Malik     Answered On: Sep 07

How are you wanting to "display it along with all other instances  of
that name"?
Are you wanting to hide rows that do not have the name?
Is the name going to be in a specific column?
Are you planning to have the user key in the name?
How many unique names will there be?
Perhaps you can have the form  read in the values in the name column
and put the unique names into an array. then load the array into a
combobox. User selects the name, then what do you want to have happen?

If the names are in a specific column, you can always use filters.

Answer #2    Answered By: Bohdana Nonob     Answered On: Sep 07

I am trying to display  my found instances  in a listbox at the bottom of
the form. Where I can then select the names that I want to amend or delete.

I have all the input boxes  and Combo Boxes in a Frame where they can be put
into the right columns in the spreadsheet. My add button  works and so does
my close button. I am now trying to understand how to make the form  do a
search for something and display it. If I am doing names and there are 5
franks and all have different info I want to be able to search and find  all
the franks and display all their information in rows inside the listbox.

Answer #3    Answered By: Atid Boonliang     Answered On: Sep 07

How do you plan to have the user enter the search criteria?
textbox  with a "Search" or "Filter List" button?
or a change event with the text  box?

Either way,
I believe what you have to do is use the ListBox.RemoveAll method, then
use a loop:
For inx = 0 To AddEntry.cmbFund1.ListCount - 1
If (InStr(1, ComboBox.List(inx), StringBeingSearched) > 0) Then
Listbox.additem combobox.list(inx)
msg = msg & Chr(13) & combobox.list(inx)
End If
Next inx
MsgBox msg

The listbox will then have only entries that contain the string being searched.

Of course, I didn't test this with your data, but I've done simliar.

Answer #4    Answered By: Hayden Evans     Answered On: Sep 07

I found some code  for what I want to do which is working with arrays.
And when I tested it worked ok until it started to populate the listbox.

Private Sub cmbFindAll_Click()

Dim FirstAddress As String

Dim strFind As String 'what to find

Dim rSearch As Range 'range to search

Dim fndA, fndB, fndC, fndD, fndE, fndF As String

Dim head1, head2, head3, head4, head5, head6 As String 'heading s
for list

Dim i As Integer

i = 1

Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))

strFind = Me.TextBox1.Value

With rSearch

Set c = .Find(strFind, LookIn:=xlValues)

If Not c Is Nothing Then 'found it


'load the headings

head1 = Range("a5").Value

head2 = Range("b5").Value

head3 = Range("c5").Value

head4 = Range("d5").Value

head5 = Range("e5").Value

With Me.ListBox1

MyArray(0, 0) = head1

MyArray(0, 1) = head2

MyArray(0, 2) = head3

MyArray(0, 3) = head4

MyArray(0, 4) = head5

MyArray(0, 5) = head6

End With

FirstAddress = c.Address


'Load details into Listbox

fndA = c.Value

fndB = c.Offset(0, 1).Value

fndC = c.Offset(0, 2).Value

fndD = c.Offset(0, 3).Value

fndE = c.Offset(0, 4).Value

fndF = c.Offset(0, 5).Value

MyArray(i, 0) = fndA

MyArray(i, 1) = fndB

MyArray(i, 2) = fndC

MyArray(i, 3) = fndD

MyArray(i, 4) = fndE

MyArray(I, 5) = fndF

i = i + 1

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> FirstAddress

End If

End With

'Load data into LISTBOX

Me.ListBox1.List() = MyArray

End Sub

There is works though the listbox doesn’t fill in the information for head5
or head6. Also fndE and fndF doesn’t get inputed in the listbox aswell.

Option Explicit

Dim MyArray(7, 5)

Public MyData As Range, c As Range

This is how the person who made  the original code set the Array. Can you
help me understand how to make this code work.

I am having a search being done from a text  box. And then hitting a find
button. Then a message box shows up that tells me how many names it found.
After that I click on the following command button find  All the listbox
should be filled in with the array information.

Thankyou for helping me. I am looking at your code to see if it will make my
task easier. Basically I am making  an input form  for my students that I
teach to input grades and do searches for certain tests and scores so I can
more easily provide the information to the office desk where I work . I
work in Japan at a college as an English teacher.

Didn't find what you were looking for? Find more on cmdFind for multiple Text and Combo Boxes Or get search suggestion and latest updates.