PDA

View Full Version : Disable macros on opening via Hyperlink



mdmackillop
07-07-2008, 10:58 AM
I'm using a hyperlink to open a workbook, then using a variable to go to the correct sheet, but I don't want any macros to run on opening by this method (there is a WorkBook_Open macro).
This doesn't seem to work. Any other ideas?

Private Sub Workbook_SheetFollowHyperlink(ByVal sh As Object, ByVal Target As Hyperlink)
Application.EnableEvents = False
ActiveWorkbook.Sheets(sht).Activate
Application.EnableEvents = True
End Sub

Simon Lloyd
07-07-2008, 11:07 AM
Malcolm you can try this


Deleted code just in case security risk!

Code PM'd to Malcolm

mdmackillop
07-07-2008, 12:05 PM
Thanks Simon,
I'll check that out later
Regards
Malcolm

Simon Lloyd
07-07-2008, 12:11 PM
It certainly works, it's just i didn't have the time to work in a hyperlink instead of the selection from dialog box!

mdmackillop
07-07-2008, 02:58 PM
Hi Simon,
Same problem as before. I can't find an event which fires before the target is opened and the WorkBook_Open macro has run, so how to trigger your code?

Simon Lloyd
07-07-2008, 03:32 PM
A couple of things to try (may not be acceptable to you!) look here (http://www.ozgrid.com/forum/showpost.php?p=395414&postcount=9) for some code and explanation on how to prevent the hyperlink firing and you can also use the msoMethodGet ExtraInfo which is a String that's appended to the address.
found here (http://msdn.microsoft.com/en-us/library/aa195735(office.11).aspx) so you could use the string as the workbook name to open!

mdmackillop
07-08-2008, 12:05 AM
Thanks for that Simon.
I just changed the hyperlink target to the cell containing the link and added the file address as the TextToDisplay, then add the following code.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Application.EnableEvents = False
Workbooks.Open Target.TextToDisplay
Application.EnableEvents = True
End Sub