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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.