Hide ribbon if present

Topic

New Reply

To hide the ribbon in a particular workbook, i put the following statement into the workbook open event

Code:

Application.Sendkeys "^{F1}"

The ribbon was indeed gone. But, if I opened the workbook a subsequent time, the ribbon re-appeared.
The above instruction does act as a toggle switch.

Hence the question: can I check for the presence of the ribbon, before issuing the instruction?

Reply | Quote

Replies

Have a look at this thread from the databases forum.

I have not tried this in Excel.

It seems to me that ctrl F1 does not actually hide the ribbon- it just minimises it. Is that what you want?

Reply | Quote

This line ought to work.
It did for me.
It Hides the ribbon rather than Minimising it

But be aware it hides it in ALL Open workbooks,
so if you need to have them appear when the book changes,
then put the code on the Window activate event and deactivate events.

Code:

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) &  ",False)"

To Turn back on Use this

Code:

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) &  ",True)"

Code:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",False)"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",True)"
End Sub

Reply | Quote

It Hides the ribbon rather than Minimising it

When I tested this line in Excel it just minimised the ribbon, then as rvWoody said, it returned it to normal again next time.

This is what I expected as that is exactly what happens in Access. Hence my post about finding the height of the ribbon.

Reply | Quote

That’s interesting because it hid it on mine.

Reply | Quote

Your screen shot is what I see for the workbook, but not for Excel. I get this sort of view:

However, once the ribbon is minimized it stays that way the next time I open Excel.

Reply | Quote

This code is just the equivalent of typing Ctrl+F1.

According to Help this minimises or restores the ribbon.

Reply | Quote

This code is just the equivalent of typing Ctrl+F1.

Not on mine.

CTRL F1 minimises the ribbon on and off

If I run
Application.ExecuteExcel4Macro “SHOW.TOOLBAR(” & Chr(34) & “Ribbon” & Chr(34) & “,False)”

Then the ribbon disappears altogether.
That is why I put it on the Activate & De-Activate Events to make sure it only applied to a specific book.
I was not trying to lose it altogether, just when a specific workbook is activated.

If I remove the De-Activate code then it stays hidden regardless of changing to another workbook.
If I leave this off then it stays hidden until Excel is closed.
Next time Excel opens the ribbon returns.

Reply | Quote

I am talking about this bit of code

Code:

Application.Sendkeys "^{F1}"

as in the first post.

Your code hides the ribbon. The code posted at the start minimises/restores it.

What we don’t know if which of these rvWoody wants to do.

Reply | Quote

To detect whether r not the ribbon is minimized, you can check the height of the Ribbon Commandbar:
For me, the height is 63 when minimized and 168 when not. I’ve seen other values though, so to be on the safe side I test for >100:

If CommandBars(“Ribbon”).Height > 100 Then
‘Ribbon is NOT minimized
Else
‘Ribbon is Minimized
End If

Reply | Quote

To detect whether r not the ribbon is minimized, you can check the height of the Ribbon Commandbar:
For me, the height is 63 when minimized and 168 when not. I’ve seen other values though, so to be on the safe side I test for >100:

If CommandBars(“Ribbon”).Height > 100 Then
‘Ribbon is NOT minimized
Else
‘Ribbon is Minimized
End If

That is exactly what was in the post I linked to at the beginning of this thread.

Reply | Quote

That is exactly what was in the post I linked to at the beginning of this thread.

Duh, I overlooked that part. Funny that I arrived at the exact same solution.

Reply | Quote

Thanks for your replies and sorry for the delay in answering….

I have to admit that I didn’t think of the distinction between hiding and minimizing the ribbon.At a fist glance minimizing would be OK. But I’m confused as to the actual effect sending ctrl+F1

I did the following test on an empty workbook with the activate/deactivate events coded as follows: (I do want to restore the Ribbon, if I minimized it before)

Code:

Private bIShrankTheRibbon As Boolean 

Private Sub Workbook_Activate()
With Application
    If Val(.Version) >= 12 Then 'Minimize ribbon if present
        If .CommandBars.Item("Ribbon").Height > 100 Then
            .SendKeys "^{F1}"
            bIShrankTheRibbon = True
        End If
    End If
End With
End Sub

Private Sub Workbook_Deactivate()
If bIShrankTheRibbon Then Application.SendKeys "^{F1}"
End Sub

Private Sub Workbook_Open()
bIShrankTheRibbon = False
End Sub

When the workbook become visible, the Ribbon was minimized. Its actual height was now 59 down from a previous 150. When I opened an Access application, its ribbon too was minimized. Upon closing the workbook (X button upper right corner) the deactivate code with the sendkeys was executed. But when I now opened a fresh excel workbook or an access application, the ribbon was still minimized! As if the Sendkeys in the deactivate event were never executed (or twice executed?) What happened?

Moving the activate code to the Open event and the Deactivate code to the BeforeClose event gave the same result: the ribbon was minimized and stayed so. As I last resort, I used the WindowActivate and WindowDeactivate event, but still the same result.

This odd(?) behaviour, together with the rather arbitrary limit(100) to the height of the ribbon made me switch to the “Cambridge” solution:

Code:

Private bIShrankTheRibbon As Boolean

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
bIShrankTheRibbon = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",False)"
bIShrankTheRibbon = True
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
If bIShrankTheRibbon Then _
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(" & Chr(34) & "Ribbon" & Chr(34) & ",True)"
End Sub

I’ll stick with that solution for the moment, but I’m not reassured that it will be a lasting solution, as I fear that someday Excel4Macro support could be dropped. The following excerpt seems to point in that direction:

Migrating Excel 4 Macros to VBA

Prior to the introduction of VBA into Excel, we had our own macro facility – known as Excel 4 macros, used in Excel macro sheets. Most people have long since migrated their Excel 4 macros to VBA; however, some Excel 4 macro capabilities were missing from VBA, which made this migration difficult.

In Excel 2010, one of our goals was to remove any remaining barriers that people had to complete the migration of Excel 4 macros to VBA. This work generally fell into two categories:

Adding new objects, methods and properties to Excel’s object model for the few remaining Excel 4 macro functions that did not have equivalents in VBA.
Performance Improvements for Page Setup operations in VBA to provide similar performance to PAGE.SETUP() functionality in Excel 4 Macros.

Reply | Quote

This seems to work fine:

Reply | Quote

JPK: the DoEvents seem to be essential for success! The following code worked as you suggested:

Code:

Private bIShrankTheRibbon As Boolean

Private Sub Workbook_Activate()
With Application
    If Val(.Version) >= 12 Then 'Minimize ribbon if present
        If .CommandBars.Item("Ribbon").Height > 100 Then
            .SendKeys "^{F1}"
            bIShrankTheRibbon = True
            DoEvents
        End If
    End If
End With
End Sub

Private Sub Workbook_Deactivate()
If bIShrankTheRibbon Then
    Application.SendKeys "^{F1}"
    DoEvents
End If
End Sub

Private Sub Workbook_Open()
bIShrankTheRibbon = False
End Sub

Without that statement, the ^{F1} was apparently sent to whatever was active at that moment. At some point I even lost my numeric pad on the keyboard, or the Help menu popped up when it was not expected. If however you open a second workbook, while the first is still open, that too will have its ribbon hidden.

Therefore, I remain with the Excel4Macro (but I keep this solution in my archive, in case these macros loose their attractiveness)
Life is full of compromises!

Reply | Quote