All Macros Are Enabled, But Macros Are Still Not Running

I have Excel 2013. I am having a problem in which, in settings, I instruct Excel to allow all macros to run, but then when I open the sheet and try to run the macro, I get the message that macros have been disabled. See screenshots below.

However, it is not consistent. When I first open Excel, then open the sheet and try to run the macro, it will usually run successfully. I may be able to open the sheet once or twice, or four or five times, or maybe eight or ten times, and it runs successfully. But then, at a certain point, right around this point, the macros stop working. And I have to close Excel and open it up again.

BUT: In addition to closing Excel normally, I have to open Windows Task Manager, go to the Processes tab, and close EXCEL.EXE, which is still resident after I terminate the program. Once I do this, I can then run the macro successfully, a few times, until I can't anymore, when I have to repeat this process all over again.

So it appears that Excel opens in a certain state that allows me to run macros and will close itself normally, but then at some point it switches to another state that disables my ability to run macros, and stays resident in Task Manager after I terminate it.

I tried two things: (1) unistalling and reinstalling Office; and (2) turning off my Norton anti-virus/firewall temporarily to see whether it was Norton borking my macors. Neither of these tactics solved the problem.

Does anyone recognize this problem and know of a fix?

Can anybody help?

Originally Posted by xladept

So - what happened???

I'm sorry, I thought you were just making an off-handed comment. I didn't realize this was a serious attempt to help. No, no difference.

Originally Posted by abousetta

Something in your code is hanging in memory and that's why you can't run it because it thinks that the workbook is already open. I would use Code Cleaner or MVP Orlando's program to Decompile then Recompile your workbook. This could be a hidden error before proceeding any further with troubleshooting.

Is it possible that the workbook is not the problem, but something with the Excel program itself? I ask because when the macro is working and I close Excel, it completely closes the process (which can be seen in Task Manager), and I can reopen Excel and successfully run the macro again.

But at that point when the macro fails and I close Excel, I look in Task Manager and EXCEL.EXE is still resident. When I reopen Excel immediately to run the macro, it fails. But when I go back to Task Manager and end the EXCEL.EXE process manually, then reopen Excel, the macro works again. Until it fails, and I have to repeat this process through Task Manager.

This is why I am wondering whether this is a program problem instead of a worksheet problem. What do you think?

Here is something else I just now took notice of:

In my Recent list, I see the workbook PERSONAL.XLSB, from the folder C:\Users\[Name]\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB. I did not manually open that file, so why would it be listed in my Recents? This isn't normal, is it?

If not, I'm starting to think there must be something happening that triggers Excel to open this file, which might be borking my macro. Does anybody recognize this problem?

Originally Posted by romperstomper

That file won't cause the symptoms you describe.

Does this happen with all macros, or just one particular workbook?

I was just answering your question when I noticed the PERSONAL.XLSB issue I just described.

The answer is, I have a little macro (converting default decimal values into baseball batting average-type values) that works universally across all open workbooks; the macro I have been describing works only in the one workbook. When the workbook macro fails, the universal macro still works.

Originally Posted by romperstomper

BTW, it is perfectly normal that Personal.xlsb opens whenever you open Excel - that is its purpose.

I am intrigued (and surprised) that the activex problem is also causing the symptoms you described. Useful information!

Does PERSONAL.XLSB show up in your Recents?

Originally Posted by mthatch19

This solved my problem on 4 different computers, some running Office 2007 and some 2010! Thank you Jerry.


Sorry I'm new here...meant to quote jbeaucaire in my post.

Did you have the Excel8.0 folder in your Temp file as be indicated?

Originally Posted by romperstomper

If you mean the File - Recent workbooks list, then no. Is it opening visible or hidden?

Note: based on what you describe I think the issue may lie with your specific code - can you post it?

I'm believe it is opening hidden. I opened a bunch of .csv files and they were all among my four recents, but when I went to open the sheet with the macro (2015 Player Card), PERSONAL.XLSB shows up with it:

ExcelRecents.png

But when I go to View/Window/Arrange All and view all the open windows, only 2015 Player Card appears. PERSONAL.XLSB is not windowed with it.

Here is the entire code for the macro I am having trouble with:

Sub RecordPlayerInput()
'
' RecordPlayerInput Macro
' Rewritten by [Name] 12/2/14 because Excel 2013 cannot process macros from Excel 2003.
'
' Keyboard Shortcut: Ctrl+q
'
Range("C7").Select
ActiveWindow.LargeScroll ToRight:=2
Range("C7:BG7").Select
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
End Sub

One thing to note here: I did not previously mention that the sheet is a Compatibility Mode sheet originally created in Excel 2003. I could not update the sheet to an XLSX because the original macro did not work in 2013. So I dumped that macro and re-stepped into a new macro that does exactly the same thing.

Originally Posted by romperstomper

I can't see anything there that would have been incompatible between versions, other than the TintAndShade property didn't exist in 2003. Nor can I see anything that could explain the behaviour you are describing.

If you open Excel in Safe Mode (hold down the Ctrl key while starting Excel normally) do you still have the erratic macro disabled behaviour?

Yes, I have the exact same problem when opening in Safe Mode.

I don't know whether this is related, but frequently when I turn on my computer in the morning and open Office apps, including Excel, they open in various states of minimization, and which are strewn seemingly randomly across my desktop. And occasionally, when I close out an Office app and reopen it in the same session (i.e., same day), it opens minimized then, as well.

I don't understand why this should happen, because I always have all Office apps maximized to the window, so I do not know why they would not retain that state from instance to instance, the way Office 2003 did.

Things like this really make me miss Office 2003. God, I miss Office 2003.

Originally Posted by JBeaucaire

I had a Windows Update install itself on Wednesday and I lost the ability to run a lot of code. Turns out an unneeded file had been added to my system, so I removed it and things went back to normal.

C:\Users\[user.name]\AppData\Local\Temp\Excel8.0\MSForms.exd

I have this in my computer and macro seems normal, do I need to delete it?