After Hyperlink, how to return return where I came from

U

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
I have a main sheet (number 1) and many supporting sheets (2-N) in a workbook

On the each supporting sheet I have hyperlinks to the main sheet. how can I return to where I came from.
If I had 15 supporting sheets, I would not want 15 selections on the main sheet.

Would it be possible to, as I click the link on the supporting sheet to create one "on the fly" on the main sheet. Then when I click that one (on the main sheet to return), delete it, so it will not sit there forever

Here is my hyperlink code

Range("B6").Select ' place to put hyperlink on supporting sheet
referTo = "Main!" & "B1" ' target location on main sheet
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:= referTo, ScreenTip:="Help Text", TextToDisplay:="To Main"

Seems like a tricky thing to accomplish........

By the way, discovered that spaces in the sheet name are a problem
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Put this code in the module for ThisWorkbook:

Code: 
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    Dim RefTo As String
    If Sh.Name <> "Main" Then
        RefTo = "'" & Sh.Name & "'!" & Target.Range.Address(False, False)
        With Worksheets("Main")
            .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:=RefTo
        End With
    Else
        With Sh.Range("A1")
            .Hyperlinks.Delete
            .ClearContents
        End With
    End If
End Sub

If a worksheet name has spaces it must be surrounded with single quote, eg:

SubAddress:="'Sheet 1'!A1"
 
Upvote 0
Thanks, will try that

I somehow knew there was a way to have spaces in the name. Other lanuages (UNIX scripts) have similar solutions.

Thanks again
 
Upvote 0
I added it and realize it is an "Event" driven sub. Did not see the link

I added text in the target field, no dice

Then I added to the event sub

TextToDisplay:="Return", ScreenTip:="MouseOver Text"

so I would have something to click on, but to no avail.

Appreciate your help, Thanks
 
Upvote 0
My mistake, I did not add the sub in the ThisWorkBook

One option, I actually go to several diffrent locations in the Main worksheet. I shtere a way to generalize that, rather if-then-else's

Thanks
 
Upvote 0
UHsoccer said:
My mistake, I did not add the sub in the ThisWorkBook

One option, I actually go to several diffrent locations in the Main worksheet. I shtere a way to generalize that, rather if-then-else's

Thanks
Click to expand...

You can use ActiveCell instead of .Range("A1") as the Anchor, if that's what you mean. Then to delete the Hyperlink when leaving sheet Main, use Target.Range instead of Sh.Range("A1").
 
Upvote 0
Thanks Andrew,

I can make it work when the sheet name has no blank space in the name. To account for that I tried

sheetName = "Roll Up"
With Worksheets("'" & sheetName & "'")

An error "Subscript out of range" stops execution

Thanks, in advance
 
Upvote 0
Sorry, I don't really understand.

It's only the SubAddress that needs the single quotes. You don't need them in the With statement.
 
Upvote 0
I struggeled with it yesterday and today it works.

You are very correct, I need only double quotes in the "with" statement.

I was trying to "generalize" the name by making it a variable. That way, it would not matter what the first sheet (Main) was really called. I am giving up on that idea for the time being.

Thanks again for your help
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA - Create a new sheet + hyperlink name
Replies
3
Views
428
excelnewbie990
E
M
  • Question Question
Formula Help - Tab naming without loosing hyperlink/s
Replies
1
Views
400
TheLastLinuxUser
T
J
  • Question Question
Using VBA to insert Hyperlinks
Replies
1
Views
712
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
332
stuartgb100
S
I
  • Question Question
Can i apply a hyperlink without opening the other worksheet
Replies
1
Views
245
james_martin001
J

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top