Return to worksheet from backstage view, i.e. close backstage view or exit backstage view

Hello Everyone,

this is my first post ever because I could not find any answers while searching the forum (which usually helps me).

I have written two macros that run smoothly when triggered via buttons. The first one saves the macro-enable workbook under a name the user enters while the file type is fixed to .xlsm. The second one "saves" a range of cells to PDF with a name also entered by the user. There are a bunch of conditions and I cover all possible scerarios i.e. cancel, overwrite existing files as well as initial file names.

Now, I added a beforeSave event in case the user ignores my buttons. This event gets triggered when the user clicks 'FILE' followed by 'Save As ..'. Once 'FILE' is clicked the so called backstage view is shown. In the event I ask the user if s/he wants to save as .xlsm or as PDF (or cancel). Again both procedures work as intended, but after PDF or cancel is clicked, the backstage view is still shown. I cannot find a way to "close" the backstage view and make the worksheet visible again. I have tried to SendKeys Escape with Wait (Escape closes the Backstage View), but it gets ignored. I am pretty much a noob when it comes to VBA, so I even tried recording the actions "Click File and click the return arrow in the upper left", which does not get recorded as this does nothing to the file (I guess).

Is there a one line code to close backstage view? Can I call that arrow in the upper left corner of the backstage view and "click" it via VBA?

FYI: Usually, when you click 'File' > 'Save As' and then "Cancel' the Save As dialogue, the backstage view also does not close - only, if you click 'Save' in the dialogue.
Also, once the event is finished running, the Workbook seems "unactivated", i.e. one has to kind of click twice, first to "go back to the workbook" (as in changing the program currently used) and then once more to click the arrow. Maybe the SendKeys would work if the workbook is "reactivated"?

Thanks a lot for your time,
Freddy

Hi,

I can only think of two options and both involve using XML in the CustomUI part of the workbook. The first option is simply to set up an onLo ad callback so that you can get access to the Ribbon object, which will allow you to call its ActivateTabMso method to activate a specific tab at the end of your code, which may solve your problem. The second, more detailed option, would be to actually customise the Backstage view to add your own save buttons. You can then simply set their isDefinitive attribute to have Backstage view automatically close after they are clicked.

No, I would wait until you actually have a working solution. If you haven't already found it, it will be extremely worthwhile looking through these pages- http://www.rondebruin.nl/win/section2.htm
If you don't like the CustomUI editor linked to on those pages, or have issues with installing programs, you may want to have a look at Andy Pope's add-in here- http://www.andypope.info/vba/ribboneditor_2010.htm

Hi Don, once again thanks for your input. I have not had the time to check out the links you sent and most likely will not get around it for some time. The "bug" is still there and I have "worked around it" by adding a message box with the FYI to click the back arrow in the upper left corner, which that pops up only in the respective scenarios.
As advised I won't set the thread to solved.