Macro for finding cells

Hi there I'm new to this forum but wondered if anyone could help me!? I have a workbook which has severalsheets in, on my main menu page I would like a macro so that when the user clicks a button the 'Find' window pops up (for the lazy people who don't know to use Ctrl+F). Also when searching for data it needs to search every sheet in the workbook. At the moment when i try to record the macro it doesn't work - simply because when the find window appears i then have to close it before stopping the macro from recording - so when the macro runs it doesn't actually appear! Can anyone help?
Thanks

do you want a custom find box by which i mean if you want some thing to pop up and say good morning what do you want to find??? you type it in it searchs and then takes you to it??

hi there, I'd like the user to enter a persons surname into the find box, or the title of a course and it takes them to the appropriate cell - this could be in any one of seven worksheets. So yes a custom find box would be ideal but didn't know how to do it in Excel or if it was even possible - VB isn't my strongest point!

Originally Posted by _nic_v

hi there, I'd like the user to enter a persons surname into the find box, or the title of a course and it takes them to the appropriate cell - this could be in any one of seven worksheets. So yes a custom find box would be ideal but didn't know how to do it in Excel or if it was even possible - VB isn't my strongest point!

i take it you know how to create a button. then if you add this code to a module

Sub test()
' inbetween the "" part change to what ever you want it to say
y = InputBox("What are you looking for?")


For x = 1 To ActiveWorkbook.Sheets.Count ' this counts the sheets and repeats the below as many times as there are sheets

Sheets(x).Activate

'the below part searches for what the user typed in


Set abc = Cells.Find(What:=y, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False)


Next x
If abc Is Nothing Then
' If PC can't find value it tells you it can not
' in between the "" part change to what ever you want it to say
MsgBox "Sorry I could not find what you want!"

Else
'if it does find it will take you to it
abc.Activate
End If
End Sub

Hope this helps if you have any trouble with it let me know and yeah please let me know if it is exactly what you wanted?

Well it sort of works - I can see how it would work but every thing I search for brings back that it couldn't find what I'm looking for, even though I'm testing it with surnames which I know are definately in the worksheets. If it makes any difference then the only column that needs to be searched is A as this contains people's surnames (which is what I would like to search for).

Also, don't know if this is possible, but in some instances there may be more than one person with that surname but their records may be on different sheets - how would the user know that there are (for example) 3 people to view.

Sorry to be a pain but I wouldn't know where to start with something like this!

Hi again sorry I've figured out what it is doing - it is only seraching the last worksheet instead of all of them.....any ideas?

attact the file and let me have a look.

okay I have had to delete the content as it is sensitive data but have left a surname (column A) Forename (column B) and training course (Column C) in each worksheet. at the moment it will only find results in the Leavers worksheet.

Thanks for this!

Originally Posted by _nic_v

okay I have had to delete the content as it is sensitive data but have left a surname (column A) Forename (column B) and training course (Column C) in each worksheet. at the moment it will only find results in the Leavers worksheet.

Thanks for this!

here is your file back hope its what you looking for.

Yep that's exactly what I'm looking for! Thank you so much you're a star!

Originally Posted by _nic_v

Yep that's exactly what I'm looking for! Thank you so much you're a star!

thanks for letting me know

Hello..

Thanks for the macro code to find cells value, but I found an error. this macro doesn't run when I use cell values instead inputbox's data.

Thanks for all support

Rolando.
San Pedro Sula, Honduras

have your changed any part of the code?