vba- hiding ribbon issue

[davewatson86]

Hello all

my vba knowledge is quite limited so please be patient.

have this code to hide the ribbon and all the unnecessary excel bits from the user.

it works a treat except when i open a workbook that has macros it throws an error because it is trying to show the ribbon etc before the user has clicked "Enable Editing" and "Enable Macros".

is there a way to make the code in this workbook wait until the user has allowed editing and macros in the new book?

any help is greatly appreciated.

code below.

Code in thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
        Application.DisplayFormulaBar = True
        Call normal

        ActiveWorkbook.Save
    
End Sub

Private Sub Workbook_Open()

        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        Application.DisplayFormulaBar = False
        Call masque

End Sub

Sub Workbook_Activate()

        Application.EnableEvents = False

        Call masque

        Application.EnableEvents = True

End Sub

Sub Workbook_Deactivate()

        Application.EnableEvents = False

        Call normal

        Application.EnableEvents = True

End Sub

code in normal module

Sub masque()


    With Application
        .ScreenUpdating = False
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .ScreenUpdating = True
    End With


    With ActiveWindow
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With


End Sub


Sub normal()


    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayHeadings = True
        .DisplayGridlines = True
    End With


    With Application
        .ScreenUpdating = False
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
        .ScreenUpdating = True
    End With
    
End Sub

in each worksheet

Private Sub Worksheet_Activate()

Call masque

End Sub

 

[Jaafar Tribak]

Not sure I understand but what happens if you try introducing a small delay in the Deactivate event before calling the "normal" routine ?

Sub Workbook_Deactivate()

    Application.EnableEvents = False
    
   [COLOR=#0000ff] Application.OnTime Now + TimeSerial(0, 0, 6), "normal"[/COLOR]

End Sub

then in the normal module :

Sub normal()

    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayHeadings = True
        .DisplayGridlines = True
    End With

    With Application
        .ScreenUpdating = False
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
        .ScreenUpdating = True
    End With    
    
[COLOR=#0000ff]    Application.EnableEvents = True[/COLOR]

End Sub

 

[davewatson86]

thanks for your reply.

i don't think a delay would help as it depends on how long it takes the user to allow editing and macros on the newly opened workbook.

regards
Dave

 

[Jaafar Tribak]

thanks for your reply.

i don't think a delay would help as it depends on how long it takes the user to allow editing and macros on the newly opened workbook.

regards
Dave

yes i know but I just wanted to know what happens when you add the delay and enable macros for the new opening workbook before the delay expires .... Does the error occurs in that case ?

And how about adding an On Error Resume Next at the start of the 'normal' macro ?

 

[Jaafar Tribak]

An alternative that I see it might work for you is hooking the application events and getting rid of the workbook Deactivate event.

This alternative should ensure that the 'normal' code is executed after the new workbook is fully opened :

change the entire code you have in the ThisWorkbook module to this :

Option Explicit

Private WithEvents xlapp As Application

Sub Workbook_Activate()
    Set xlapp = Application
    Call masque
End Sub

Private Sub xlapp_WorkbookActivate(ByVal Wb As Workbook)
    If Not Wb Is Me Then
        Call normal
    End If
End Sub

 

[davewatson86]

as soon as i open the new workbook i get method ontine of object application failed.

 

[davewatson86]

so this is one step closer it now waits for the new workbook to open but after it is open fully i have to activate the workbook with the code and then go back to the new workbook before it runs the code.

 

[davewatson86]

Just tried your code for a third time and it worked perfectly.....
No idea why it didn't work the first couple times but its all good now

Thank you very much Jaafar Tribak for your help with this as it was way over my knowledge level.

Regards
Dave

 

[Jaafar Tribak]

Just tried your code for a third time and it worked perfectly.....
No idea why it didn't work the first couple times but its all good now

Thank you very much Jaafar Tribak for your help with this as it was way over my knowledge level.

Regards
Dave

Yeah I forgot to mention that the code should work after you first run the Workbook_Activate event or you reopen the workbook .

Glad you got this working and thanks for the feedback.