Form to sort data
 

I'd like to create a form so that users can generate a contact list (name,
address, phone, etc.) based on certain criteria. I have a master contact
list of 200+ individuals who have expressed interest in a number of different
issues. I'd like to be able to sort through the list to select, for example,
only those individuals who are interested in issues A, B, or C.

Right now the data is set up so that if someone is interested in issue a,
there is a "y" in that column; otherwise the field is blank.

Is there a simple tutorial somewhere on how to set up a *very* simple form
to help users sort through the data to get the list they need quickly?

thanks, in advance, for any help you may be able to provide!

Form to sort data
 

I just created this for someone else, sounds like you might be able to use
it or adapt it. This example requires a userform with 2 comboboxes and a
button, you can select in the code which dropdown will have which data and
AutoFilter with it.


Dim lastCell As Long
Private Sub ComboBox1_Change()
Rows("1:1").Select 'this is the row number of the header for the AutoFilter
Selection.AutoFilter
If ComboBox1 < "" Then Selection.AutoFilter Field:=1,
Criteria1:=ComboBox1.Value 'change the field to the Name column

End Sub

Private Sub ComboBox2_Change()
If ComboBox2 < "" Then Selection.AutoFilter Field:=2,
Criteria1:=ComboBox2.Value 'ditto for the date
End Sub

Private Sub CommandButton1_Click()
UserForm1.Hide

End Sub

Private Sub UserForm_Initialize()

Sheets("sheet1").AutoFilterMode = False 'turns off filter

lastCell = Cells(Rows.Count, "A").End(xlUp).Row 'this is the last cell with
data, change the "A" to the column with the names

For i = 2 To lastCell ' I start with 2 because the headers are in 1
ComboBox1.AddItem (Cells(i, 1)) 'this assumes Name is in Column 1 or A
ComboBox2.AddItem (Cells(i, 2)) 'this assumes the dates are in Column 2 or B
Next

End Sub


--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"evelyn" wrote in message
...
I'd like to create a form so that users can generate a contact list (name,
address, phone, etc.) based on certain criteria. I have a master contact
list of 200+ individuals who have expressed interest in a number of
different
issues. I'd like to be able to sort through the list to select, for
example,
only those individuals who are interested in issues A, B, or C.

Right now the data is set up so that if someone is interested in issue a,
there is a "y" in that column; otherwise the field is blank.

Is there a simple tutorial somewhere on how to set up a *very* simple form
to help users sort through the data to get the list they need quickly?

thanks, in advance, for any help you may be able to provide!

Form to sort data
 

Thanks so much for the speedy reply. I've created simple macros before, but
haven't ever created anything that looks like the code that you've provided.
How exactly do I plug this code in? Do I cut and past it into the Visual
Basic Editor somehow?

Thanks!



"John Bundy" wrote:

I just created this for someone else, sounds like you might be able to use
it or adapt it. This example requires a userform with 2 comboboxes and a
button, you can select in the code which dropdown will have which data and
AutoFilter with it.


Dim lastCell As Long
Private Sub ComboBox1_Change()
Rows("1:1").Select 'this is the row number of the header for the AutoFilter
Selection.AutoFilter
If ComboBox1 < "" Then Selection.AutoFilter Field:=1,
Criteria1:=ComboBox1.Value 'change the field to the Name column

End Sub

Private Sub ComboBox2_Change()
If ComboBox2 < "" Then Selection.AutoFilter Field:=2,
Criteria1:=ComboBox2.Value 'ditto for the date
End Sub

Private Sub CommandButton1_Click()
UserForm1.Hide

End Sub

Private Sub UserForm_Initialize()

Sheets("sheet1").AutoFilterMode = False 'turns off filter

lastCell = Cells(Rows.Count, "A").End(xlUp).Row 'this is the last cell with
data, change the "A" to the column with the names

For i = 2 To lastCell ' I start with 2 because the headers are in 1
ComboBox1.AddItem (Cells(i, 1)) 'this assumes Name is in Column 1 or A
ComboBox2.AddItem (Cells(i, 2)) 'this assumes the dates are in Column 2 or B
Next

End Sub


--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"evelyn" wrote in message
...
I'd like to create a form so that users can generate a contact list (name,
address, phone, etc.) based on certain criteria. I have a master contact
list of 200+ individuals who have expressed interest in a number of
different
issues. I'd like to be able to sort through the list to select, for
example,
only those individuals who are interested in issues A, B, or C.

Right now the data is set up so that if someone is interested in issue a,
there is a "y" in that column; otherwise the field is blank.

Is there a simple tutorial somewhere on how to set up a *very* simple form
to help users sort through the data to get the list they need quickly?

thanks, in advance, for any help you may be able to provide!

Form to sort data
 

shoot me an e-mail am and I will send you a book with
it in working order and you can try from there

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"evelyn" wrote in message
...
Thanks so much for the speedy reply. I've created simple macros before,
but
haven't ever created anything that looks like the code that you've
provided.
How exactly do I plug this code in? Do I cut and past it into the Visual
Basic Editor somehow?

Thanks!



"John Bundy" wrote:

I just created this for someone else, sounds like you might be able to
use
it or adapt it. This example requires a userform with 2 comboboxes and a
button, you can select in the code which dropdown will have which data
and
AutoFilter with it.


Dim lastCell As Long
Private Sub ComboBox1_Change()
Rows("1:1").Select 'this is the row number of the header for the
AutoFilter
Selection.AutoFilter
If ComboBox1 < "" Then Selection.AutoFilter Field:=1,
Criteria1:=ComboBox1.Value 'change the field to the Name column

End Sub

Private Sub ComboBox2_Change()
If ComboBox2 < "" Then Selection.AutoFilter Field:=2,
Criteria1:=ComboBox2.Value 'ditto for the date
End Sub

Private Sub CommandButton1_Click()
UserForm1.Hide

End Sub

Private Sub UserForm_Initialize()

Sheets("sheet1").AutoFilterMode = False 'turns off filter

lastCell = Cells(Rows.Count, "A").End(xlUp).Row 'this is the last cell
with
data, change the "A" to the column with the names

For i = 2 To lastCell ' I start with 2 because the headers are in 1
ComboBox1.AddItem (Cells(i, 1)) 'this assumes Name is in Column 1 or A
ComboBox2.AddItem (Cells(i, 2)) 'this assumes the dates are in Column 2
or B
Next

End Sub


--
--
-John
Please rate when your question is answered to help us and others know
what
is helpful.

"evelyn" wrote in message
...
I'd like to create a form so that users can generate a contact list
(name,
address, phone, etc.) based on certain criteria. I have a master
contact
list of 200+ individuals who have expressed interest in a number of
different
issues. I'd like to be able to sort through the list to select, for
example,
only those individuals who are interested in issues A, B, or C.

Right now the data is set up so that if someone is interested in issue
a,
there is a "y" in that column; otherwise the field is blank.

Is there a simple tutorial somewhere on how to set up a *very* simple
form
to help users sort through the data to get the list they need quickly?

thanks, in advance, for any help you may be able to provide!