Forum Discussion

Milto1850's avatar
Milto1850
Copper Contributor
Jul 30, 2019

Mac OS Excel vba error Run-time error 91 . Object variable or With block variable not set

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

vba excel application.onkey

1 Reply

Sort By
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jul 31, 2019

    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

Resources