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

  • Thread starter Thread starter gab1972
  • Start date Start date
G

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
 
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.
 
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.
Click to expand...

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?
 
Back
Top