Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-779732%22%20slang%3D%22en-US%22%3EMac%20OS%20Excel%20vba%20error%20Run-time%20error%2091%20.%20Object%20variable%20or%20With%20block%20variable%20not%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779732%22%20slang%3D%22en-US%22%3E%3CP%3EMacOS%20High%20Sierra%2010.13.6%3C%2FP%3E%3CP%3EMS%20Office%20Excel%20for%20Mac%20365%2016.27%3C%2FP%3E%3CP%3EVBA%207.1%3C%2FP%3E%3CP%3EI%20would%20like%20to%20assign%20a%20%7BF11%7D%20to%20run%20a%20sub%20for%20all%20worksheets.%26nbsp%3B%20I%20can't%20even%20get%20it%20to%20work%20for%20the%20current%20worksheet.%26nbsp%3B%20Should%20I%20be%20able%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3EOption%20Explicit%0ADim%20WithEvents%20App%20As%20Application%0APrivate%20Sub%20App_WorkbookOpen(ByVal%20wb%20As%20Workbook)%0AApp.OnKey%20%22%7BF11%7D%22%2C%20%22HyperAdd1%22%20%20%0AEnd%20Sub%0ASub%20HyperAdd1()%0A%0ADim%20rng%20As%20Range%0ADim%20WorkRng%20As%20Range%0AOn%20Error%20Resume%20Next%0ASet%20WorkRng%20%3D%20Application.Selection%0AFor%20Each%20rng%20In%20WorkRng%0AApplication.ActiveSheet.Hyperlinks.Add%20rng%2C%20rng.Value%0ANext%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20I've%20tried%20other%20events%2C%20auto_open%2C%20Private%20Sub%20App_SheetActivate(ByVal%20Sh%20As%20Object)%2C%20Private%20Sub%20workbook_open()%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3CP%3EMilton%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-779732%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Evba%20excel%20application.onkey%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781017%22%20slang%3D%22en-US%22%3ERe%3A%20Mac%20OS%20Excel%20vba%20error%20Run-time%20error%2091%20.%20Object%20variable%20or%20With%20block%20variable%20not%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384474%22%20target%3D%22_blank%22%3E%40Milto1850%3C%2FA%3E%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EI'm%20not%20sure%20why%20you%20are%20writing%20this%20code%20in%20a%20class%20module%2C%20as%20the%20hotkey%20assignment%20only%20needs%20to%20be%20done%20once%20in%20your%20Excel%20session%2C%20when%20the%20workbook%20containing%20this%20code%20opens.%20After%20that%2C%20pressing%20the%20hotkey%20will%20call%20the%20routine%20Hyperadd1%20which%20already%20works%20on%20the%20active%20sheet.%20Also%2C%20you%20need%20to%20reset%20the%20default%20behavior%20of%20F11%20when%20the%20workbook%20is%20closed.%20So%20remove%20your%20code%20and%20add%20this%20to%20the%20ThisWorkbook%20module%20of%20the%20workbook%3A%3C%2FFONT%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EOption%20Explicit%0A%0APrivate%20Sub%20Workbook_BeforeClose(Cancel%20As%20Boolean)%0A%20%20%20%20Application.OnKey%20%22%7BF11%7D%22%0AEnd%20Sub%0A%0APrivate%20Sub%20Workbook_Open()%0A%20%20%20%20Application.OnKey%20%22%7BF11%7D%22%2C%20%22Thisworkbook.HyperAdd1%22%0AEnd%20Sub%0A%0ASub%20HyperAdd1()%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Dim%20WorkRng%20As%20Range%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20Set%20WorkRng%20%3D%20Application.Selection%0A%20%20%20%20For%20Each%20rng%20In%20WorkRng%0A%20%20%20%20%20%20%20%20Application.ActiveSheet.Hyperlinks.Add%20rng%2C%20rng.Value%0A%20%20%20%20Next%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Milto1850
Occasional Visitor

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

1 Reply

@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
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies