Hide and bring back Ribbon

[Jose5847]

Hi all, so I am wondering if anybody can help me fix a problem. I have a sheet that when "workbook_open" its suppose to hide ribbon, headings, formula bar, tabs etc.. Which it does perfectly. The problem is that it is not going back to normal when I close. Below is the workbook_close VBA. Any help would be greatly appreciate it. And while I am at it, is there a way to hide all of the mentioned only for the workbook instead of all other excels opened while this one is open?

Sub workbook_Close()

   
    Worksheets("PastedReport").Visible = xlSheetVisible
    Worksheets("PastedReport").Cells.ClearContents
    Worksheets("PastedReport").Visible = xlSheetVeryHidden
    Worksheets("Sheet2").Visible = xlSheetVeryHidden
    Worksheets("Sheet3").Visible = xlSheetVeryHidden
    Sheets("Home").Visible = True
    Sheets("Home").Select
   
    ThisWorkbook.Saved = False
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True
    Application.DisplayFormulaBar = True
    Application.ScreenUpdating = True
   
   
End Sub

 

[jmacleary]

Hi there. Just a wild guess, but it may be that because you do nothing else after setting screenupdating back on, that excel 'loses' the changes? You could try putting something like

Sheets("Home").Range("A1").Select

at the end (just before the end sub).
For your other question, unfortunately the scope of the changes is at Application level, so is the same for all workbooks open in this instance of excel. One way to achieve roughly what you want would be to open this workbook in a separate instance of the excel program. You can do this by starting excel from the Programs list, and then opening your workbook in this 'new' excel.

 

[Jose5847]

JMACleary, thank you for the response. I tried it but it did not work. Then I noticed that it actually does work but I need to close all excel files before if can go back to normal. I dont like it but, for now, its ok.

Thanks again for the feedback and help!

 

[jmacleary]

You're welcome, even though I didn't really solve it. A couple of last ideas, you could try leaving out the

Application.ScreenUpdating = False

to see if that works, or try

Application.CalculateFull

at the end (I don't expect this to work, since it recalculates data rather than settings, but you never know).