Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

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

Can I create a macro that works like the "find' function
 

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.

Can I create a macro that works like the "find' function
 

This means what?

Gord

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

Yea, nothing is working.