What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

In my example, the sheets are named a, b, c, etc. Is this how you're
workbook is organized, or is there something that differs?

"doral" wrote:

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

"Chad" wrote:

In my example, the sheets are named a, b, c, etc. Is this how you're
workbook is organized, or is there something that differs?

"doral" wrote:

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

I'm not sure how to do it with a drop down list like that, but I'd use a
combo box or list box. Then, you can use the code I listed earlier.


"doral" wrote:

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

"Chad" wrote:

In my example, the sheets are named a, b, c, etc. Is this how you're
workbook is organized, or is there something that differs?

"doral" wrote:

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

I will try this, but I can't get my values of a through d into the combo box.

"Chad" wrote:

I'm not sure how to do it with a drop down list like that, but I'd use a
combo box or list box. Then, you can use the code I listed earlier.


"doral" wrote:

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

"Chad" wrote:

In my example, the sheets are named a, b, c, etc. Is this how you're
workbook is organized, or is there something that differs?

"doral" wrote:

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

Or even a list box, but I'd rather have a combo, since it is like a drop down.

"Chad" wrote:

I'm not sure how to do it with a drop down list like that, but I'd use a
combo box or list box. Then, you can use the code I listed earlier.


"doral" wrote:

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

"Chad" wrote:

In my example, the sheets are named a, b, c, etc. Is this how you're
workbook is organized, or is there something that differs?

"doral" wrote:

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

you can get values in either combo or list box by going to the properties and
putting "A1:A4" (for example) in the listfillrange. this will allow the box
to pull the values from cells a1 thru a4. then you can assign your code to
the box based upon the value selected.


"doral" wrote:

Or even a list box, but I'd rather have a combo, since it is like a drop down.

"Chad" wrote:

I'm not sure how to do it with a drop down list like that, but I'd use a
combo box or list box. Then, you can use the code I listed earlier.


"doral" wrote:

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

"Chad" wrote:

In my example, the sheets are named a, b, c, etc. Is this how you're
workbook is organized, or is there something that differs?

"doral" wrote:

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

Not sure what you need but if all you want is to select a sheet.

You could use sheet event code to go to the sheet required when you select a
value from the DV dropdown list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" And Target.Value < "" Then
Select Case Target.Value
Case "a"
Sheets("a").Activate
Case "b"
Sheets("b").Activate
Case "c"
Sheets("c").Activate
End Select
End If
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.


Gord Dibben MS Excel MVP



On Thu, 23 Aug 2007 06:54:32 -0700, doral
wrote:

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

I completely understand what you are telling me to do, and I remember
learning this in school, but when I put that range in the listfillrange, it
is not pulling anything. It works for the list box, but then it won't allow
me to scroll, it just selects the whole box.

"Chad" wrote:

you can get values in either combo or list box by going to the properties and
putting "A1:A4" (for example) in the listfillrange. this will allow the box
to pull the values from cells a1 thru a4. then you can assign your code to
the box based upon the value selected.


"doral" wrote:

Or even a list box, but I'd rather have a combo, since it is like a drop down.

"Chad" wrote:

I'm not sure how to do it with a drop down list like that, but I'd use a
combo box or list box. Then, you can use the code I listed earlier.


"doral" wrote:

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

"Chad" wrote:

In my example, the sheets are named a, b, c, etc. Is this how you're
workbook is organized, or is there something that differs?

"doral" wrote:

I must be doing something wrong, because it is not working at all. Keep
getting an object required error.

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

Yea, nothing is working.

Thanks though.

"Gord Dibben" wrote:

Not sure what you need but if all you want is to select a sheet.

You could use sheet event code to go to the sheet required when you select a
value from the DV dropdown list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" And Target.Value < "" Then
Select Case Target.Value
Case "a"
Sheets("a").Activate
Case "b"
Sheets("b").Activate
Case "c"
Sheets("c").Activate
End Select
End If
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.


Gord Dibben MS Excel MVP



On Thu, 23 Aug 2007 06:54:32 -0700, doral
wrote:

Yes, but I don't think I'm assigning the macro correctly. This is what I
did. a1=a, a2=b, a3=c, a4=d. Then I went data, validate and made a drop
down list in c1 with the values a through d. So how do I get that macro to
run off the drop down? Thanks

Wow!, I got it to work. Thanks Chad

"Chad" wrote:

Use code similar to the following for the listbox.

Private Sub ListBox1_Click()
gotosheet = ListBox1.Value
Sheets(gotosheet).Select
End Sub

Hope this helps,
Chad

"doral" wrote:

This didn't work. What I want to do say. I have a list a through d on first
sheet. Then I have 4 other sheets with a on one, b on one, etc to d. I want
to select from a drop down list a letter, say d, then have a textbox with a
macro assigned to it which would then find that letter in the other 4 sheets
(a through d). So after you would run the macro, it would go to sheet d.

"Chad" wrote:

I'm not sure if you're simply looking for 'find' feautre, try this:

texttofind = "blah blah blah" ' or whatever you're passing in to search for

Cells.Find(What:=texttofind, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Hope this helps,
Chad

"doral" wrote:

What I want to do is have a drop down list of names. After selected I would
like the macro to sort the various other sheets for the variable selected.
Is there a way to build a macro like this? Thank you.

This means what?

Gord

On Thu, 23 Aug 2007 08:36:02 -0700, doral
wrote:

Yea, nothing is working.