Jan 16 2023 12:29 AM
Jan 16 2023 12:29 AM
I have an excel spreadsheet with lots of macro buttons.
I have recently updated excel (Jan 2023) and now the most important button is not working properly.
I have spent an entire day trying to fix it and am hoping that my findings/partial solution below can give someone a clue of what is the issue.
1) When I load the file it does not work.
2) I then open the code behind the macro button and change 1 thing. (Userform_Initialize to Userform_Activate)
3) The everything works fine, so I save the file
4) When I re-open the newly saved file, I try and run the macro and the macro has the same problem.
I am scratching my head over this. It seems like the only way to make the macro work is to edit the code of the original file everytime I open it
I hope someone can help
Jan 16 2023 01:36 AM
I'm not much of a VBA specialist
...but when displaying a UserForm, the event "Initialize" and then "Activate" is executed if it wasn't previously loaded. This is the case the first time the user form is called (even after a STOP), or if the user form was previously closed with a cross or Unload Me.
If the user form was only hidden using Me.Hide, only the "Activate" event is executed the next time the user form is called - but not the "Initialize" event.
There is also a command - with which you can make a userform run a code over and over again (interval-technical) - as long as the userform is open.
An example in the included file. The OnTime method is used there to update data in the user form at regular intervals. The procedures required for OnTime are usually stored in a general module, which is usually the best experience. These procedures can then be started from anywhere without any problems.
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
Jan 17 2023 06:56 AM
I'm having the same problem after the January update with a workbook that I've used for well over 3 years without issue.
And same as you, I found that editing the code would temporarily fix the issue until the workbook was closed and opened again. Oddly, I have several workbooks with macros (one being very similar to my problem workbook) and those all work fine.... it's only the one that has trouble.
I don't have a fix but have found 2 workarounds so far that at least allow us to keep using the workbook....
I found that if I rename the file, Excel no longer recognizes it as a Trusted Document. If I click enable macros each time I open the workbook but do not make it a trusted document, everything works. As soon as I try making it a trusted document again, the issues return.
The second workaround that I found is rolling back the O365 update to version 15831.20208 (the last version from 2022). Once I did that, everything worked fine just like it had been for the last 3 year.
Jan 17 2023 01:12 PM
Hi Matt, thanks for your reply.
In one way I'm glad that someone else is having this same issue so hopefully Microsoft see this and can fix the issue. But this is still a very annoying issue.
Unfortunately your 2 work arounds haven't worked for me.
The 'trusted document' method looked promising as I read it, but it didn't work. (I can't actually change the file name without the file working, so I made a new file with the same name which still gives me the trusted document prompt)
And our IT department have tried rolling back the update, but the issue is still there. I think if we spent more time trying to roll back the update it would work, but the process looks very complicated and we might not be doing it correctly. Also, moving forward it probably isn't ideal.
One strange thing I noticed is I needed to make a small change to the file and when I saved/overwrited the file, the file size increased from 2.5mb to 3.0mb.
I looked deeper into it and found that if I opened the original 2.5mb file, and saved it without doing anything, the file size still increased to 3.0mb
For the moment my best option is to edit the code when I open the file. Thankfully only one other computer in our office has done the excel update, so I have paused excel updates on all the other machines.
Thanks for your replay Matt. Appreciate it.
If you come up with any other solutions, please let me know.
Jan 24 2023 01:41 AM
We have been affected by the exact same thing, Macro heavy workbooks have stopped working since we applied the patch last week. Most of the time it actually crashes on opening.
What works for us, is to to enable the Macro each time you open the document, and keep all files untrusted:
Go to File > Options > Trust Center > Trust Center Settings > Trusted Documents > Tick Disable Trusted Documents > Also click Clear all trusted documents so they are no longer trusted button
You can now copy the file directly from I drive to desktop, and each time you launch it; it will ask to enable macros. I've been able to open and close the doc multiple times on my desktop with this setting.
Also in Macro Settings > Disable VBA macros with notification
I believe you have to disable trusted locations too, as this will overwrite the above
That means each time you open you have to enable Macros
Hopefully this can fixed soon