Solved

Certain Excel commands have been greyed out

Posted on 2015-01-11
14
1,999 Views
Last Modified: 2015-01-18
I inherited several excel documents that I need to work on.  While I am able to modify cells, I am unable to insert and delete rows, format cells, change height, column width, etc. etc.

I am using an administrative account, and the file / directory ownership includes Everyone with Full Access.

Finally, the document itself appears to be shared (it says it in the title bar).  I tried unsharing the workbook as well as un-grouping the sheets, but neither worked.
0
Comment
Question by:rdege
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 95

Expert Comment

by:John Hurst
ID: 40543506
For a problem worksheet, are you able to open it, copy it (formulas and data) into a completely new workbook, save it as a new name, does the result work?
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points
ID: 40543517
Sounds like it's been protected. Go to View > Unprotect Sheet and/or Unprotect Workbook. You may need a password.
0
 
LVL 1

Author Comment

by:rdege
ID: 40543532
@John Hurst - I opened a workbook with 4 sheets, and created a new Sheet2.  Copying the data from Sheet1 -> Sheet2 in the same workbook works.  However, I am unable to delete the Sheet2.

@Wayne Taylor - The sheet does not appear to be protected.  I clicked the Review tab.  The "Protect Sheet" is greyed out, the "Protect Workbook" is accessible as well as the "Share Workbook".  I am unable to locate an Unprotect sheet or Unprotect Workbook.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 95

Expert Comment

by:John Hurst
ID: 40543563
Try copying the information from just one sheet to a brand new workbook..   For the one sheet, does it work properly?
0
 
LVL 1

Author Comment

by:rdege
ID: 40543601
@John Hurst - Copying the data into a new workbook appears to resolve the issue.  However, I have about 50 excel documents, each containing multiple sheets.  On top of that, the documents are shared, which means copying the data to a new workbook would lose all of the tracked changes.  Is there a way to remove the restrictions in the current file?  If the document was restricted or protected using a copy of Office 2010, is it still possible to rectify it using a copy of Office 2007?
0
 
LVL 35

Assisted Solution

by:Seth Simmons
Seth Simmons earned 250 total points
ID: 40543628
that was going to be my question...are the documents shared
this causes certain functions to not be available; found out the hard way before

Features that are unavailable in shared workbooks
http://office.microsoft.com/en-gb/excel-help/features-that-are-unavailable-in-shared-workbooks-HP005201080.aspx
0
 
LVL 1

Author Comment

by:rdege
ID: 40543654
@Seth - Even when I unshared the workbook, the greyed out function did not return.  Even the [Shared] in the title bar was gone.  Additionally, according to the URL that you included, adding/removing rows and columns should still be available whether the document is shared or not.

*Update* - It looks as though the Sheets are protected.  When I click on the Review tab, there is a button called "Unprotect Sheet".  However, that button is greyed out, and I am unable to remove the protection.  When I click on Protect Workbook, a drop down menu appears.  Under Restrict Editing, Protect Structure and Windows is greyed out.  Under Restrict Permission, there's a check mark next to "Unrestricted Access"

So it looks like the Workbook is also partially protected, but I don't know how to remove those features.
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 40544085
If you cannot unshare or otherwise fix these worksheets, you are probably going to have to do it manually.

How could 50 sheets be secured without you knowing?  Does not someone else know what happened?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40544160
The Shared Workbook feature is renowned for causing problems, most threads on EE recommend not using it because of the issues it causes.

Sounds like the files may have become corrupt by the Sharing feature, even if you have switched it off.

Thanks
Rob H
0
 
LVL 1

Author Comment

by:rdege
ID: 40546968
I was able to resolve the problem.  Here's what I did:

Open the document in Excel

Click the Review Tab

If the "Unprotect Sheet" exists but is greyed out
      Click the Share Workbook button
      Uncheck "Allow changes by more than one user at a time" && click OK
      The "Unprotect Sheet" button should no longer be greyed out

Click the "Unprotect Sheet"
Enter the password and click OK
The "Unprotect Sheet" button should now change to "Protect Sheet"

If the "Unprotect Workbook" exists, Click the "Unprotect Workbook"
Enter the password and click OK
The "Unprotect Workbook" button should now change to "Protect Workbook"

Lastly, Click the Share Workbook button again
Check "Allow changes by more than one user at a time"
Click OK

Close the document
0
 
LVL 1

Author Comment

by:rdege
ID: 40547033
I've requested that this question be closed as follows:

Accepted answer: 500 points for Wayne Taylor's comment #a40543517
Assisted answer: 0 points for rdege's comment #a40546968

for the following reason:

As stated above, sharing the document disables certain functionality.  This appears to include adding/removing password protection.

In order to remove sheet protect, you first need to un-share the document, which in turn restores this functionality.  Then you can remove the password protection, and re-share the document
0
 
LVL 35

Expert Comment

by:Seth Simmons
ID: 40547034
I asked you before if the document was shared because some functionality was not available which you discovered when you couldn't remove protection.  I gave you half the answer (sharing), Wayne gave the other half (protected sheet).
0
 
LVL 35

Expert Comment

by:Seth Simmons
ID: 40551195
points should be split evenly between wayne and myself considering both our comments contributed to the author's conclusion
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question