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

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

"amit" wrote:

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

Give this a go...

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


Mark

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

"amit" wrote:

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

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 wrote in message ...
Give this a go...
Sub hide_unhide_Ribbons()
Application.SendKeys ("^{F1}")
End Sub

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

Great addition Hector...

Thanks for the input.

Mark

"Héctor Miguel" wrote in message
...
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 wrote in message ...
Give this a go...
Sub hide_unhide_Ribbons()
Application.SendKeys ("^{F1}")
End Sub

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

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...
"Mark Ivey" wrote:

Great addition Hector...

Thanks for the input.

Mark

"Hctor Miguel" wrote in message
...
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 wrote in message ...
Give this a go...
Sub hide_unhide_Ribbons()
Application.SendKeys ("^{F1}")
End Sub

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

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

"amit" wrote:

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...
"Mark Ivey" wrote:

Great addition Hector...

Thanks for the input.

Mark

"Hctor Miguel" wrote in message
...
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 wrote in message ...
Give this a go...
Sub hide_unhide_Ribbons()
Application.SendKeys ("^{F1}")
End Sub

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

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.

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


"Héctor Miguel" wrote in message
...
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 wrote in message ...
Give this a go...
Sub hide_unhide_Ribbons()
Application.SendKeys ("^{F1}")
End Sub

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

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

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.

"Rick Rothstein (MVP - VB)" wrote:

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