Ribbon & Ribbon Tabs

[mikeymay]

When I worksheet is selected I want to hide the ribbon but show the ribbon tabs to allow the user to still access the ribbon functions.

Also when another file is selected I want the Workbook_Deactivate to check if the Ribbon is visible and if not to make it visible again.

I can find code to hide the ribbon and ribbon tabs but nothing to leave the ribbon tabs and to also check whether it's currently visible or not.


TIA

 

[Fluff]

Are you talking about switching between sheets or workbooks?

 

[mikeymay]

Are you talking about switching between sheets or workbook

Yes, when a different workbook is selected I need to detect the visiblity of the ribbon and make visible if not visible.

 

[Fluff]

Ok how about

Private Sub Workbook_Activate()
   If Application.CommandBars("Ribbon").Height > 100 Then
      Application.CommandBars.ExecuteMso ("MinimizeRibbon")
   End If
End Sub

 

[mikeymay]

This doesn't work.

Even when the ribbons are hidden (with just the ribbon tabs visible - File, Insert, etc) the height is the same as when visible and therefore, doesn't make it visible again.

 

[mikeymay]

The user can specify whether they want the following display or not

If they have the above display set I then need to select the Show Tabs and Commands (as below) when the workbook is deactivated

 

[mikeymay]

Sorry...I have just tried this again and it does work. I think I manually played with the ribbon that returned the same height as when visible.

 

[Fluff]

You're welcome & thanks for the feedback.