Formula Bar in Excel VBA

[Alexky]

Excel does not restore a Formula Bar. I wrote codes for Open and BeforeClose events to hide and restore Formula, Menu and Tool Bars. It works perfectly when you launch it from VBA editor, but when you open and close a workbook it restores everything but a Formular Bar. I can't understand why. Could anybody help?

Cheers...

 

[Loomah]

I can't recreate your problem with the formula bar as it seems to work fine for me. Could you post the code?

The only other thing I could think of was that the code wasn't in the book you were closing but if that was the case then the command bars wouldn't be restored (thinking aloud!)
;-)

 

[Alexky]

Here it is...

Private Sub Workbook_Open()
On Error Resume Next
Application.ScreenUpdating = False
InitView
Sheets("1st page&quot.Select
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.ScreenUpdating = False
ActiveWorkbook.Saved = True
ExitView
End Sub

Sub InitView()
SetMenu
SetWindow xlOn
SetBars xlOn
End Sub
Sub ExitView()
ZapMenu
SetWindow xlOff
SetBars xlOff
End Sub

Sub SetMenu()
Dim myBar As CommandBar
Dim myButton As CommandBarButton
ZapMenu
Set myBar = CommandBars.Add(Name:="OCM", _
Position:=msoBarTop, _
MenuBar:=True)

Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonCaption
myButton.Caption = "&Start the exercise"
myButton.OnAction = "Start_routine"

Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonIcon

Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonCaption
myButton.Caption = "&Go to LDC data"
myButton.OnAction = "go_LDC_Data"
myButton.Visible = False

Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonIcon

Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonCaption
myButton.Caption = "&Go to LDC Chart"
myButton.OnAction = "go_LDC_Chart"
myButton.Visible = False

Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonIcon

Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonCaption
myButton.Caption = "E&xit"
myButton.OnAction = "ExitOCM"

myBar.Protection = msoBarNoMove + msoBarNoCustomize
myBar.Visible = True
End Sub

Sub SetBars(State)
Static myOldBars As New Collection
Dim myBar
If State = xlOn Then
For Each myBar In Application.CommandBars
If myBar.Type <> 1 And myBar.Visible Then
myOldBars.Add myBar
myBar.Visible = False
End If
Next myBar
Else
For Each myBar In myOldBars
myBar.Visible = True
Next myBar
End If
End Sub

Sub SetWindow(State)
Static myOldState
Application.ScreenUpdating = False
On Error Resume Next
If State = xlOn Then
myOldState = Application.WindowState
Application.WindowState = xlMaximized
Application.Caption = &quot;Optimal Capacity Mix&quot;

ActiveWindow.WindowState = xlMaximized
ActiveWindow.Caption = &quot;&quot;

Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False
ActiveWindow.DisplayWorkbookTabs = False
Else
Application.Caption = Empty
Application.WindowState = myOldState
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
ActiveWindow.DisplayWorkbookTabs = True
End If
End Sub

BTW, Status Bar does not restore either...

 

[Loomah]

Alexky
I've sat with very little to do all day then all of a sudden....

I've not been able to get your code to work more than once as it quits for me (cleanly) at

Set myBar = CommandBars.Add(Name:=&quot;OCM&quot;, _
Position:=msoBarTop, _
MenuBar:=True)

in the SetMenu routine

If it sets up your app ok and the only thing wrong on closing is that the formula bar is missing try moving the Application.DisplayFormulaBar = True line (my patent trial and error method!!). You could try it in a different position in SetWindow(State), move it up a couple of lines (this sounds very amaturish) or try it in the actual before close routine.

Sorry I ran out of time, I'll be back tomorrow.

 

[Alexky]

I have simplified a workbook to leave only substitution of the standard menu and delete/restore Formula and Status Bars. The custom menu has the only button exit which seems to be a problem. You can close the workbook using either this button or just closing it with a standard cross button in the right upper corner of the window. If you use the second method the Formula and Status Bars restore without any problems. Howwever, if you use the Exit button on the custom menu, it messes all up. I do not understand what is wrong with the custom window.

I can send you this workbook if you like, anyway I'll put the code below. If you can help I would be very obliged because this problem is driving me really crazy.

Cheers

For ThisWorkbook:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ExitView
End Sub

Private Sub Workbook_Open()
InitView
End Sub

For the module:

Option Explicit

Sub InitView()
SetWindow xlOn
SetMenu
End Sub

Sub ExitView()
SetWindow xlOff
ZapMenu
End Sub

Sub SetMenu()
Dim myBar As CommandBar
Dim myButton As CommandBarButton
ZapMenu
Set myBar = CommandBars.Add(Name:=&quot;OCM&quot;, _
Position:=msoBarTop, _
MenuBar:=True)
Set myButton = myBar.Controls.Add(msoControlButton)
myButton.Style = msoButtonCaption
myButton.Caption = &quot;E&xit&quot;
myButton.OnAction = &quot;ExitOCM&quot;
myBar.Protection = msoBarNoMove + msoBarNoCustomize
myBar.Visible = True
End Sub

Sub ZapMenu()
On Error Resume Next
CommandBars(&quot;OCM&quot.Delete
End Sub

Sub SetWindow(State)
Static myOldState
Application.ScreenUpdating = False
On Error Resume Next
If State = xlOn Then
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Else
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
End If
End Sub

Sub ExitOCM()
ActiveWorkbook.Close (False)
End Sub

 

[Loomah]

Alexky
I honestly don't know why this is reacting the way it does, in that the lines appear to be read ok (I removed error handling to check) and the book can be closed by conventional means.

The following appears to solve the problem but doesn't explain why it happens

Sub ExitOCM()
' added lines to restore formula & status bars
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
ActiveWorkbook.Close (False)
End Sub

One thing I was trying to do but haven't been able to was to use Workbook_BeforeClose as the onaction for your button but this is something I'm not used to and I don't even know if it's possible - if anybody else is reading this, can I?

Happy Friday
;-)

 

[Alexky]

Great, it works!!! Thanks a lot.