vba- hiding ribbon issue

davewatson86

New Member
Joined
Jul 8, 2019
Messages
30
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
Code:
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
Code:
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
Code:
Private Sub Worksheet_Activate()

Call masque

End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Not sure I understand but what happens if you try introducing a small delay in the Deactivate event before calling the "normal" routine ?

Code:
Sub Workbook_Deactivate()

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

End Sub

then in the normal module :
Code:
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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 :
Code:
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
 
Upvote 0
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.
 
Upvote 0
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 :cool:

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

Regards
Dave
 
Upvote 0
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 :cool:

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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top