Jul 30 2019 11:31 AM
MacOS High Sierra 10.13.6
MS Office Excel for Mac 365 16.27
VBA 7.1
I would like to assign a {F11} to run a sub for all worksheets. I can't even get it to work for the current worksheet. Should I be able to do this?
Option Explicit
Dim WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
App.OnKey "{F11}", "HyperAdd1"
End Sub
Sub HyperAdd1()
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
For Each rng In WorkRng
Application.ActiveSheet.Hyperlinks.Add rng, rng.Value
Next
End Sub
I've tried other events, auto_open, Private Sub App_SheetActivate(ByVal Sh As Object), Private Sub workbook_open()
Thanks for any help.
Milton
Jul 31 2019 01:57 AM
@Milto1850 I'm not sure why you are writing this code in a class module, as the hotkey assignment only needs to be done once in your Excel session, when the workbook containing this code opens. After that, pressing the hotkey will call the routine Hyperadd1 which already works on the active sheet. Also, you need to reset the default behavior of F11 when the workbook is closed. So remove your code and add this to the ThisWorkbook module of the workbook:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{F11}"
End Sub
Private Sub Workbook_Open()
Application.OnKey "{F11}", "Thisworkbook.HyperAdd1"
End Sub
Sub HyperAdd1()
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
For Each rng In WorkRng
Application.ActiveSheet.Hyperlinks.Add rng, rng.Value
Next
End Sub