CODEWITHSUNDEEP
vbaexcel
Dmitry
Dmitry

Reputation: 315

Activate and deactive ribbon by 1 button VBA EXCEL

I have excel worksheet with 2 buttons. First button deactivates ribbon, second activates it. I need to combine both functions into one button. Code sample:

Private Sub CommandButton1_Click()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False
End Sub

Private Sub CommandButton2_Click()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True
End Sub

Hope for help.

Upvotes: 0

Views: 263

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

You need to get the actual visibility state of the ribbon first.

Private Sub cmdToggleRibbon_Click()
    Dim isRibbonVisible As Boolean
    isRibbonVisible = Application.ExecuteExcel4Macro("Get.ToolBar(7,""Ribbon"")")

    If isRibbonVisible Then
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Else
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    End If
    Application.DisplayFormulaBar = Not isRibbonVisible
    Application.DisplayStatusBar = Not isRibbonVisible
    ActiveWindow.DisplayWorkbookTabs = Not isRibbonVisible
End Sub

Upvotes: 1

Related Questions