Thursday, June 11, 2020

Hide Ribbon Tabs, Scroll bars and Formula bar in Excel



Private Sub Workbook_Open()
    Application.CommandBars("Ply").Enabled = False    '' disable right click on sheet tabs
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" ''hide ribbon tabs
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = False
    ActiveWindow.DisplayWorkbookTabs = False
    ActiveWindow.DisplayHorizontalScrollBar = False
    ActiveWindow.DisplayVerticalScrollBar = False
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
    Range("K1").Select
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Ply").Enabled = True     '' enable right click on sheet tabs
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True
    ActiveWindow.DisplayHorizontalScrollBar = True
    ActiveWindow.DisplayVerticalScrollBar = True
End Sub

Private Sub Workbook_Activate()
    Application.CommandBars("Ply").Enabled = False    '' disable right click on sheet tabs
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = False
    ActiveWindow.DisplayWorkbookTabs = False
    ActiveWindow.DisplayHorizontalScrollBar = False
    ActiveWindow.DisplayVerticalScrollBar = False
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
    Range("K1").Select
End Sub

Private Sub Workbook_Deactivate()
    Application.CommandBars("Ply").Enabled = True   '' enable right click on sheet tabs
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Cancel = True   '' disable right click on all sheets
End Sub

Below is the snapshot  of the Excel sheet with hidden formula bar, scroll bars, ribbon tabs, status bar and disabled right click.



No comments:

Post a Comment

Hot Topics