EXCEL FOR MAC HYPERLINK CODE
Start by double clicking on a sheet in the Project Explorer window to open its code window. This code will be placed in the Deactivate event of each worksheet. I want some code to run, no matter the method of leaving. You could be leaving the sheet by following a hyperlink on that sheet, or maybe just by clicking a sheet tab at the bottom. However, you might want the sheet to automatically hide itself again when you have finished with it. Now that solves our problem and the purpose of this tutorial. The Left function was used for this, and the Instr function located the position of the exclamation mark. This is because exclamation marks separate the sheet name and cell address or range in Excel i.e. This was done by extracting all the characters up to the exclamation mark in the sub address. A sub address is when you link to a location within the current file. In this example we extracted the sheet name from the sub address of the clicked hyperlink. ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1) Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
/InsertHyperlinkRibbon-5bdf643fc9e77c0051804e13.jpg "excel for mac hyperlink")
This second example of code does not rely on the link text and sheet names matching and is more durable. This is then used to make the sheet visible and then select it. If the hyperlink text and worksheet name are different, then the second example is better for you. In this example the hyperlink text and the name of the worksheets is exactly the same, so this is a good idea. A variable named Target is provided to us for this. It then assigns the name of the clicked hyperlink (the text displayed by the hyperlink) to that variable. This code declares a string variable named ShtName. We will need to use code to identify what link was clicked, unhide the required sheet, and take the user to it.įor the first example, the code below will do the trick. It is just an event that is triggered by someone clicking a hyperlink on that sheet. It is called FollowHyperlink but it does not actually follow the link. What is quite misleading is the name of this event. We can now enter some code inside the Worksheet_FollowHyperlink sub provided. To access the Worksheet_FollowHyperlink event, select Worksheet from the Object list, and then FollowHyperlink from the Procedures list. The Title will display the currently active code window. We need to open the code window for the “Main” worksheet because that is where the event resides.ĭo this by double clicking on the sheet in the Project Explorer window. We need to use this code on the Worksheet_FollowHyperlink event so that it occurs when the link is clicked. Lets begin by opening the Visual Basic Editor by clicking Visual Basic on the Developer tab, or by pressing Alt + F11. Excel VBA to Follow Hyperlink to Hidden Worksheet
0 kommentar(er)
