Hiding the ribbon

[adam]

Code: Select all

Private Sub Workbook_Activate()
    On Error Resume Next
    With Application
        .DisplayFullScreen = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
    End With
     
End Sub
 
Private Sub Workbook_Deactivate()
    On Error Resume Next
    With Application
        .DisplayFullScreen = False
        .CommandBars("Worksheet Menu Bar").Enabled = True
    End With
     
End Sub

The above code when placed in the “Thisworkbook” module hides the default excel menu thread after enabling macro security on excel workbook.
But when Excel prompts the user to enable macros it shows the menu thread and then when the user enables the macro ribbon, it hides the menu thread.

Does excel have the capability or how can the code me modified to show only the “enable macros ribbon” without showing the “menu thread” on excel workbook open.

[HansV]

I have moved this post to a separate thread because it has nothing to do with the thread in which you posted it (Hiding the title bar).

[HansV]

Your code mentions the Worksheet Menu Bar, which is only available in Excel 2003 and before, but you also mention the ribbon, which is only available in Excel 2007 and later, so I don't understand what you're doing here.

[adam]

What I'm trying to do is to hide the excel 2007 menu ribbon when opening excel workbook.

I'm trying to find a way when the workbook is open; only the enable macro ribbon to be visible in excel 2007.

I hope I have made my question clear.

[HansV]

You already have a line

.DisplayFullScreen = True

This will mimimize the ribbon.

There is no "enable macro" ribbon as far as I know.

[adam]

There is no "enable macro" ribbon as far as I know.

by the enable macro ribbon i meant the button on top of excel to enable macros.

[HansV]

Do you mean this dialog?

x124.png

[adam]

When a macro enabled workbook is opened it displays the bar or something saying "Security Warning Macros have been disabled. Options...
this is what I'm referring to.

I hope I have made my question clear

[HansV]

Either add the folder containing the workbook to your trusted locations, or do the following:
- Click the Office button.
- Click Excel Options.
- Click Trust Center.
- Click Trust Center Settings...
- Click Macro Settings.
- Select "Disable all macros with notification".
- Click OK twice.

[adam]

Thanks for the reply. Ill follow as guided.

[Jan Karel Pieterse]

Do you want to hide the ribbon entirely all the time your workbook is open?

[Jan Karel Pieterse]

Also, check out this page (pay special attention to the downloads there, I think the dictator samples are relevant):

http://www.rondebruin.nl/ribbon.htm

[adam]

Yeah Jan,I had initially checked the site you had given. And I too preferred to choose the document with the name "XL Dictator (A)". The document shows up with a macro enabled sign. But there's no button to enable the macro.

Any suggestions regarding this please?!!

[Jan Karel Pieterse]

The file does not show an enable macros button because it does not contain any macros, just custom ribbon xml to hide the ribbon.

[adam]

how can it be customized not to show the office button which it already shows.

[Jan Karel Pieterse]

Does this do the trick:

Code: Select all

Hide/Show the whole Ribbon with VBA

You can use this VBA example posted by Jim Rech to Hide/Show the whole ribbon.
Sub HideRibbon()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub


Sub ShowRibbon()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub

(from http://www.rondebruin.nl/ribbon.htm)

[adam]

Thanks for the reply & the reference code. Ill try implying it.