Hide toolbar at workbook change

[nyx1888]

Hi everyone,

when I switch from workbook 1 to workbook 2, then the toolbar of workbook 1 should be hidden.

How can I realize this action in VBA?

Best regards
nyx1888

 

[Domenic]

You can use the workbook Activate and Deactivate event handlers, which need to be placed in the code module for ThisWorkbook (in the VBE, and in the Project Explorer window, right-click ThisWorkbook, and select View Code)...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Activate()
    [COLOR=green]'Your code to create the toolbar or code that calls the sub that creates the toolbar[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Deactivate()
    [COLOR=green]'Your code to remove the toolbar or code that calls the sub that removes the toolbar[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[nyx1888]

Hi Domenic,

your answer doesn't help, cause I don't know the code to perform my disered action.

Can anyone help me?

Best regards
Tony

 

[nyx1888]

bump

 

[Domenic]

Do you mean that you don't already have code that creates your toolbar? If so, try searching Google for the desired code.

 

[nyx1888]

Hi Domenic,

thanks for your priceless help. I have a code to hide the toolbar:

Private Sub Workbook_Deactivate()

Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.ScreenUpdating = True

End Sub

The problem is, that I hide with this code the toolbar of workbook 2 (the activated workbook). But I search a code to hide the toolbar of the deactivated workbook.

Best regards
Tony

 

[Domenic]

Oh I see. Yeah, when the ribbon is hidden, it does so for the active window. So when the workbook is deactivated and the Deactivate event is triggered, it hides the ribbon for the activated workbook, since it's now the active window. As far as I know, I think you'll need to use RibbonX code, which will be a bit more involved.

Is it really necessary to hide the ribbon for a workbook that is not the active workbook?

 

[nyx1888]

Hi Domenic,

I have an own ribbon that I created with custom UI editor, so if you have an idea, I would be very grateful. And yes it is necessary, cause the workbook is deactivated, but still visible on one half of the monitor.

Best regards
Tony

 

[nyx1888]

bump

 

[Domenic]

Maybe something like this...

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Dim[/color] bClose [color=darkblue]As[/color] [color=darkblue]Boolean[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeClose(Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
    bClose = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_WindowActivate([color=darkblue]ByVal[/color] Wn [color=darkblue]As[/color] Window)
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_WindowDeactivate([color=darkblue]ByVal[/color] Wn [color=darkblue]As[/color] Window)
    [color=darkblue]If[/color] bClose [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]If[/color] Wn.WindowState = xlMinimized [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]Dim[/color] ActWin [color=darkblue]As[/color] Window
    [color=darkblue]With[/color] Application
        .EnableEvents = [color=darkblue]False[/color]
        .ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Set[/color] ActWin = ActiveWindow
    Wn.Activate
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    ActWin.Activate
    [color=darkblue]With[/color] Application
        .EnableEvents = [color=darkblue]True[/color]
        .ScreenUpdating = [color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]