Create a dynamic hyperlink in Excel

[zolaforever]

My workbook has ten worksheets plus a 'Notes' worksheet.
I hyperlink to the 'Notes' sheet from any of the other ten sheets to enter a note.
Where I'm stuck is - creating a single hyperlink on the 'Notes' sheet that takes me back to whatever sheet I just came from? (i.e. it'd be like the 'last page viewed' link available in Powerpoint).
Any suggestions (clean!) would be appreciated...

 

[mthakershi]

I guess you will have to write a little VBA code and maintain which sheet is active in some variable. And when you go to the 'Notes' sheet, you will know where you came from.

Malay

 

[zolaforever]

Thanks for your reply.
It sounds simple - but you used that 'VBA' word!
I've not used VBA before - is this a good place to forget the whole idea?

 

[GlennUK]

If a keyboard solution is OK, how about doing F5-Enter

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Fenrirshowl]

Glenn - Doesn't F5 trigger Goto (which will need a reference)?
Fen

 

[mthakershi]

VBA means Visual Basic For Applications.

Write the following code under your workbook section. I have three sheets and a notes sheet as you said. When I go to 'Notes' sheet, it detects where I came from. Hope this helps.

Private mStrCurSheet As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Check whether you are going to go on 'Notes' sheet
If IsNull(mStrCurSheet) <> True _
And mStrCurSheet <> "" _
And UCase(Trim(Sh.Name)) = "NOTES" Then

MsgBox "Going to " & Sh.Name & " sheet from " & mStrCurSheet & " sheet.", vbOKOnly
End If

mStrCurSheet = Sh.Name
End Sub

Malay

 

[GlennUK]

Fenrirshowl,

yes F5 triggers Goto, and the default is to go to where you just came from ( after any automated navigation ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Fenrirshowl]

Glenn

That's interesting and I am trying it purely as an exercise but it isn't working for me!

I run the following (as the automation part)

Sub move()
Sheets("Sheet2").activate
Cells(1, 1).select
End Sub

Then I hit F5 followed by Enter and I get a reference is not valid error message - have I missed something?

My thought was whether zolaforever could utilise your post using the sendkeys method e.g.

Sub sndkys()
Application.SendKeys ("{F5}")
Application.SendKeys ("{return}")
End Sub

but as I am falling over on the keyboard route I'm not getting very far.

 

[1726]

Consider an alternative solution:

Open two windows for the workbook, one displaying the Notes sheet the other displaying the 'current sheet' then you can switch windows to jump between the current sheet and the notes sheet.

You could even display them side by side.

 

[GlennUK]

Hi Fenrirshowl,

when I say automated navigation I mean like Hyperlink, or follow-link, or another Goto, in native Excel, then use F5-Enter.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Fenrirshowl]

Glenn - thanks for confirming (thought it would have been human error getting in the way!).

Fen

 

[dallen43]

Can't you just create a hyperlink back to the original worksheet on the specific note?

To do this (using Excel 2003):

1. Select the cell where you want to insert the hyperlink. In your example the notes worksheet, on a specific note.
2. Click insert and select hyperlink. The Insert Hyperlink window appears.
3. Click on "Place in this document", then click on the sheet and enter the cell reference, such as A1.
4. Click OK.

You will now have a hyperlink on the note that will take the user to the selected sheet/cell when they click on it.

This wouldn't require VBA nor the user to hold F5 while hitting Enter.

Deb

 

[zolaforever]

Hi to everyone who contributed and many thanks for your help.
My first post - and a successful result. This could become habit-forming...

 

[GlennUK]

Hi dallen43/Deb,

just a small point, you said ...

This wouldn't require VBA nor the user to hold F5 while hitting Enter.

... you don't hold F5 while hitting Enter, you press F5 then press Enter.

I use this all the time, because I check formulae all the time and am always doing link-navigation ( with Tools/Options/Edit/Enter In Cell switched off, by the way ) by double-clicking on a cell, which takes me to the first precedent cell ( be it another cell within the sheet, or on another sheet, or in a completely different workbook ). Pressing F5 then Enter takes me straight back to where I came from.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.