+ Reply to Thread
Results 1 to 13 of 13

Creating a Search Form

  1. #1
    Karen
    Guest

    Creating a Search Form

    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.



  2. #2
    JulieD
    Guest

    Re: Creating a Search Form

    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

    "Karen" <Karen@discussions.microsoft.com> wrote in message
    news:6E81D3A5-482E-453C-9DE1-31F47116B9CE@microsoft.com...
    >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.
    >
    >




  3. #3
    Dave Peterson
    Guest

    Re: Creating a Search Form

    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)


    Karen wrote:
    >
    > 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.


    --

    Dave Peterson

  4. #4
    Karen
    Guest

    Re: Creating a Search Form

    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" wrote:

    > 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
    >
    > "Karen" <Karen@discussions.microsoft.com> wrote in message
    > news:6E81D3A5-482E-453C-9DE1-31F47116B9CE@microsoft.com...
    > >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.
    > >
    > >

    >
    >
    >


  5. #5
    JulieD
    Guest

    Re: Creating a Search Form

    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


    "Karen" <Karen@discussions.microsoft.com> wrote in message
    news:92DD9812-FA71-4E8F-8A44-3E0DCD1E4C02@microsoft.com...
    >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" wrote:
    >
    >> 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
    >>
    >> "Karen" <Karen@discussions.microsoft.com> wrote in message
    >> news:6E81D3A5-482E-453C-9DE1-31F47116B9CE@microsoft.com...
    >> >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.
    >> >
    >> >

    >>
    >>
    >>




  6. #6
    Karen
    Guest

    Re: Creating a Search Form

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

    "JulieD" wrote:

    > 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
    >
    >
    > "Karen" <Karen@discussions.microsoft.com> wrote in message
    > news:92DD9812-FA71-4E8F-8A44-3E0DCD1E4C02@microsoft.com...
    > >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" wrote:
    > >
    > >> 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
    > >>
    > >> "Karen" <Karen@discussions.microsoft.com> wrote in message
    > >> news:6E81D3A5-482E-453C-9DE1-31F47116B9CE@microsoft.com...
    > >> >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.
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >


  7. #7
    Karen
    Guest

    Re: Creating a Search Form

    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" wrote:

    > 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)
    >
    >
    > Karen wrote:
    > >
    > > 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.

    >
    > --
    >
    > Dave Peterson
    >


  8. #8
    Dave Peterson
    Guest

    Re: Creating a Search Form

    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


    Karen wrote:
    >
    > 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" wrote:
    >
    > > 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)
    > >
    > >
    > > Karen wrote:
    > > >
    > > > 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.

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

  9. #9
    Karen
    Guest

    Re: Creating a Search Form

    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" wrote:

    > 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
    >
    >
    > Karen wrote:
    > >
    > > 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" wrote:
    > >
    > > > 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)
    > > >
    > > >
    > > > Karen wrote:
    > > > >
    > > > > 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.
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >

    >
    > --
    >
    > Dave Peterson
    >


  10. #10
    Karen
    Guest

    Re: Creating a Search Form

    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

    "Karen" wrote:

    > 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" wrote:
    >
    > > 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
    > >
    > >
    > > Karen wrote:
    > > >
    > > > 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" wrote:
    > > >
    > > > > 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)
    > > > >
    > > > >
    > > > > Karen wrote:
    > > > > >
    > > > > > 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.
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


  11. #11
    Dave Peterson
    Guest

    Re: Creating a Search Form

    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.

    Karen wrote:
    >
    > 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
    >
    > "Karen" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > Karen wrote:
    > > > >
    > > > > 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" wrote:
    > > > >
    > > > > > 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)
    > > > > >
    > > > > >
    > > > > > Karen wrote:
    > > > > > >
    > > > > > > 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.
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Dave Peterson
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >


    --

    Dave Peterson

  12. #12
    Karen
    Guest

    Re: Creating a Search Form

    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" wrote:

    > 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.
    >
    > Karen wrote:
    > >
    > > 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
    > >
    > > "Karen" wrote:
    > >
    > > > 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" wrote:
    > > >
    > > > > 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
    > > > >
    > > > >
    > > > > Karen wrote:
    > > > > >
    > > > > > 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" wrote:
    > > > > >
    > > > > > > 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)
    > > > > > >
    > > > > > >
    > > > > > > Karen wrote:
    > > > > > > >
    > > > > > > > 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.
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > Dave Peterson
    > > > > > >
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >

    >
    > --
    >
    > Dave Peterson
    >


  13. #13
    Dave Peterson
    Guest

    Re: Creating a Search Form

    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.



    Karen wrote:
    >
    > 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" wrote:
    >
    > > 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.
    > >
    > > Karen wrote:
    > > >
    > > > 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
    > > >
    > > > "Karen" wrote:
    > > >
    > > > > 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" wrote:
    > > > >
    > > > > > 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
    > > > > >
    > > > > >
    > > > > > Karen wrote:
    > > > > > >
    > > > > > > 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" wrote:
    > > > > > >
    > > > > > > > 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)
    > > > > > > >
    > > > > > > >
    > > > > > > > Karen wrote:
    > > > > > > > >
    > > > > > > > > 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.
    > > > > > > >
    > > > > > > > --
    > > > > > > >
    > > > > > > > Dave Peterson
    > > > > > > >
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Dave Peterson
    > > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1