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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
I must be doing something wrong, because it is not working at all. Keep
getting an object required error.
 
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?
 
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'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.
 
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.
 
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
 
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.
 
Yea, nothing is working.

Thanks though.

Gord Dibben said:
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
Click to expand...
 
Wow!, I got it to work. Thanks Chad

Chad said:
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
Click to expand...
 
Back
Top