.DisplayFormulaBar = True doesn't display formula bar...

[gab1972]

I've been using this coding for a while. I like it because it limits
my users to tinkering with the cells when I don't want them to, but it
has never given me back my Formula Bar after I close the sheet. I've
tried various different edits of the coding and I can't get it to
bring the formula bar back. It hides it, but when I close, it doesn't
come back. Any clues?

Option Base 1
Dim MoveAfterReturn As Boolean
Dim MoveAfterReturnDirection As XlDirection
Dim CBvisible() As Boolean

Private Sub Workbook_Open()

Dim i As Integer

'Hide all commandbars, including formula bar, but not Worksheet Menu
Bar

With Application

ReDim CBvisible(.CommandBars.Count)

For i = 1 To .CommandBars.Count
CBvisible(i) = .CommandBars(i).Visible 'save original
visibility state
If .CommandBars(i).Name <> "Worksheet Menu Bar" Then
If .CommandBars(i).Visible Then .CommandBars(i).Visible =
False
End If
Next i

.DisplayFormulaBar = False

With .CommandBars("Worksheet Menu Bar")
For i = 1 To .Controls.Count
Select Case .Controls(i).Caption
Case "&File", "&Help"
Case Else
.Controls(i).Visible = False
End Select
Next i
End With

'save current settings so they can be restored later,
'then set enter key to move down
MoveAfterReturn = Application.MoveAfterReturn
MoveAfterReturnDirection = Application.MoveAfterReturnDirection
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlToRight
End With

'Turn off row and column headings
ActiveWindow.DisplayHeadings = False

Sheets("Lists").Range("I2").Value = ""
Sheets("Home").Select
PermitTrackerSplash.Show

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Integer

'Unhide all commandbars, including formula bar, but not Worksheet
Menu Bar

With Application

For i = 1 To .CommandBars.Count
If .CommandBars(i).Visible <> CBvisible(i) Then
.CommandBars(i).Visible = CBvisible(i)
End If
Next i

.DisplayFormulaBar = True

With .CommandBars("Worksheet Menu Bar")
For i = 1 To .Controls.Count
.Controls(i).Visible = True
Next i
End With

'restore move-after-enter original settings
.MoveAfterReturn = MoveAfterReturn
.MoveAfterReturnDirection = MoveAfterReturnDirection
End With

'Turn on row and column headings
ActiveWindow.DisplayHeadings = True

End Sub

 

[ker_01]

This works as expected on my installation of XL2003, hiding then showing the
formulabar.

Sub test1()
Application.DisplayFormulaBar = False
End Sub

Sub test2()
Application.DisplayFormulaBar = True
End Sub

I wasn't able to see anything wrong in your code, but without knowing
everything else you are doing with the workbook, I'm not sure what to suggest
you start looking.

 

[gab1972]

This works as expected on my installation of XL2003, hiding then showing the
formulabar.

Sub test1()
Application.DisplayFormulaBar = False
End Sub

Sub test2()
Application.DisplayFormulaBar = True
End Sub

I wasn't able to see anything wrong in your code, but without knowing
everything else you are doing with the workbook, I'm not sure what to suggest
you start looking.

I discovered if I use File, Exit...everything works as it should.
However, if I use my Exit macro button, it does everything button show
the formula bar.

Sub ExitProg()
ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[ker_01]

Strange. Have you tried putting a doevents or a 5-second timer in after that
statement, to see if maybe Excel just hasn't finished processing the request?