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 message like||SUM=3.44||EXT||CAPS||NUM||SCRL||END||FIX|
Status from Excel or Macro
statusbar to change)
Max / Min / Sum
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
Status Messages: (see above)
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)
Count [WS Functions COUNTA], counts the number of cells in a range that contain data. (does not include ISBLANK cells)
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 ExcelSome statusbar progress reporting tools:
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.
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
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):
Status Bar reporting reflects visible cells when filtered.
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)) _
& 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.
A B C 2 17 90 b 3 23 63 4 46 32 5 41 6 6 51 42
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.
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
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?
The usual problems are one of these:
* Scroll Lock is ONExcel 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.
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 SelectionYou 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.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2007, F. David McRitchie, All Rights Reserved