Disable macros on opening via Hyperlink

[mdmackillop]

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?
[vba]
Private Sub Workbook_SheetFollowHyperlink(ByVal sh As Object, ByVal Target As Hyperlink)
Application.EnableEvents = False
ActiveWorkbook.Sheets(sht).Activate
Application.EnableEvents = True
End Sub

[/vba]

[Simon Lloyd]

Malcolm you can try this


Deleted code just in case security risk!

Code PM'd to Malcolm

[mdmackillop]

Thanks Simon,
I'll check that out later
Regards
Malcolm

[Simon Lloyd]

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]

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]

A couple of things to try (may not be acceptable to you!) look here 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 so you could use the string as the workbook name to open!

[mdmackillop]

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.
[vba]
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Application.EnableEvents = False
Workbooks.Open Target.TextToDisplay
Application.EnableEvents = True
End Sub

[/vba]