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
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