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.

Menus grayed out (#menus)

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:

message "the document is already open"
message you’re getting is “cannot find xxx.xls or one of it’s components...”
Learning TeamFlow, If the server application does not respond to the DDE commands, make sure it has its DDE facility enabled, and ...
Open a word document in a hyperlink, and Excel is closed.
When I double-click on an Excel file, it opens Excel, but it doesn't open the file.
XL97: Excel Opens Without Displaying Workbook,
message: Cannot find the file 'path' (or one of its components). Make sure the path and filename are correct and that all required libraries are available. -- and another instance of Excel is open.
different problem & solution discussed in KB 158996: You previously used the Get External Data command on the Data menu to retrieve data from an external database.
Google Search: excel "ignore other applications"

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:

Rows to Repeat at top in File, Page Setup, Sheets: ...

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)

Tool Options, View, Horizontal and Vertical options.

Sheet Tabs are Missing (#sheettabs)

Tool Options, View, Sheet Tabs

Title Bar is missing (#titlebar)

Check the following:

Full Screen view, (view menue: Normal or Full Screen)

Values instead of Formulas from Copy (#value)

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

Copy (Ctrl+C) of locked cells on a protected sheet results in copying Values instead of formulas.

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)

Gray page numbers in Page Break Preview, use View, Normal to return to normal view.

Backup your files, always take backups
Commands and features are missing from Microsoft Excel « , Self Help topic (F1) with that title, (search: formula view)
Excel Window, Workbook Window, Worksheet Window, Fullscreen mode, min/max buttons, titlebar, zoom level.
Protection, Worksheet
missing a visible feature (many are already mentioned on this page) [also includes some off topic references to missing]
- fill handle missing
- colors lost due to accessibility
- gridlines covered on gridlines and borders page.
- scrollbars missing
- missing commas in .csv file
- missing webpage links, some comments about,
- menu items missing (look again this page) at menus and at personalized
- titlebar for window is missing, also this page, and toolbars page
- statusbar is missing at bottom
- toolbars missing and of course on this page (above).
- menu lost
Toolbars, Custom Buttons and Menus, and if missing toolbars see special subtopic Emergency as you may be able to restore toolbars if you have not closed Excel. (missing is different than grayed out).
Trouble Shooting and Startup Problems.
Things that can prevent response, keying in data

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