Excel Help

rctneil · 12 Sep 2009 at 12:43

Hello,
There are two things I would like help with doing in Excel.

1.) How can I add a button to a spreadsheet that will jump to a different sheet?

2.) I ahve 3 sheets where I want to search for a text string. Currently to do this I have to press Control-F and then change the options to say search in every sheet and then search for what I want to find. Can I have a text field at the top of each sheet where I can type what I wnat and press a "Find" button and it do it's normal search but automatically search over all the sheets in the document?

Thanks

Regards,
Neil

geuben · 12 Sep 2009 at 12:46

I'm pretty sure the button can be done using VBA, the search can probably be done in VBA too but might be a bit more difficult. The downside with using VBA is that you have to enable macros which can be a problem if you're planning on running this spreadsheet on someone elses controlled system/network.

rctneil · 12 Sep 2009 at 12:47

This document is for my own personal and sole use.

I would need tutorials on how to do these though

geuben · 12 Sep 2009 at 12:49

Ok, I could probably knock together some instructions on how to do it, but it would no doubt be far from optimal. I'll wait a bit and see if anyone else can assist you.

rctneil · 12 Sep 2009 at 12:51

That would be great Geuben. I'll wait for a bit too see what other people come up with but some step by steps would be most appreciated.

Thanks

geuben · 12 Sep 2009 at 12:52

oh, which version of excel are you using?

rctneil · 12 Sep 2009 at 12:52

I am using 2010 beta but It's similar to 2007.

Namsnik · 12 Sep 2009 at 19:04

Instead of a button Insert a hyperlink to a "place in this workbook".

Find remembers the last settings used so it isn't that bad is it?

Tools,Macro, Record, in this workbook
Manually perform the find
Stop the recorder

Tools macro, Edit to view and modify the code. (If you are recording to Personal.xls then you can Step into the macro then press stop)

I got this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/09/2009 by ...
'
Cells.Find(What:="sh", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

Change to
What:=Activesheet.range("A1").value

Namsnik · 12 Sep 2009 at 19:24

You asked for a button. I wouldn't bother cause you would need it on each sheet etc. Better just assign a key combination to fire the macro.
Tools, Macro, Macros, select the macro then click Options.