9.1 StatusBar and Caption

Application.StatusBar - and the User Interface:

• The StatusBar is located at the bottom of the Excel application window

  
  

  Fig 1: Status bar - located at the bottom of the Excel application window.

• The left side of the StatusBar displays the WS mode indicator: Ready, Enter, Point, ... (figure 1)
• The StatusBar is a property of the Application object
• The property is used to Return or Set the text in the StatusBar as a Read or Write String
• To write a custom message to the StatusBar use:

  Application.StatusBar = "Welcome to My Workbook"
      

• But this can cause a problem. The message will on the left of the StatusBar during the current session, and over-ride the Excel mode indicator information
• To return control of the StatusBar to Excel:

          ' To return control of the StatusBar to Excel:
  Application.StatusBar = False   ' or
  Application.StatusBar = ""      ' an empty string
      

• Control is normally returned with the Application.StatusBar = False statement
• You can add a time to only display a message for a preset interval:

  Sub SBCountDown()
  Const Blips As Integer = 8                      ' 8 second countdown
  Const Wait As String = "00:00:01"               ' 1 second interval
  Dim i As Integer
          Application.DisplayStatusBar = True     ' ensure that the StatusBar is visible
          For i = Blips To 1 Step -1
              Application.StatusBar = String(i, ChrW(9609)) & "  " & i
              Application.Wait Now + TimeValue(Wait)
          Next i
      Application.StatusBar = False
  End Sub
      

• About the SBCountDown macro: VBA.ChrW returns: ▉ and VBA.String repeats the character i times
• See link for UTF-8 Block Elements in the Range: Decimal 9600-9631. 9609 is named "left seven eights block""
• Here is an animated example:

Fig 2: StatusBar countdown - progress indicator with numerical countdown

ActiveWindow.Caption - and the User Interface:

• ActiveWindow is a property of the Application object and returns a Window object. (Application.ActiveWindow)
• The ActiveWindow is the one on top
• The Caption property Returns or Sets the text in the Caption as a Read or Write String
• Customization of the ActiveWindow.Caption can remain during the session and does not interfere with operation of the WorkBook
• To write a custom message to the Caption use:

  ActiveWindow.Caption = "XLF Services :: " & ActiveWorkbook.Name
      

• Including the ActiveWorkbook.Name property preserves visibility of the file Saved information
• To return control of the Caption to Excel:

  ActiveWindow.Caption = ActiveWorkbook.Name
      

• Here is an animated example with time adjustment for Pacific Time:

  ActiveWindow.Caption = "xlf OCO :: " & Now() - 17 / 24 & " PT " & ActiveWorkbook.Name
      

  Note. this code version will not update the clock in real time

Fig 3: Custom Caption - with business name and Pacific Time clock

• Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Time (AET)]