Grayed Out Options

Location: http://www.mvps.org/dmcritchie/excel/grayedout.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
Additional keywords: grayed out, greyed out, unavailable options

AutoSum is grayed out   (#autosum)

If the sum option has been programatically disabled, you can reset it by running a macro with this line of code: Jim Rech 2002-05-30.
    CommandBars("AutoCalculate").Reset

See what options are available under Tools, Protection (#quickcheck)

A check of Tools, Protection, for Unprotect options can quickly tell you what protection you might have inadvertently turned on.
Look at all of the possibilites even if not listed directly under the problem:

Menus at top grayed out (View, Insert, Format, Tools, Data)..., or
Right-Click menus are grayed out (Insert..., Delete..., Format Cells..., Pick From List, Hyperlink...)

Probably have sheet protection turned on which will turn off several options

Make sure you are not in Edit mode by hitting Esc (Escape key), as edit mode will gray out a lot of menu options -- notably most Edit options (except cut & paste).  Will grayout Options under Toolbar; many options under Data and under Window. 

Tools -> Protection -> Unprotect Worksheet

Insert menu grayed out, can't insert worksheet.

Tools -> Protection -> Unprotect Workbook

Single item in Insert Menu grayed out:   Probably invalid, like inserting a pagebreak can insert only before a cell, a column, or a row -- not before each cell in column for instance.

Conditional Formatting is grayed out.

Shared workbook (Highlight changes is automatic shared workbook) will gray out Conditional Formatting menu option.  To remove the shared status:  Tools, Share Workbook..., remove checkmark from allow multiple users.

All menu and context menu options are grayed out, except for those with submenus, but the submenus are found to be also grayed out.

If you can't use the keyboard, and the scroll bars are missing, you are in data entry mode.  Select another worksheet then return to curent worksheet. 
Format, cells menu tabs reduced to only the Font tab instead of Number, Alignment, Font, Border, Patterns, and Protection tabs.
You are in data entry mode, in the process of changing a cell value or formula -- see previous item.

Toolbars or other things controlled in Tools, Options (#missing)

Toolbars, Options, View (tab):
  show: Formula Bar, Status Bar, Windows in Taskbar
  Comments: None, Comment indicator only, Comment & indicator
  Objects: Show all, Show placeholders, Hide all
  Window Options: Page breaks, formulas, Gridlines
    Row & Column headers, Outline symbols, Zero values,
    Horizontal Scroll bars, Vertical Scroll bars, Sheet tabs

Toolbars, Options, General (tab):
  R1C1 Reference style -- control whether you see number or letters for column headers.

If the menubar is missing, in VBA code, or via the intermediate window use: Application.CommandBars(1).Reset
Also see Loss of an entire section on the toolbars (#missing), additional items that can be lost or a macro terminated and failed to reactivate something, can be found in the reset_things macro at http://www.mvps.org/dmcritchie/code/proper.txt

Menus Items Missing or Delayed Appearance  (#personalized)

The intent was to keep the actively used menu items at the top.  The consequences are that lesser used items disappear, and the order keeps changing.  To get things back to a reasonable footing:

Excel

Tools, Customize, Options tab, then uncheck both:
  Menus show recently used commands first
    Show full menus after a short delay

Windows: (called Personalized Menus)

Windows Start button, Settings, Taskbar & Start Menu, General (tab)
    uncheck Use Personalized Menus

Ignore other Applications (#ignore)

Tools, Options, General, uncheck 'Ignore other applications' fixes:

Paste Options are grayed out   (#paste)

The following macro will make paste options unavailable.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = 0  'Turn off previous use
    If Cells(1, 1) = "." Then Exit Sub
    Target.EntireRow.Interior.ColorIndex = 19
    Target.EntireRow(15).Interior.ColorIndex = 38
    'CommandBars("AutoCalculate").Reset   -- doesn't fix problem
End Sub

File Close, File Minimise button upper right grayed out   (#close)

Novell GroupWise may be installed.  The Close button is dimmed in Excel after GroupWise is installed
  260199 - XL2000: Close Button Is Dimmed After Groupwise Is Installed
  828484 The Close button appears dimmed and is unavailable in Excel 2003

Page Setup items disabled by Grouped Sheets or Print Preview (#setup)

The following Page Setup items are disabled by Grouped Sheets or Print Preview:

Purposely Disabling Features in Excel, Use Restraint   (#restraint)

Please use restrait in disabling Excel feaures.  Very few experienced users will tolerate someone changing their screen resolution, color depth, removal of scroll-bars, formula bars, status bar, or other things.  Others may be very unhappy when they find out that a feature they could have used was disabled so they weren't even aware of some features.  If you would not want someone messing with your options, then they probably will not like you messing with their options either.

Also along these lines do not ship your toolsbars file to someone else and expect them to replace their personal toolbar with your, and don't expect it to work either. (also see Backup and Toolbars)

Recently Used Files is grayed out   (#history)

OFFXP: "Most Recently Used" List Missing from File Menu and "Recently Used File List" Option Unavailable (Q284896)

Scroll bar is Missing (#scrollbar)

Sheet Tabs are Missing (#sheettabs)

Title Bar is missing (#titlebar)

Check the following:

Values instead of Formulas from Copy (#value)

Not a grayed out option, but since there are some protection issues here elsewhere.

Safe Mode (#safemode)

You will see a "Safe Mode" in the banner, and will have (temporarily) lost your customizations to menus, but everything looks intact, your own customizations return once you restart Excel in the normal manner.

Pending updates (#pending)

Have no information of what one sees or does not see without product activation, or if using the Office Reader which is only for viewing.

Microsoft Product Activation (#activation)

If nothing works in Excel, with menus grayed out, check to make sure that you have legal version of Excel that was properly activated.

Other gray things   (#gray)


This page was introduced on May 30, 2002. 

[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 - 2006,  F. David McRitchie,  All Rights Reserved