ActiveX Controls - ToggleButton


The following code provides you with two choices.

  1. Switching to Full Screen mode. This is equivalent to the ribbon sequence View > Workbook Views > Full Screen in Excel 2010, or Ribbon Display Options > Auto-hide Ribbon in Excel 2013, and
  2. Enabling a minimal view, referred to as Clear Screen mode in this module.

The animated toggle view workbook is demonstrated in figure 1.


/xlf-toggle-screen.gif
Fig 1: xlf Animation WS screen toggle. The image is on the drawing layer, and covers any WS data. Moving the image to the WS background is left as an exercise

Required components

''
''  REQUIRED: WS controls (ActiveX)
''  1.  Toggle Button
''      Name: tglFullScreen
''      Caption: "Full Screen"
''
''	2.  Toggle Button
''      Name: tglClearScreen
''      Caption: "Clear Screen"	
''

1. Toggle Full Screen



Code 1: tglFullScreen ToggleButton Click Event 
Private Sub tglFullScreen_Click()
    If tglFullScreen Then
        Application.DisplayFullScreen = True
        tglFullScreen.Caption = "Normal"
        tglClearScreen.Enabled = False
    Else
        Application.DisplayFullScreen = False
        tglFullScreen.Caption = "Full Screen"
        tglClearScreen.Enabled = True
    End If
End Sub

2. Toggle Clear Screen



Code 2a: tglClearScreen ToggleButton Click Event 
Private Sub tglClearScreen_Click()
    If tglClearScreen Then
        tglClearScreen.Caption = "Normal"
        tglFullScreen.Enabled = False
        Call ClearScreenOn
    Else
        tglClearScreen.Caption = "Clear Screen"
        tglFullScreen.Enabled = True
        Call ClearScreenOff
    End If
End Sub


Code 2b: tglClearScreen sub procedure 
Private Sub ClearScreenOn()
    
    With Application
        .Caption = "xlf :: Spreadsheet Modeling"
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
        .ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
    End With
    
    With ActiveWindow
        .Caption = ""
        .DisplayGridlines = False
        .DisplayHeadings = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
        .DisplayWorkbookTabs = False
    End With
        
End Sub


Code 2c: tglClearScreenOff sub procedure 
Private Sub ClearScreenOff()

    With Application
        .Caption = "Microsoft Excel"
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
        .ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
    End With
    
    With ActiveWindow
        .Caption = ActiveWorkbook.Name
        .DisplayGridlines = True
        .DisplayHeadings = True
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = True
    End With
        
End Sub