Show Excel 2007 Ribbon in XLS file using Excel VBA

Question

I have a excel dashboard which works such that before the excel file is closed, I would like to display all the EXCEL ribbon, so that next time excel is opened, the application / excel will show the ribbon. At present, it does not show the ribbon if excel is opened.

Private Sub Workbook_BeforeClose(cancel As Boolean)

    On Error Resume Next

    Application.ScreenUpdating = True
    ActiveWindow.DisplayWorkbookTabs = True
    Application.DisplayFormulaBar = True
    Application.DisplayFullScreen = False
    Application.DisplayStatusBar = True
    Application.DisplayScrollBars = True
    Application.ScreenUpdating = True
    Sheets("Introduction").Select  

End Sub


This is an .xls file with Macro and supposed to work in Excel 2003 and Excel 2007.

Also, if "Cancel" is clicked, I do not want to show any of the above / ribbon, as user is supposed to get a protected view of the excel dashboard.

I have already tried the following .................................................................... Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)".................... This does not work. It just displays for the time being, but after the application is closed, ribbon is not shown niether the bars. — user415457
I must be missing something but I don't understand your question. Why is the ribbon not visible in the first place? Is your code doing something to it? — Mathias

Alistair Collins Alistair Collins · Accepted Answer · 2011-02-15T15:47:17

If the ribbon is closed by default, you can open it again by double clicking on one of the tabs (for instance, the Home tab).

(See this for more details).

If, however, you wish to write an event to take place when the workbook opens, then use the Workbook_Open() event from the ThisWorkbook Excel Object.

Show Excel 2007 Ribbon in XLS file using Excel VBA

2 Answers