Log In
Register
Home
Power Query
January 30, 2020
Offline
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
See Answer
October 5, 2010
Offline
Steve,
Without your workbook I can't do anything.
Phil
Power Query
January 30, 2020
Offline
Sorry Phil,
Here is the workbook.
Sorry once again
As stated in my reply email
Steve
October 5, 2010
Offline
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
Power Query
January 30, 2020
Offline
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
October 5, 2010
Offline
Hi Steve,
I can't see why your code would run any differently on 2019 or 365.
Phil
Trusted Members
Moderators
November 1, 2018
Offline
Does it work properly if you enable macros, then save close and reopen the workbook? I suspect protected mode is interfering.
Power Query
January 30, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
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
Power Query
January 30, 2020
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
Nothing in your code is 32 vs 64 bit specific.
On the computers where it works, are you prompted to enable editing?
Answers Post
Power Query
January 30, 2020
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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)?
New Member
July 18, 2020
Offline
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.
1 Guest(s)
