Excel 2007 loses the use of all toolbars 1

[pjw001]

Good Morning,

I have been having this problem occasionally for some time now..

The toolbars and office button stop working. I can still edit the spreadsheet but none of the buttons and icons at the top work. The only way I can save the file is to exit Excel at which point it asks if I want to save the changes.

It saves the file and when I open it again everything works fine.

If anyone has any ideas I would be most grateful.

Thanks.

 

[pjw001]

Should probably have said that I am using Windows 8.1 with all important updates.

 

[combo]

Any macros that put excel in edit mode? Does hitting Esc key help?

combo

 

[pjw001]

Thank you for the reply.

There are no Macros.

The next time it happens (which could be weeks away) I will try the Esc key.

Thanks.

 

[pjw001]

So it has just happened again and hitting the Esc key did not help.

I have updated the spreadsheet several times since 13 December so I guess I will just live with the occasional misfire. (Another First World problem!)

Thank you to combo for your suggestions.

 

[contraretro]

Did you manage to solve the problem, if so, how?

 

[pjw001]

I have not solved the problem.

 

[kjv1611]

I've seen this before, but I forget the particular issue. I'd try these options:

Before you try my listed items below, maybe take a look here and see if this is the whole problem:

https://helpmerick.com/how-to-resto...-in-microsoft-office-word-excel-video-tip.htm

I don't think it is, but just in case.

Also, I think it can sometimes SOUND like fullscreen vs normal screen mode, which I do not think is the issue. But if you want to check that out as well, here's that link:

https://support.office.com/en-us/ar...in-Excel-cbf43625-3fbc-48ca-9ed7-1fc678f3a0cc

Now for a list of items I think MIGHT actually work. The one I'm thinking is the best one to try is removing files from your appdata folder/directory.
[ol][li]Try opening Excel in safe mode: Here are some instructions I found:[/li]
[ul][li]

https://howtech.tv/office/how-to-open-excel-2007-in-safe-mode/

[/li][/ul]
[li]Probably won't work, but try [Aly]+[F] to open the file or formerly called "backstage" view/menu and then [T] for options. If you can get in here, look for default options within the Excel options. One idea, maybe, is Advanced: Disable hardware graphics acceleration in case there's an issue there (Under Display). There are other options to try as well.[/li]
[li]Again if you can get under File (Or in 2007 it was just the round button at top left) -> Options -> Customize Ribbon - see if everything is somehow unchecked (this is just a wild guess trying to think of possibilities.[/li]
[li]With Excel closed, try moving ALL files/folders under C:\Users\<YourUsername>\AppData\Roaming\Microsoft\Excel to some place like a new folder on your desktop. Then try to open Excel again. MAYBE look at files in the same path, but "Local" instead of "Roaming", but I think you'll find no help there. And there should not be such a folder under LocalLow, but if there is might as well give it a shot. If removing the files/folders works, the only thing you most likely will need to put back is a PERSONAL.xls or PERSONAL.xlsb file if you have either of them in the XLSTART folder. Really anything in that folder in case you have some other file(s) there. I'm guessing you have none, since you say no macros.[/li][/ol]

Those are just some thoughts. I wish I could remember the definite exact fix. It's probably been at least 7 or 8 years since I used Excel 2007. That was first year of the ribbon and the ribbon functionality was greatly improved beginning in Excel 2010.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[pjw001]

Hi kjv1611,

Thank you for the extensive reply and apologies for the delayed response, but I wanted to have time to check out your suggestions before replying.

I agree with you that the first two are probably not the problem. It is not that the ribbons are missing. They are there on the screen, but totally non-responsive. But I would not describe myself as an Excel expert so did learn some useful stuff checking them out.

Moving on to your other suggestions.

1. Excel in safe mode. I have set up a short cut to do this. Since the problem only happens rarely it would mean I would need to always open in safe mode. Having said that, the problem only seems to occur when I am editing multiple files (at the same time) related to my pension. (Perhaps Excel is confused by large numbers - I wish!)

2. [Alt]+[F]. This is potentially very useful as even if it doesn't help me solve the problem it should give me access to the functions I want.

3. The same as in 2.

4. The only file in C:\Users\<YourUsername>\AppData\Roaming\Microsoft\Excel is Excel12.xlb. The folder does not exist in either Local or LocalLow.

Thank you again for your suggestions. If I do find the cause of the problem I will post it here.

 

[pjw001]

Hi kjv1611,

Well, it has just happened again and [Alt]+[F] did the trick. It has also re-enabled the toolbars.

Thank you for that.

 

[Deniall]

I've been following this thread with an elusive feeling of déjà vu, and today I have pinned down the reason for the feeling.[&nbsp;] So I dug way back into my files.[&nbsp;]
Many years ago a work colleague was getting a problem like this.[&nbsp;] We never found the cause, but came up with a work-around.[&nbsp;] This was to execute the following line of VBA code.

Application.CommandBars("Worksheet Menu Bar").Enabled = True

 

[kjv1611]

Glad that worked out, pjw001.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

And Deniall, that's possibly worth setting that short snippet up with a keyboard shortcut to run wheenver it happens IF that person has it happen frequently. Otherwise, they'll probably forget the keyboard shortcut. ]wink]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[pjw001]

How do I execute a VBA command if none of the buttons and icons work.

The only function available to me is the "X" in the top right corner of the screen, which fortunately asks if I want to save the file.

 

[Deniall]

I attach the actual spreadsheet in which I passed the solution to my work colleague.[&nbsp;] It lists several possible ways to get the command executed (including Ctrl-Shift-G which is the shortcut to the macro the spreadsheet contains - and which is still active).

 

[Deniall]

When I went to test the download of the spreadsheet I just posted, my anti-virus software stripped out all the VBA code.[&nbsp;] In case OP has similarly officious AV system, here it is again, this time shallowly disguised by hiding it in a compressed folder.

 

[Deniall]

That fooled my AV system adequately.[&nbsp;] Hopefully yours is no smarter.

 

[pjw001]

I think I have found the cause.

If File Explorer is previewing a spreadsheet, even in another folder, the toolbars and icons no longer work.

Thank you to all who have contributed to this. It has been educational for me.