Excel auto-hide ribbon difference between "manual way" and vba code

[Das443344]

Hello,

I need to have my Excel file with the ribbon hidden.
I have what I need if I do it manully with the button "auto-hide ribbon " from the "ribbon display option":

If i do it with vba code I don't have the same result:

as you can see, with the vba code Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)", the top and bottom bars are different. So the vba code is not doing what you can do manually.

I don't understand how to do in vba to obtein the same result as if I do it manually.

I hope is clear

thank you for your support!

 

[Jaafar Tribak]

See if this works for you :

In a Standard Module:

Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongLong, ByVal nIndex As Long) As LongLong
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongLong, ByVal nIndex As Long, ByVal dwNewLong As LongLong) As LongLong
    #Else
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    #End If
#Else
    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
#End If


Public Property Let HideRibbon(Optional ByVal bHideExcelCaption As Boolean, ByVal bHide As Boolean)

    Const GWL_STYLE = (-16)
    Const WS_CAPTION = &HC00000
    
    #If Win64 Then
        Dim lStyle As LongLong
    #Else
        Dim lStyle As Long
    #End If

    Call Application.ExecuteExcel4Macro("show.toolbar(""Ribbon"",Not(" & bHide & "))")
    
    lStyle = GetWindowLong(Application.hwnd, GWL_STYLE)
    If bHide Then
        If bHideExcelCaption Then
            lStyle = lStyle And Not WS_CAPTION
        End If
    Else
        lStyle = lStyle Or WS_CAPTION
    End If
    Call SetWindowLong(Application.hwnd, GWL_STYLE, lStyle)

End Property

Code Usage to toggle the ribbon + caption visibility :

Sub Hide_Ribbon()
    HideRibbon(bHideExcelCaption:=True) = True
End Sub

Sub Show_Ribbon()
    HideRibbon = False
End Sub

 

[Das443344]

Thank you for the answer, however is still not the result I was expecting, as you can see, the bottom bar is still present:

you can see the "ready" text and zoom buttons with 100%.
is not like when you do manually the auto-hide ribbon button.

thank you!

 

[ZVI]

To show/hide status bar use Application.DisplayStatusBar = True ' or = False

 

[Das443344]

Thanks! this is working! happy new year!