How do I hide the formulabar and statusbar in one specific workbook without affecting all other opened workbooks using VBA?

user_18619546 0
Viewed 105

As the header says; How do I hide the formulabar and statusbar in just one workbook/sheet in Excel using VBA, without affecting all other opened workbooks/sheets ?

I have inserted two buttons in my worksheet. One that says "Full Screen" which hide the formulabar, statusbar, headings, scrollbar etc., and one button saying "Window Screen" that displays the before mentioned elements.

My problem is, that when I click on the "Full Screen"-button and hide the elements, I hide the elements in all opened workbooks/sheets in Excel, which I don't want. I only want the elements to be hidden in that specific sheet called "Dashboard".

Currently, I'm using this code:

Sub HideAll()    
    ActiveWindow.DisplayHorizontalScrollBar = False    
    ActiveWindow.DisplayVerticalScrollBar = False    
    ActiveWindow.DisplayHeadings = False    
    ActiveWindow.DisplayWorkbookTabs = False    
    Application.DisplayStatusBar = False    
    Application.DisplayFormulaBar = False    
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"    
End Sub
      
Sub ShowAll()    
    ActiveWindow.DisplayHorizontalScrollBar = True    
    ActiveWindow.DisplayVerticalScrollBar = True    
    ActiveWindow.DisplayHeadings = True    
    ActiveWindow.DisplayWorkbookTabs = True    
    Application.DisplayStatusBar = True    
    Application.DisplayFormulaBar = True    
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"    
End Sub
vba excel
2 Answers

Easy answer, you can't.

As you can see on the code Application.DisplayStatusBar the status bare and the formula bar are objects of the application and not objects of the window or worksheet.

Therefore if you hide them the get hidden in the entire application.

As a workaround you can open a second Excel instance which then has its own application where you can hide them. Note that this might come with other disadvantages as the 2 Excel instances don't "see" each other. So if you are trying to interact between them it might be more difficult.

Another workaround could be that you hide them when your workbook gets activated and you show them whenever your workbook gets deactivated. But still they get hidden in the entire application.

user_32196130

why wouldn't it work?

some codes (ActiveWindow) work for a specific file, for example, like this:

ThisWorkbook.Windows(1).DisplayHeadings = False

unfortunately not for "Application" codes. There it could work via IF/ELSE:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Name = "Sheet1" Then
     Application.DisplayFormulaBar = False
Else
     Application.DisplayFormulaBar = True
End If

End Sub
user_196309460
Related