Status Bar

Parts of Screen (#screen)

Parts of the Screen:  Title Bar (Excel Window), Menu Bar, Tool Bars, name box, Formula Bar, Document Title Bar (Workbook Window), Scroll Bars, Scroll Box (slider), Status Bar (moved), workbook tabs;
Cells, Comment boxes, Text boxes, shapes (objects), Combo box; Dropdowns: autofilter, validation

Tools, Options, View: Formula bar, Status bar, Comments and Indicator, objects, Page Breaks, Formulas, Gridlines, Row & column headers, Outline symbols, Zero values, Horizontal scroll bar, Vertical scroll bar, Sheet tabs.

Status Bar     (#statusbar)

status message like		SUM=3.44	EXT	CAPS	NUM	SCRL	END	FIX
Ready Calculate Calculating % Circular Found A22 Status from Excel or Macro		(RClick anywhere on     statusbar to change)   Average   Count   Count Num   Max / Min / Sum	ADD				OVR
Calculate is an indication that you have turned Calculation off in Tools, options, Calculation (Tab), [X] Manual -- and that you have something that needs to be calculated.  (also see MS KB 243495) Status Messages:  (see above) Auto Calculate:  (see above) Web Query: , Web Query Nodes: Extended mode (F8).  Click on a "start" cell (Anchor), press F8, now click on another cell and this will highlight *all* cells between the 'anchor' cell and the one selected.  To remove indication hit F8 once more.  Use of Shift+F8 shows ADD and allows you to keep adding more ranges until turn off with Shift+F8 (test with MarkSepAreas).  See ghosting.txt for a problem that mimics this behavior. Caps Lock, Num Lock, OVR, Scroll Lock, and End are keys on the keyboard.  Caps (Lock), Num Lk, and Scroll Lk also have keyboard lights.  Microsoft Office Assistance: Turn on or off Overtype (OVR) mode.  For more information on use of End in shortcuts see  http://www.mvps.org/dmcritchie/excel/shortx2k.htm SCRL, Scroll Lock Key.  Press this and the arrow keys will scroll the worksheet, rather than moving the selection down the page.  Engaging the Scroll Lock Key is a frequently reported problem. OVR, overtype, When you are editing text in Overtype mode, you are typing new characters over the existing characters.  Double-click a cell or press F2 to turn Overtype mode on or off. Also, the Edit directly in cell check box must be selected (it is selected by default). To select or clear this check box, on the Tools menu, click Options, and then click the Edit tab.  Actually I find it comes on and off with the Insert key when you are on the menu bar and it is VERY annoying to have found it on and to have wiped out characters. FIX, fixed decimal, an option to enter with a fixed number of decimals if no decimal point is keyed in.  This is an option that would mess most people up.  Tools --> Options --> Edit --> Fixed decimals (number) It is useful for accounting types who like to key in 500 instead of 5.00 -- can be overridden by actually typing a decimal point   1.  10.  1.001  Count  [WS Functions COUNTA], counts the number of cells in a range that contain data.  (does not include ISBLANK cells) Count Num [WS Function COUNT], counts the number of cells in a range that contain numbers. Statusbar Count above corrected 2003-05-10, for more on the Worksheet Functions see John Walkenbach's COUNT, Tip 52: Cell Counting Techniques Some of the above status bar codes have been described more precisely at Better Solutions on their Status Bar page. (and I thought no one else had such a page).  There are more now:  Jan's Excel Intro: Web Query

Auto Filter: 3 of 120 records selected, but you may just see "filter mode".  213886 - XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode", or another workaround

Reporting progress on Status Bar, may delay things a bit.

  Application.StatusBar = 5*INT(20*n/ncnt)&"%"
  Application.StatusBar = False      'return control to Excel

• Excel Files: For Developers, see "Control the LED Display in the StatusBar", by David Wiseman,additional help from Ole Erlandsen, and Jim Rech.
• Progress Indictor, Chip Pearson
• Excel Progress Bar (Enhanced Datasystems), Robin Hammond.  Provides a modeless user form showing progress bar (with left to right shading red to blue), title and three captions.  Run the ProgBarDemo example from the macro toolbar.  (class module and Form)

Intermediate Window
The following can be entered in VBA coding or from the intermediate window.  to get there Open the VBE (Alt+F11) and if there is not an immediate window showing, go to View>Immediate window. (Ctrl+G). You can paste the lines below there to actually run them.

If formula bar is no longer being calculated, Jim Rech said this might fix
  CommandBars("AutoCalculate").Enabled = True

If the formula bar is no longer showing Ready, etc., Jim Rech said the following will turn control of the Status Bar back to Excel.  This can be entered from the intermediate window as well as the other code above. 
  Application.StatusBar=False

Macro to Toggle Fixed Decimal Places edit option   (#FixDP)

The status bar contains a FIX indicator to tell you if fixed decimal places is currently active or not.  The following macro can be used to toggle on or off.  To change the number of places you would have to go to Tools, Options, Edit, ...

Sub ToggleFixedDec()
  'David McRitchie, 2004-07-29, statusbar.htm (toolbars.htm)
  ' http://www.mvps.org/dmcritchie/excel/statusbar.htm
  Dim FDP As Long
  With Application
    If .FixedDecimal Then
      .FixedDecimal = False
      MsgBox "Fixed Decimal Turned OFF, by your command"
    Else
      .FixedDecimal = True
      MsgBox "Fixed Decimal Turned ON (Places=" _
        & .FixedDecimalPlaces & ")"
    End If
  End With
End Sub

Macro to simulate StatusBar Reporting (#simulation)

Sub Statusbar_simulation()
    ' http://www.mvps.org/dmcritchie/statusbar.htm 
    Dim str As String, strcnt As String, _
        strx As String, strcnta As String
    If Application.Count(Selection.SpecialCells _
         (xlCellTypeVisible)) >= 1 And _
         Application.CountA(Selection.SpecialCells _
         (xlCellTypeVisible)) > 1 Then
       str = "SUM=" & _
          Application.Sum(Selection.SpecialCells _
          (xlCellTypeVisible)) & "   on Status Bar"
       strcnt = "Count Nums=" & _
          Application.Count(Selection.SpecialCells _
          xlCellTypeVisible)) & "   on Status Bar"
       strcnta = "Count=" & _
          Application.CountA(Selection.SpecialCells _
          (xlCellTypeVisible)) & "   on Status Bar"
       strx = Chr(10) & "  Average=" & _
            Application.Sum(Selection.SpecialCells(xlCellTypeVisible)) _
            / Application.Count(Selection.SpecialCells(xlCellTypeVisible)) _
          & Chr(10) & "  Count=" & Application.CountA(Selection. _
            SpecialCells(xlCellTypeVisible)) _
          & Chr(10) & "  Count Nums=" & Application.Count(Selection. _
            SpecialCells(xlCellTypeVisible)) _


 
A
B
C
 2
 17 
 90 
 b
 3
 23 
 63 
 
 4
 
 46 
 32 
 5
 
 41 
 6 
 6
 
 51 
 42 

          & Chr(10) & "  Max=" & Application.Max(Selection. _
            SpecialCells(xlCellTypeVisible)) _
          & Chr(10) & "  Min=" & Application.Min(Selection. _
            SpecialCells(xlCellTypeVisible)) _
          & Chr(10) & "  Sum=" & Application.Sum(Selection. _
            SpecialCells(xlCellTypeVisible)) _
          & Chr(10) & _
             " Status Bar reporting reflects visible cells when filtered."
    Else
       str = "Nothing would be reported for SUM="
       strcnt = "Nothing would be reported for Count Nums="
       strx = "Nothing would be reported on Status Bar"
    End If

    MsgBox "Actual Values:" & Chr(10) _
      & Application.Sum(Selection.SpecialCells(xlCellTypeVisible)) _
        & " Calculated SUM" _
        & ",  " & str & Chr(10) _
      & Application.Count(Selection.SpecialCells(xlCellTypeVisible)) _
        & " Numeric count" _
        & ",  " & strcnt & Chr(10) _
      & Application.CountA(Selection.SpecialCells(xlCellTypeVisible)) _
        & " Nonblank count" _
        & ",  " & strcnta & Chr(10) _
      & Application.CountA(Selection.SpecialCells(xlCellTypeVisible)) _
        - Application.Count(Selection.SpecialCells(xlCellTypeVisible)) _
      & " text count" & Chr(10) & Chr(10) _
      & "Status Bar Reporting (depends on choice):" & strx
End Sub

Placing value shown on status bar into the clipboard.  A thread answered by PaulB & Lonnie M. indicated that I had to change the above code to reflect changed values (visible cells) if filtering is on.  Paul's code places SUM value into the clipboard.  More information:  clipboard, examples.  Note Status Bar will not show any values for a single cell.

Filtering comments on Status Bar  The status bar may show a count of filtered records "10 of 37 records found" or if there are many formulas it may simply show "Filter Mode", for some insight and workarounds see Debra Dalgleish's Excel Filters -- AutoFilter Tips, Count of Filtered Records in Status Bar.

Status Bar Message (#statusbaarmsg)

   Dim oStatusBar     'from a post by Bob Phillips, 2006-07-18 in excel.setup  
   oStatusBar = Application.DisplayStatusBar
   Application.DisplayStatusBar = True
   Application.StatusBar = "Please be patient..."
   MsgBox "Break to demonstrate"
   Application.StatusBar = False
   Application.DisplayStatusBar = oStatusBar

Miscellaneous Tips (#misc)

• The Status Bar (and Formula bar) can be hidden via the View Menu to provide more viewable space.  Status Bar is hidden in Full Screen mode, but you have the ability to control via View Menu as Normal and Full Screen mode have their own settings.

Keyboard Problems (#keyboardproblems)

Since there are several keyboard type of problems that show up on the status bar will include them here. 

• Keyboard Shortcuts in Excel
• Status Bar is covered on this page above and below.
• Transition Navigation Keys on this page above and below.

What do the keyboard shortcuts do instead working?
  http;//www.mvps.org/dmcritchie/excel/shortx2k.htm

The usual problems are one of these:

* Scroll Lock is ON

Excel 2007 indication at bottom of window at left, prior to Excel 2007 at bottom of window at the right. You also have the keyboard lights to tell if Scroll Lock and/or Caps Lock and/or Num Lock are on.

Suggest using an accessibility option to warn you if you accidentally hit any of those keys, you get a distinctive on/off tone.
  http;//www.mvps.org/dmcritchie/excel/statusbar.htm

On Vista: Control Panel, Ease of Access Center: [Shortcut WinKey+U, then choose keyboard]. Make the keyboard easier to use: [x] Turn on Toggle keys (sound for Caps/Num/Scroll lock)

On Windows Pre Vista, Control Panel, Accessibility Options Keyboard (tab), (check) "Use Toggle keys", [settings button] checkmark "Use shortcut"

* Transition Navigation keys invoked (Lotus 1-2-3 compatibility features)

Pre Excel 2007 is Tools, Options, Transition (tab), turn off transition keys.
Excel 2007, Office button (large button) in upper left corner, Options at the bottom of that popup window, Advanced (red/orange) button on left, On right scroll down to bottom, "Lotus Compatibility" on blue stripe, uncheck "Lotus navigation keys" to work in Excel mode. Then use [Ok] One of the big differences is that Home key takes you to home with navigation keys on; otherwise, you have to use Ctrl+Home but everything else works much better and you are in sync with other Excel users when asking for help.

* Use of F8 to Extend or Add to a Selection

You would see an indication of use in lower left corner using Excel 2007, or lower right corner on status bar pre Excel 2007
If you see "Ext" or "Add" on the Excel status bar or in Excel 2007 you see "Extend Selection" or "Add to Selection" hit F8 until those indications disappear.
  http://www.mvps.org/dmcritchie/excel/statusbar.htm

Microsoft MS KB articles -- usually problems (#mskb)

• XL: Calculate Message Remains in Status Bar If 65,536 Formula References (MSKB 243495),
  also see Status Bar Shows “CALCULATE” (decisionmodels.com)
• XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode" (MSKB 213886), or another workaround (contextures.com)

Related   (#related)

• Circular reference, David McRitchie, 2006-01-19
• Control the LED Display in the StatusBar, download at j-walk, concept David Wiseman, modifications Jim Rech.
• The Calculate message may remain in the status bar when you enter formulas that include cell references
• Microsoft Menus, Toolbars, Status bar in VBA Tips and Tricks from Excel Tip .com / Microsoft Excel Tips / All Tips
• Selection statistis on Status Bar, no need to limit yourself to just SUM or one of the other options.
• Show FORMULA or FORMAT of another cell
• Toolbars, Custom Buttons and Menus
• Microsoft Excel 12 : Quick detour – cool things on the status bar ..., blog by David Gainer, 2005-11-18, zoom control, ultiple status bar calculations, View switcher, and then at the end of the sheet tabs a button to add a worksheet.

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2007,  F. David McRitchie,  All Rights Reserved