hi.
i create many new worksheets every day and want to provide links between them.
i want to use a macro for that.
Question:
via a macro, how does one define/insert a hyperlink (at a particular cell location) from one sheet to another?
for example:
if ‘sheet1’ is currently visible,
i want to provide a hyperlink on sheet1 at cell ‘a12’ (say the text in the cell is ‘sheet2’)'that when clicked sets the active sheet to sheet2.
how would i do that via a macro?
thanks
Judging by your description, you want to point the macro to Sheet2.A12 in some way? How are you going to indicate this? In dialogue? How do you want to run a macro? With a hotkey? In this case, your task has already been solved - press Ctrl+K and select Document, Target (Sheet2), add .A12 and you will receive a hyperlink like #Sheet2.A12.
what i want to do is ‘create/insert a hyperlink in sheet1 at cell a12’ that when clicked sets the active sheet to sheet2. i want to create/inset that hyperlink on sheet1 at cell a12 via a macro, not manually. i need to do this for many sheets, so i would like to use a macro. i revised the text of the question for clarity.
Ah, it seems I’m starting to understand your plan. Do you need to create a table of contents sheet? Collect links to other existing sheets of the spreadsheet on Sheet1?
i want to do two separate things: (1) i would like to create a table to contents sheet AND (2) provide quick links from one sheet to another based on their contents (ex: allow 'drilling down to details.from a high level sheet to a details sheet). i am able to insert/create such links manually via the ‘insert hyperlink dialog’ , but i will have so many links and there will changes over time so using a macro will make it easier.
Honestly, I don’t understand how a macro would simplify the task.
I’ll give a code of a simple macro that recreates the table of contents. Perhaps with its help you can realize your vision of solving this problem.
Create (or Recreate) TOC for spreadsheet:
Sub createTOC()
Const TOC_NAME = "TOC"
Dim oSheets As Variant
Dim oNames As Variant, oRes As Variant
Dim oSheet As Variant
Dim i As Long, j As Long
oSheets = ThisComponent.getSheets()
Rem Delete old TOC if it exists
If oSheets.hasByName(TOC_NAME) Then oSheets.removeByName(TOC_NAME)
oNames = oSheets.getElementNames()
ReDim oRes(UBound(oNames))
For i = LBound(oNames) To UBound(oNames)
oRes(i) = Array("=HYPERLINK(""#" +oNames(i) + """;""Jump to " + oNames(i) + """)")
Next i
Rem Create new (empty) TOC
oSheets.insertNewByName(TOC_NAME,0)
oSheet = oSheets.getByIndex(0)
Rem Paste hyperlinks from cell A12
oSheet.getCellRangeByPosition(0, 11, 0, 11+UBound(oNames)).setFormulaArray(oRes)
End Sub
Update. @calcboy, it seems to me that I have found an acceptable solution for the second problem from your question, “provide quick links from one sheet to another based on their contents”. It doesn’t require a macro.
Let’s say you want to place a hyperlink to the original formula range to the right of a cell with a formula like =AVERAGE($Sheet6.E15:E19).
Please do the following. Select cell B2 on any sheet, press Ctrl+F3 to open the Manage Names and add new name (for example JumpTo).
To the field “Range or formula expression” paste this formula:
HYPERLINK("#"®EX(FORMULA(A1);"'?[a-zA-Z0-9\s\[\]\. ]{1,99}'?\.\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?");"(See "®EX(FORMULA(A1);"'?[a-zA-Z0-9\s\[\]\. ]{1,99}'?\.\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?")&")")
Now, to the right of any cell with a formula that refers to another sheet, you can enter =JumpTo and get (most often) a hyperlink to the original range
that took care of my table of contents challenge.
many thanks
Assume you know how to get a cell object, and myLinkingCell is its name for the example.
Assume also you know the name of the target sheet, say theOtherSheet.
myLinkingCell.Formula = "=HYPERLINK(""#theOtherSheet"")" should then create the link you want.
This way you get a link to the other sheet, making it active and giving the focus to the cell which had it when that shaat last was active.
There are alternatives, e.g:
Assign the link directly to the myLinkingCell.Hyperlink (Seems inaccessible from the UI.)
Create a text for the cell containing a TextField of URL type.
Insert a graphic object (anchored to the cell) and assign the target to it.
…
The answer was created before I had read the few comments on the question posted while I wrote it. .
both solutions (provided by JohnSUN and Lupp) helped me address my challenges.
JohSUN with TOC,
and Lupp with individual hyperlink.
but it looks like i can only select one solution as being correct (which would not be accurate), so i am writing this ‘solution’ which refers to both ‘true’ solutions.
You can upvote (upward angle) the more complete solution by @JohnSUN.
BTW: The tool ‘Add Answer’ should only be used for an actual anwer to the question on top. For different purposes you have the “add a comment” here or there.
thanks.
what would the approach/syntax be if
instead of explicitly specifying the actual sheet name (in this case ‘theotherSheet’)
a variable was specified that was associated with the sheet name.
i can get it to work if i specify the sheet name as in your example, but i can’t get it working with a variable.
Quoting @calcboy: “…but i can’t get it working with a variable.”
How did you try (lines of code)?
Does it read like
myLinkingCell.Formula = "=HYPERLINK(""#" & mySheetNameVariable & """)" ?
(Regard that you need to use "" in place of an ordinary doublequote if located inside a string constant.)
If so, how did you assign the needed value to that variable?
Thoroughly study the solution by @JohnSUN. He offered much more than I did. (He used, however the usual “+” as the operator for string concatenation which I heavily dislike for its ambiguity, and replaced therefore by the also working “&”. The need-to-double-quotes-in-stringconstants is the same, of course.
that works. my problem was the number of "s. many thanks