Hide Excel Ribbon 2007

[amit]

hi - i need to be able to hide the ribbon and formula bar in excel upon
opening the file, and undo these while closing it....i place the below code
in This Workbook...and it works fine....however i also loose the "Office
Button" with the code below...

i need to know what i need to include to the below code to allow the Office
Button(required for saving the file) to be include but still hide the ribbon
and formula bar.

Private Sub Workbook_Open()
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
Application.DisplayFullScreen = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFormulaBar = True
Application.DisplayFullScreen = False
ActiveWindow.DisplayHeadings = True
End Sub

 

[amit]

just to be clear....i need the view that we get when double clicked on "Home"
in excel 2007

 

[Mark Ivey]

Give this a go...

Sub hide_unhide_Ribbons()
Application.SendKeys ("^{F1}")
End Sub


Mark

 

[Héctor Miguel]

hi, guys !

just to be sure it "acts" the way you need...

Sub Minimize_Ribbon()
If Application.CommandBars("Ribbon").Height > 80 Then SendKeys "^{F1}"
End Sub

Sub Full_Ribbon()
If Application.CommandBars("Ribbon").Height < 80 Then SendKeys "^{F1}"
End Sub

hth,
hector.

 

[Mark Ivey]

Great addition Hector...

Thanks for the input.

Mark

 

[amit]

thx Mark and Hector this worked great....although i did make a change i
wanted this to happen when opening the file and reverse the change when
closing the file and so called the minimize n expand subs within open and
beforeclose subs...

Private Sub Workbook_Open()
Application.DisplayFormulaBar = False
Call Minimize_Ribbon
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFormulaBar = True
Call Full_Ribbon
End Sub

Sub Minimize_Ribbon()
If Application.CommandBars("Ribbon").Height > 80 Then SendKeys "^{F1}"
End Sub

Sub Full_Ribbon()
If Application.CommandBars("Ribbon").Height < 80 Then SendKeys "^{F1}"
End Sub

just putting this outthere for anyone who might need a similar
functionality...thx again...

 

[amit]

just another note.....this seems to delay the opening of the file by a couple
of seconds...any reasons why????

 

[Héctor Miguel]

hi, amit !

just another note... this seems to delay the opening of the file by a couple of seconds... any reasons why????

- when the opening of your file joins the opening of excel (i.e. from a shortcut) ?

- only the first time ?... or every time you open your file (after excel is running) ?

(just in case and AFAIK) the sendkeys method runs (only)
- AFTER the calling procedure is ended (branches included)...
- at the moment any interactive dialog is showed (expecting for any user response)

hth,
hector.

 

[Rick Rothstein \(MVP - VB\)]

I realize I am coming to this thread after no one will be looking at it;
but, for the archives, you can show and hide the Ribbon and Formula bar
directly (that is, without resorting to SendKeys)...

Sub HideRibbonAndFormulaBar()
ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
End Sub

Sub ShowRibbonAndFormulaBar()
ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
End Sub

Or, as a simple "toggle" macro...

Sub ToggleRibbonAndFormulaBar()
ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & _
CStr(Not Application.DisplayFormulaBar) & ")"
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
End Sub


Rick

 

[Rick Rothstein \(MVP - VB\)]

Sub ToggleRibbonAndFormulaBar()

ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & _
CStr(Not Application.DisplayFormulaBar) & ")"
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
End Sub

Probably a better layout for the toggle routine would be this...

Sub ToggleRibbonAndFormulaBar()
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & _
CStr(Application.DisplayFormulaBar) & ")"
End Sub

Rick

 

[amit]

hi Rick - thx for the feedback....the short key did cause issues when closing
the file......becos i placed those codes in open and close subs...evrytime i
closed the file Help will pop up becos of F1.

i will try your method. thx.

 

[fadel hamwi]

hello All
I used your advice but in C# language :

ApplicationClass Application = new ApplicationClass ();
Application.Workbooks.Open("wb1.xls",missing ,....)
string str = "\"SHOW.TOOLBAR(\"\"Ribbon\"\",false)\"";
Application.ExecuteExcel4Macro(str);

but when I run my program no thing happen even there is no errors or
exceptions
any advice ??


*** Sent via Developersdex http://www.developersdex.com ***