• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Runtime Error 91 "Object Variable or with Block Variable not Set"|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Runtime Error 91 "Object Variable or with Block Variable not Set"|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosRuntime Error 91 "Object Variable o…
sp_PrintTopic sp_TopicIcon
Runtime Error 91 "Object Variable or with Block Variable not Set"
Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
1
March 9, 2020 - 9:06 am
sp_Permalink sp_Print

Hi,

 

I have made Database VBA with visible sheets. After converting to down from excel 2019 to 365 office it

comes up with Runtime error 91 "Object Varible or with Block Varible not Set".

 

Yet when I use it in 2019 nothing happens?????

 

Sub VisibleTrue()

DIM ws As worksheet

 

For Each ws In Activeworkbook.Sheets

ws.Visible = True

Next ws 

ActiveWindow.DisplayWorkbookTabs = True

Sheet9.Select

End Sub

 

I Also tried.

 

DIM As Worksheet

Set ws ActiveWorkbook

 

ECT.....

 

Have I missed something here or is it because of going from 2019 too 365

 

Thanks

 

Steve

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
March 9, 2020 - 12:11 pm
sp_Permalink sp_Print

Steve,

Without your workbook I can't do anything.

Phil

Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
3
March 9, 2020 - 12:35 pm
sp_Permalink sp_Print

Sorry Phil,

 

Here is the workbook.

 

Sorry once again

 

As stated in my reply email

 

Steve

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
March 9, 2020 - 5:39 pm
sp_Permalink sp_Print sp_EditHistory

Hi Steve,

I can't reproduce the error.  When I run VisibleTrue it runs without issue.

Do you get this error when you open the workbook?

What line of code is highlighted when the error occurs?

Have you tried debugging by stepping though the code line by line using F8?

regards

Phil

Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
5
March 9, 2020 - 6:29 pm
sp_Permalink sp_Print

Hi Phil,

 

Thats interesting. The error came up as soon as I transferred the database from my excel 2019 to 365 (work computer).

The error comes as soon as I hit enable editing or macro. The error indicates on VBA debug highlighted in yellow:

For Each ws In Activeworkbook.Sheets

I could bench test this tomorrow at another collueges computer and see if it debugs on that computer. All work computers run 365 - sad.

I had previously completed 3 databases and never had an issue with that visibletrue scenario coding.

Can you confirm if its a 365 or my 365 work computer or excel difference between 2019 and 365????

 

Thanks

 

Steve

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
March 10, 2020 - 11:47 am
sp_Permalink sp_Print

Hi Steve,

I can't see why your code would run any differently on 2019 or 365.

Phil

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
7
March 11, 2020 - 1:19 am
sp_Permalink sp_Print

Does it work properly if you enable macros, then save close and reopen the workbook? I suspect protected mode is interfering.

Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
8
March 11, 2020 - 5:04 pm
sp_Permalink sp_Print

Terrible sorry phil for not getting to you earlier...been swamped at work.

 

I've tried it on 2 work coleugue 365...and it works with no errors.

 

So im a bit perplexed on this maybe some of my 365 isn't working????

Anyway I leave at that and move on

 

Thanks for your help

 

Steve

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
9
March 11, 2020 - 6:46 pm
sp_Permalink sp_Print

Could you be running different versions of Excel i.e 32 bit or 64 bit? I know there are some differences between the 2 versions

Purfleet 

Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
10
March 13, 2020 - 7:56 am
sp_Permalink sp_Print

Hi Purfleet,

 

I have looked on my Work 365 Excel and its running 32Bit.

 

My Personal excel 2019 64 bit.

 

So looks like a type of version that might have a some issues with

 

Steve

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
11
March 14, 2020 - 4:19 am
sp_Permalink sp_Print

Nothing in your code is 32 vs 64 bit specific.

 

On the computers where it works, are you prompted to enable editing?

sp_AnswersTopicAnswer
Answers Post
Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
12
March 14, 2020 - 5:11 pm
sp_Permalink sp_Print sp_EditHistory

Yes of coarse Velouria>

The only way to work and function is to enable it and macro.

I have not asked or seen if my work colegues have 32bit 365 as yet.

Steve

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
13
March 16, 2020 - 11:16 pm
sp_Permalink sp_Print

Steve,

My point is that if your workbook opens in protected mode and you then enable editing and macros, any code run in the Open event will not be able to access any application properties (such as Activeworkbook). If you save, close and then reopen it, so it should be trusted at that point, does the code then run without error (assuming you don't get any prompts)?

Avatar
The Nerd

New Member
Members
Level 0
Forum Posts: 1
Member Since:
July 18, 2020
sp_UserOfflineSmall Offline
14
July 18, 2020 - 1:47 am
sp_Permalink sp_Print

Excel will throw error 91 when calling Activeworkbook.Sheets while having multiple files with the same name open at the same time. This can cause random errors 91 in code that worked fine previously.

I don't know whether this was the problem in your case or not.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
15
July 20, 2020 - 9:16 pm
sp_Permalink sp_Print

But you can't have multiple files with the same name open in the same Excel instance.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Aislinn Mellamphy, Lynnette Altomari, Roy Lutke, Dieneba NDIAYE, Darrell Hodge, Tucker Oakley
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.