Creating a Search Form

[Guest]

I have a simple address book spreadsheet. I would like to create a Search
form, where the user can enter the Last Name of the person they are searching
for.

 

[JulieD]

Hi Karen

two inbuilt options that you might like to have a look at as long as your
data is in "database / list" format
ie something like
..........A..............B.....................C......................D
1.....First Name...Last Name.....Address.........Town.
2.....Joe..............Bloggs............15 North Rd.....Little Parks

click in cell A2 choose data / filter / autofilter - you'll see a drop down
arrow for each column, click on the one for last name and choose the person
you're interested in

or two
click in cell A2 and choose Data / form - click on the Criteria button,
type the last name in the last name field and click Find Next

if neither of these two are acceptable, could you explain a bit more on what
you mean by "form" - do you want one like the data / form one, or do you
just want a cell where they can type the name in or a combo box where they
can select it from

Cheers
JulieD

 

[Dave Peterson]

A couple of options...
1. Have the user select the column and hit ctrl-f (short cut for Edit|find).
2. Apply Data|filter|autofilter
(and show them how to use the dropdown arrow)
3. Download Jan Karel Pieterse's FlexFind.
You can find it here:
http://www.oaltd.co.uk/MVP/
(it has some nice options already built in)

 

[Guest]

I want a stand alone form. The user won't see the spreadsheet where the
information is kept. Something like the example below:

Search for:

Where it: [Drop down: Begins In OR Contained In]
the Employee's [Drop Down: First Name OR Last Name]

 

[JulieD]

Hi Karen

then you'll need to create a userform in the VBE window and code it. Have
you done any work with VBA before?

Cheers
JulieD

I want a stand alone form. The user won't see the spreadsheet where the
information is kept. Something like the example below:

Search for:

Where it: [Drop down: Begins In OR Contained In]
the Employee's [Drop Down: First Name OR Last Name]

Hi Karen

two inbuilt options that you might like to have a look at as long as your
data is in "database / list" format
ie something like
..........A..............B.....................C......................D
1.....First Name...Last Name.....Address.........Town.
2.....Joe..............Bloggs............15 North Rd.....Little Parks

click in cell A2 choose data / filter / autofilter - you'll see a drop
down
arrow for each column, click on the one for last name and choose the
person
you're interested in

or two
click in cell A2 and choose Data / form - click on the Criteria button,
type the last name in the last name field and click Find Next

if neither of these two are acceptable, could you explain a bit more on
what
you mean by "form" - do you want one like the data / form one, or do you
just want a cell where they can type the name in or a combo box where
they
can select it from

Cheers
JulieD

 

[Guest]

Not really. I have worked a little bit with it, but not much.

Hi Karen

then you'll need to create a userform in the VBE window and code it. Have
you done any work with VBA before?

Cheers
JulieD

I want a stand alone form. The user won't see the spreadsheet where the
information is kept. Something like the example below:

Search for:

Where it: [Drop down: Begins In OR Contained In]
the Employee's [Drop Down: First Name OR Last Name]

Hi Karen

two inbuilt options that you might like to have a look at as long as your
data is in "database / list" format
ie something like
..........A..............B.....................C......................D
1.....First Name...Last Name.....Address.........Town.
2.....Joe..............Bloggs............15 North Rd.....Little Parks

click in cell A2 choose data / filter / autofilter - you'll see a drop
down
arrow for each column, click on the one for last name and choose the
person
you're interested in

or two
click in cell A2 and choose Data / form - click on the Criteria button,
type the last name in the last name field and click Find Next

if neither of these two are acceptable, could you explain a bit more on
what
you mean by "form" - do you want one like the data / form one, or do you
just want a cell where they can type the name in or a combo box where
they
can select it from

Cheers
JulieD

I have a simple address book spreadsheet. I would like to create a
Search
form, where the user can enter the Last Name of the person they are
searching
for.

 

[Guest]

I really don't want the user to see the spreadsheet, just the information
they are searching for. I was hoping for a form for them to enter the search
criteria.

 

[Dave Peterson]

I put my "address book" in sheet1.

I added a sheet2 and put some instructions on that worksheet--along with a
button from the Forms toolbar that had a macro assigned to it that would show
the userform.

This was the macro (located in a general module):

Option Explicit
Sub testme()
UserForm1.Show
End Sub

I had headers in Row 1. Last Name in column A and other stuff in B:F (6 columns
total).

Then I created a userform.

I had one textbox, one checkbox, one listbox and two buttons.

The textbox was used to get the name to search for.
The checkbox was used to indicate contains or exact (checked means contains)
The listbox returned all the names that matched--and its associated columns
(B:F).
The commandbuttons did the work or closed the userform.

This was the code behind the userform:

Option Explicit
Dim myRng As Range
Dim myNameRng As Range
Private Sub CommandButton1_Click()

Dim myCell As Range
Dim VisNameRng As Range
Dim StrToFind As String
Dim iCol As Long

Me.ListBox1.Clear

If Trim(Me.TextBox1.Value) = "" Then
Beep
Exit Sub
End If

StrToFind = Me.TextBox1.Value
myRng.Parent.AutoFilterMode = False

If Me.CheckBox1.Value = True Then
StrToFind = "*" & StrToFind & "*"
End If

With myRng
'lastname in column A
Set myNameRng = .Columns(1)
End With

With myNameRng
.AutoFilter field:=1, Criteria1:=StrToFind
Set VisNameRng = Nothing
On Error Resume Next
Set VisNameRng _
= .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

If VisNameRng Is Nothing Then
MsgBox "Name not found!"
Exit Sub
End If

For Each myCell In VisNameRng.Cells
With Me.ListBox1
.AddItem myCell.Value
For iCol = 2 To myRng.Columns.Count
.List(.ListCount - 1, iCol - 1) _
= myCell.Offset(0, iCol - 1).Text
Next iCol
End With
Next myCell

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()

With Worksheets("sheet1")
'resized to 6 columns
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 6)
End With

Me.ListBox1.ColumnCount = myRng.Columns.Count

Me.CommandButton1.Caption = "Go"
Me.CommandButton2.Caption = "Cancel"
Me.CheckBox1.Caption = "Contains?"

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you're new to Userforms, you may want to read Debra Dalgleish's notes at:
http://www.contextures.com/xlUserForm01.html

 

[Guest]

I used the code below. I am getting the following error.

Run-time Error "9": Subscript out of range

Do you happen to know what that might mean?

 

[Guest]

I think I fixed the Run-Time Error "9", but now I am receiving this error:

Run-time Error '91': Object Varable or With Block variable not set for the
code:
myRng.Parent.AutoFilterMode = False

I used the code below. I am getting the following error.

Run-time Error "9": Subscript out of range

Do you happen to know what that might mean?

 

[Dave Peterson]

This portion:

With Worksheets("sheet1")
'resized to 6 columns
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 6)
End With

could be changed to:

With activesheet
'resized to 6 columns
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 6)
End With

To run against the active sheet.

That's my guess.

 

[Guest]

Do I need to set something regarding the code "myRng.Parent.AutoFilterMode =
False"? That is were the debugger stops for the RunTime Error

 

[Dave Peterson]

Yes, but I don't have a guess. The code I posted worked for me.

I'm guessing that you made a minor (but significant change). You'll have to
post the code you're using.

Do I need to set something regarding the code "myRng.Parent.AutoFilterMode =
False"? That is were the debugger stops for the RunTime Error