Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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 he 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
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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
|
|||
|
|||
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 he 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
|
|||
|
|||
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 he 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
|
|||
|
|||
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 he 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
|
|||
|
|||
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 he 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
|
|||
|
|||
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 he 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
|
|||
|
|||
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 he 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
|
|||
|
|||
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 he 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilling data on a form | New Users to Excel | |||
FAQ Spreadsheet with search function | Excel Discussion (Misc queries) | |||
View of Excel form is different on different computers. | Excel Discussion (Misc queries) | |||
Sending a Form | Excel Discussion (Misc queries) | |||
Trying to delete form border, and it deletes all borders | Excel Discussion (Misc queries) |