VBA to Hide every Excel Ribbon (just cells)

[countryfan_nt]

Hello friends, Hope all is well! Need your help please.

I want edit the code below. it supposed to show only excel cells, no ribbons whatsoever.
I know there is the manual option of 'auto-hide' ribbon; but I am wondering if it can be done via VBA.

Can you pleas help me edit the below codes please?

Thank you very much in advance.

Sub ShowRibbon()
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",True)"

    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True
    ActiveWindow.DisplayHeadings = True
    ActiveWindow.DisplayHorizontalScrollBar = True
    ActiveWindow.DisplayVerticalScrollBar = True
    Application.ScreenUpdating = True

Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",true)"

End Sub

Sub HideRibbon()

Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)"

    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = False
    ActiveWindow.DisplayWorkbookTabs = False
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayHorizontalScrollBar = False
    ActiveWindow.DisplayVerticalScrollBar = False
   
Application.DisplayFullScreen = False
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True

End Sub

 

[Jaafar Tribak]

Try this :

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Private Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hwnd As LongPtr) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function DrawMenuBar Lib "user32" (ByVal hwnd As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Property Let ShowExcelCaption(ByVal Show As Boolean)

    Const GWL_STYLE As Long = -16
    Const WS_CAPTION = &HC00000
    
    With Application
        If Show Then
            Call SetWindowLong(.hwnd, GWL_STYLE, GetWindowLong(.hwnd, GWL_STYLE) Or WS_CAPTION)
        Else
            Call SetWindowLong(.hwnd, GWL_STYLE, GetWindowLong(.hwnd, GWL_STYLE) And Not WS_CAPTION)
        End If
        Call DrawMenuBar(.hwnd)
    End With

End Property


Sub ShowRibbon()
    [COLOR=#0000ff]ShowExcelCaption = True[/COLOR]
    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True
    ActiveWindow.DisplayHeadings = True
    ActiveWindow.DisplayHorizontalScrollBar = True
    ActiveWindow.DisplayVerticalScrollBar = True
    Application.ScreenUpdating = True
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",true)"
End Sub

Sub HideRibbon()
    [COLOR=#0000ff]ShowExcelCaption = False[/COLOR]
    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = False
    ActiveWindow.DisplayWorkbookTabs = False
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayHorizontalScrollBar = False
    ActiveWindow.DisplayVerticalScrollBar = False
    Application.DisplayFullScreen = False
    Application.WindowState = xlMaximized
    ActiveWindow.WindowState = xlMaximized
    Application.ScreenUpdating = True    
End Sub

 

[countryfan_nt]

Ustath Jaafar!!! that was awesome. 1000 Shukr.

a new challenge came up. I got a bug: "Code Exception has been interrupted".
The yellow highlight is on the line: Application.DisplayFormulaBar = True

for both codes

Sub ShowRibbon()

Sub HideRibbon()

Sorry to bother you, your kind help please

 

[Jaafar Tribak]

Hi countryfan_nt,

You are welcome.

Difficult to tell.

How and when are you calling those ShowRibbon and HideRibbon Procedures ? Are you calling them upon opening\closing the workbook or when loading a userform etc ? and is setting the other application & ActiveWidow Properties also raising an error ?

Regards.