Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Autohide Ribbon at opening

0

Hello Forum

I'm trying to write a code that hides Ribbon and menus when opening the workbook.
But there seems to be a bug - but where?

I get this message:

"Run-time error '438':

Object doesn't support this property or method"

Can somebody help ?

best regards

Morten Magni

Private Sub Workbook_Open()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .ExecuteExcel4Macro "Show.Toolbar(""Ribbon"", False)"
        .WindowState = xlNormal
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .DisplayStatusBar = False
        .DisplayFormulas = False
        .DisplayScrollBars = False
        .Width = 845
        .Height = 408
    End With
    With ActiveWindow
        .DisplayWorkbookTabs = False
        .DisplayRuler = False
        .DisplayHeadings = False
        .DisplayGridlines = False
    End With
    With Application
         .ScreenUpdating = True
         .Calculation = xlCalculationAutomatic
    End With
End Sub
excel
MS Office Version: Office365
posted by: magnificent (rep: 4) on Thu May 11, 2023 at 1:28 am
Answer
Discuss

Answers

0
Selected Answer

Hi Morten and welcome to the Forum.

The problem is that you're trying to set .DisplayFormulas for the Application but it is a property of windiws (here the ActiveWindow) notthe Applcation (hence the "Object does not support..." error).

I think you meant to set Application.DisplayFormulaBar to False instead, so you just need to change the line in bold below (optionally doing the second bold item too for the window):

Private Sub Workbook_Open()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .ExecuteExcel4Macro "Show.Toolbar(""Ribbon"", False)"
        .WindowState = xlNormal
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .DisplayStatusBar = False
        .DisplayFormulaBar = False
        .DisplayScrollBars = False
        .Width = 845
        .Height = 408
    End With
    With ActiveWindow
        .DisplayWorkbookTabs = False
        .DisplayRuler = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayFormulas = False
    End With
    With Application
         .ScreenUpdating = True
         .Calculation = xlCalculationAutomatic
    End With
End Sub

Hope this fixes things for you- if so, please remember to return and mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Thu May 11, 2023 at 2:40 am
Discuss

Discussion

Thanks for selecting my Answer, Morten.

I meant to add the tip that you can step through a macro line-by-line using the F8 button repeatedly until you find the faulty line (the one where the Object does not support... error arises).
John_Ru (rep: 6142) May 11, '23 at 7:26 am
I'm greatful for your help - thankyou very much ;-)
magnificent (rep: 4) May 11, '23 at 8:52 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login