I have a Workbook that, when opened, hides the 'Status Bar', the 'Formula Bar' and the 'Worksheet Tabs' and resets them when the workbook is closed.
Private Sub Workbook_Activate()
On Error Resume Next
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
.CommandBars("Worksheet Menu Bar").Enabled = False
End With
With ActiveWindow
.DisplayWorkbookTabs = False
End With
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
.CommandBars("Worksheet Menu Bar").Enabled = True
End With
With ActiveWindow
.DisplayWorkbookTabs = True
End With
End Sub
On Sheet1, I have a Shape (Rounded Rectangle) which I'm using as a toggle button.
The button starts by saying 'Show'. When the user clicks on the button, the macro below, shows the formula bar, the status bar and the worksheet tabs and the button text changes to 'Hide'. So far, so good.
If the user now saves the worksheet, closes it and then re opens it, the toggle button still say's 'Hide' because this is the how it was left when the user saved and closed the workbook, however, because the workbook is set to open fullscreen, the button actually needs to say 'Show', when the workbook is opened?
How can I ensure that when the workbook is opened, the button text will always say 'Show' by default, irrespective of what it said when the user saved and closed the workbook?
Sub RoundedRectangle2_Click()
Dim shp As Shape
With ActiveSheet
Set shp = .Shapes(Application.Caller)
End With
With shp.TextFrame.Characters
If .Text = "Show" Then .Text = "Hide" Else .Text = "Show"
End With
With ActiveSheet
Application.DisplayStatusBar = Not Application.DisplayStatusBar
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
ActiveWindow.DisplayWorkbookTabs = Not ActiveWindow.DisplayWorkbookTabs
End With
End Sub
I've attached a simple sample to demonstrate.
Bookmarks