macro help

[Sandy]

Excel 2007 -The following works just fine :-

Sub DisplayRibbon()

Application.DisplayFullScreen = False

End Sub

However when I do this :-

Private Sub Workbook_BeforeClose(Cancel As Boolean)

DisplayRibbon

******Other code here******

End Sub

the formula bar displays and but not the Menus or Ribbon.

Any ideas?
Sandy

 

[Ron de Bruin]

Hi Sandy

You can use a Excel4 macro

See

Sub HideRibbon()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub


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

See also this page for more info
http://www.rondebruin.nl/ribbon.htm

 

[Sandy]

I have tried that Ron - the point is that I cannot get the ribbon (or more
importantly) the Close File or Close Application buttons to be visible. When
the "Do you want to save the changes......etc" dialog box appears Clicking
Yes or No is fine but if Cancel is selected there are no Close Buttons on
view.

The code you suggest works fine on its own but not when associated with the
Worksheet_BeforeClose event (at least not in my application).
Sandy

 

[Ron de Bruin]

Try this instead of the open and beforeclose events Sandy

Private Sub Workbook_Activate()
HideRibbon
End Sub

Private Sub Workbook_Deactivate()
ShowRibbon
End Sub