Status Bar

Location: http://www.mvps.org/dmcritchie/excel/statusbar.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

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)

The Status bar is at the bottom of the Excel spreadsheet and provides useful information from the Excel software or issued from a running macro.  If missing it can be restored on Tools --> View.  The status bar provides a convenient method of summing a range of cells (SUM not included for a single cell).  SUM will not be shown unless at least one of the cells is numeric and at least a second cell has content (not ISBLANK).  Some of the other indications are for keys depressed.  ¤ If you maintain a checkbook in Excel you can add up deposits for a deposit slip by selecting the range and looking at the SUM= on the status bar.

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
Some statusbar progress reporting tools:

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)


  Actual Values:
   411 Calculated SUM, SUM=411 on Status Bar
   10 Numeric count, Count Nums=10 on Status Bar
   11 Nonblank count, Count=11 on Status Bar
   1 text count
 
  Status Bar Reporting (depends on choice):
   Average=41.1
   Count=11
   Count Nums=10
   Max=90
   Min=6
   Sum=411
  Status Bar reporting reflects visible cells when filtered.
[OK]
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)) _
 ABC
 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)

Keyboard Problems (#keyboardproblems)

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

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)


Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page had been part of formula.htm from at least Oct 15, 2000 through March 15, 2004. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

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


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