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

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

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

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?


"gab1972" wrote:

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