Hide command bars in 2003 and minimize the ribbon in 2007 on open - same workbook

davede · Mar 25, 2011

Hi all,

Have been slaving away for 6 hours or so trying to find a solution to this.

I am creating a workbook for distribution. Users may have excel 2003, 2007, or 2010. What I would like is to streamline the look of excel and still have it function like a program.

I need a macro to run when opened that will minimize the ribbon for 2007/2010 users and hide the commandbars excluding the worksheet menu bar for 2003 users.

Before you ask I do not want to use full screen mode because in excel 2003 the bottom goes behind the windows task bar, there is no close, minimize or resize option for the workbook (not the worksheet) and in 2007/2010 users lose the file menu all together.

It’s almost like full screen mode does opposite things in the different versions.

Any help please?

BobUmlas · Mar 25, 2011

If Application.Version >= 12 then Application.Sendkeys "^{F1}"

in your auto_open will minimize the ribbon -- it's a toggle, actually.

(yes, I don't like using sendkeys either)

davede · Mar 27, 2011

Excellent. Thank you so much.

I was looking around for something exactly like that to have different code execute depending on the version.

So now what i've got is:

Code:

Private Sub Workbook_Open()
  If Application.Version >= 12 Then Application.SendKeys "^{F1}"
  HideCommandbars
  End Sub

In the ThisWorkbook section.

Tested in 2003, 2007 and 2010 and works fine.

Yeah not a big fan of the sendkeys command - users may have already minimized the ribbon before opening --- then it will have the opposite effect by opening it again. Is there a way to counteract that?

The other item on the checklist is to have all the toolbars except the worksheet menu bar hide on open and then restore on close for 2003.

Is there a way to do this?

eromilly · Apr 13, 2011

You have likely figured this out by now, but a simple solution is...

If (Application.Version >= 12) And (.CommandBars("Ribbon").Height >= 80) Then
Application.SendKeys "^{F1}" 'Hide the Ribbon
End If

Or, conversely, to show the ribbon

If (Application.Version >= 12) And (.CommandBars("Ribbon").Height < 80) Then
Application.SendKeys "^{F1}" 'Show the Ribbon
End If

davede · Apr 13, 2011

Ahh thanks for that eromilly.

Yes i did resolve the issue eventually.

However I abandoned the idea of having the file menu visible.

One reason was that i am not a big fan of using send keys.

I decided to remove the command bars completely and but in my own set up buttons to give the functions that i wanted to retain for the user.

The full code i ended up was this in the 'This Workbook' area

Code:

Private Sub Workbook_Open()

'If App version is required for excel 2007+ because not a valid command
If Application.Version < 12 Then
FullScreen.HideCommandBars
Else
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End If

    With Application
        .DisplayStatusBar = False
        .DisplayFormulaBar = False
        .WindowState = xlMaximized
    End With

    'Code below is required or the sheet that is loaded on start will display all those things
    With ActiveWindow
        .DisplayHeadings = False
        .DisplayGridlines = False
        ' .DisplayZeros = False
        .DisplayWorkbookTabs = False
    End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'If App version is required for excel 2007+ because not a valid command
If Application.Version < 12 Then
FullScreen.ShowCommandBars
Else
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End If

    With Application
        .DisplayStatusBar = True
        .DisplayFormulaBar = True
        .DisplayAlerts = False 'This is supposed to stop the save button popping up on close
    End With

    With ActiveWindow
        .DisplayHeadings = True
        .DisplayGridlines = True
        ' .DisplayZeros = True
        .DisplayWorkbookTabs = True
    End With

    ThisWorkbook.Saved = True 'Fool Excel

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayWorkbookTabs = False
    On Error GoTo 0
End Sub



Private Sub Workbook_Activate()

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'If App version is required for excel 2007+ because not a valid command
If Application.Version < 12 Then HideCommandBars

End Sub

Private Sub Workbook_Deactivate()
' Should return command bars to other workbooks

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
'If App version is required for excel 2007+ because not a valid command
If Application.Version < 12 Then ShowCommandBars

End Sub

and in a module:

Code:

Sub HideCommandBars()
Dim cBar As CommandBar
For Each cBar In CommandBars
     cBar.Enabled = False
Next cBar
End Sub

Sub ShowCommandBars()
Dim cBar As CommandBar
For Each cBar In CommandBars
     cBar.Enabled = True
Next cBar
End Sub

Hide command bars in 2003 and minimize the ribbon in 2007 on open - same workbook

davede

New Member

Excel Facts

BobUmlas

Well-known Member

davede

New Member

eromilly

New Member

davede

New Member

Similar threads

Forum statistics

Share this page

Hide command bars in 2003 and minimize the ribbon in 2007 on open - same workbook

davede

New Member

Excel Facts

BobUmlas

Well-known Member

davede

New Member

eromilly

New Member

davede

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock