Navigating in Excel

[dutinming]

Good days! Need help: MS Excel supports hyperlinks to enable jumps to certain pre-determined locations (within workbook or external). To jump back, one method is to build a "Return to menu" hyperlink in every sheet. My question is, whether there exist methods to jump to the "calling location" instead of everytime back to the "menu" (for example) ?

VBA may be the solution that I can think of. Kindly advise where can I get such kind of VBA scripts (or Java, whatever that work) download, since I do not have experience in programming!

Regards.

 

[RealBlackStuff]

M$ has the answers:
http://office.microsoft.com/en-us/assistance/HP051995161033.aspx

 

[dutinming]

Thank you sir!

But I read thru the suggested MS Office help page, it still does not tell how to jump back to the calling location. It only advise how to establish hyperlinks to a specific location, instead.

To illustrate what I need, see the following:

<Example 1>
Sheet "Menu" -> Sheet "Equipment List" -> Sheet "Optical equipment" -> Sheet "Microscope"

From the final Sheet "Microscope", how to go back to the calling Sheet "Optical equipment" instead of the Sheet "Menu" ?

<Example 2>
Sheet "Equipment" -> Sheet "Microscope"
Wants to jump back to Sheet "Equipment" instead of "Meun"

Of course, it can put a full menu in every worksheet for hyperlinking to everywhere, but it is not the best way -- only want two buttons "<-" and "->" !

 

[RealBlackStuff]

When you right-click those small scroll arrows in the lower left corner of Excel's display, it will bring up a list of the worksheets in your book.
Select the sheet you want to return to.
No programming needed.

 

[poertner_1274]

I know you can add the web toolbar, and hit the back button on there. That will take you back to where you were before you clicked on that hyperlink. But this is only a solution for you. If you send this to multiple people they will all have to add the web toolbar to their excel setup, it's not on by default.
It's an option anyway.

BTW
:wave:Welcome to TechSpot:wave:

 

[dutinming]

Yes, uUsing Tabs and activating Web toolbar both work. But it requires the person (suppose my boss) who read the file have certain computer knowledge, at least Excel.

I am looking a way to automate the spreadsheet so that the flow of reading the report is controlled in predetermined way, rather than going anywhere by the reader (especially the boss!). Hence, if somthing like the navigation arrows in the Web toolbar can be created and embedded in to spreadsheets will be perfect !

Regards!

 

[RealBlackStuff]

How about giving your boss a crash-course in how to read Excel spreadsheets?

Or make your spreadsheet better, in that you don't need to switch back and forth.

Perhaps pay a visit here? http://www.mrexcel.com/

 

[kirock]

Excel sheets movement

dutinming,


Instead of creating hyperlinks in a cell on each sheet, why don't you use a macro button. Put 1 button called "Next" on the 1st sheet and then 2 buttons on all the rest ("Previous" and "Next) and then on last sheet just put "previous".

The function instead the macro button (goto macro editor), would just be click_Next
Sheets("Equipment list").Show
Click_Previous:
Sheets(Menu").Show

etc. each button would be hard coded for your application. Take Excel security on Boss's PC to minimal and he wouldn't get message about macro needing to run. It will just load itself.

You can get more flexible if you use a variable like:
Dim Sheetname as String, n as integer. Now each is still recognized in Excel as Sheet1, 2, 3 etc.
So you can make it step, like this Sheetname ="Sheet" & n, (where n =n +1, or n=n-1) for Next and Previous.
So example:
In ThisWorkbook put:
Private Sub Workbook_Open()
n=1
Sheet1.show
End Sub
on Sheet1 put the button named "Next" and in Next's click function put this code:
n=n+1 ( for Previos just put n=n-1 here)
Sheetname = "Sheet" & n
Sheetname.show

Cheers,

 

[dutinming]

Kirock,

Your solution is great!
My Excel version is 2002, and found that VBA commands are a bit different, for example:
"Sheetname.show" replied with a bug "Compile error: Invalid qualifier" and the macro stopped.

In addition, clicking "Previous" go back to the n-1'th sheet and "Next" to the n+1'th sheet. However, I want the buttons bring the reader to the sheet that call the current one and not necessarily the previous (or next) sheet in sequence. I build a skeleton of my project in the Excel file attached.

So, starting with the main manu. Suppose the "Clients" sheet is read, which instructs to look at the "Location" sheet via a click. Then how can I build the "Previous" button to return to the "Clients" sheet instead of "Equipment" ("Equipment" is the physical "previous" sheet, but I don't mean it) ? Much appreciation if you can advise.

Sincerely,
dutinming

 

[kirock]

Excel stuff

dutinming,

I couldn't get it pass a global variable to each sheet (so it would know what sheet it came from). So I had to do it using Cells(2,2). You can use Cells(2000, 2000) so it never uses a working area of your sheet.

As you can see it passes the active sheet name to the next sheet it's going to, so the "Previous" command is quite simple. Reads the Cells (2,2) and applies that name to the sheet.activate command.

Cheers,

 

[dutinming]

Kirock,

So kind you are!

However, it still cannot work as I expect:
Starting from sheet1 and follow my step (1): click "Clients" and it activates the sheet "Clients", where I (or my boss) work thru the client list...

Suppose I need to find info from the Location for an client ... Step (2): Click the word "Location" as indicated to activate the Location sheet.

Now we are in the sheet "Location", while in one second earlier we were in the sheet "Clients".

(Suppose) we have finished looking a location information in the sheet "Location", and want to return to the sheet "Clients" to continue our work there, then clicking the "Previous" button shall bring us back to the (previous) sheet "Clients" where we called the sheet "Location" .... However, your "Previous" button brought us to the Menu !