On Jul 20, 2:23*pm, ker_01 wrote:
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" wrote:
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

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