How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Mike B Your Own Question

Mike B
Mike B, Information Analyst
Category: Computer
Satisfied Customers: 280
Experience:  15+ years experience with computers, programming, tech support
Type Your Computer Question Here...
Mike B is online now
A new question is answered every 9 seconds

How can I protect a cell in Excel from being changed but still

Resolved Question:

How can I protect a cell in Excel from being changed but still allow it to selected for a copy (of the value) & paste
Submitted: 5 years ago.
Category: Computer
Expert:  Mike B replied 5 years ago.
Customer

You can enable sheet protection and any cell that has the Locked attribute set, will not be editable, but can still be copied.

Please do the following:
  1. Highlight all the cells on your worksheet that you're interested in.
  2. Right-click and select Format Cells
  3. Select the Protection tab
  4. Un-check the "Locked" option (this will enable editing for all cells)
  5. Click OK
  6. Now, select the cell that you DO NOT want edited
  7. Right-click and select Format Cells
  8. Select the Protection tab
  9. Check the "Locked" option (this will lock the cell from being edited)

Now, we need to protect the sheet:
  1. Click the Tools menu
  2. Select Protection
  3. Select Protect Sheet
  4. Type in a password XXXXX Unprotect the sheet.
  5. Click OK
  6. Confirm Password

Please let me know if you have any questions.



Mike B, Information Analyst
Category: Computer
Satisfied Customers: 280
Experience: 15+ years experience with computers, programming, tech support
Mike B and 6 other Computer Specialists are ready to help you
Customer: replied 5 years ago.
I don't understand. It looks like you're saying unlock the cells (to allow editing) then lock the cells (to protect from editing). What am I missing?
Expert:  Mike B replied 5 years ago.
Exactly... I wasn't trying to confuse you.

Excel, be default locks ALL cells of a spreadsheet. Your initial query was to "protect a cell in Excel"... from this I gathered that you were looking to protect ONE single cell.

That is why I said, you should unlock all the cells, then lock only the one cell that you would like to protect.

Does that make sense?

Customer: replied 5 years ago.

Yes, but it's not doing what I need. But I may not have described my situation sufficiently. Give me a few minutes to elaborate.
Customer: replied 5 years ago.

Mike, I have a workbook with multiple sheets. It was created by somebody else who also created an add-in that, among other things, appears to automatically protect all sheets when the workbook opens.

I want to be able to copy data from some of the sheets and paste to another application but with the cells locked and the sheets protected I’m not able to select the data I need. So I created a new sheet and populated it with references (=OldSheet!E12) to the data I need. The plan was to copy from the new sheet. But I couldn’t select data from it either.

So I unprotected the new sheet and unlocked the cells I wanted to copy, then closed, saved, and reopened the workbook. I can now select from the new sheet, but – here’s the problem: I could also inadvertently edit the cells I was attempting to copy. I want to copy but not be able to edit.

Is that clearer?

Expert:  Mike B replied 5 years ago.
Customer

I don't understand why you can't copy from the protected sheet... When I protect a sheet in my Excel file, it does not prevent me from copying any of the data to another sheet. Maybe there is something else going on with your file? Can you share the file with me? If so, just upload it to http://wikisend.com ... Wikisend will give you a Download Link, which you have to copy/paste into a reply here, so that I can download your file.

Thanks,
Mike B.
Customer: replied 5 years ago.
I can send you the file but not the add-in that it uses or the database that the file reads when it opens. Should I send it?
Expert:  Mike B replied 5 years ago.
No... do not send it.

The new workbook that you have with the unprotected cells... What happens if you select all the cells, make sure the "Locked" option is checked on, the protect the sheet?
Are you able to select and copy the cells?

Let me know.

Thanks,
Mike B.
Customer: replied 5 years ago.
It's a new worksheet in the existing workbook.

But if I Unprotect the sheet (so I can select all cells) then turn on "Locked" for all cells, then protect the sheet - I'm right back where I started: can't select Locked cells on a protected sheet.
Expert:  Mike B replied 5 years ago.
Can you try this in a completely new workbook?

Type some stuff into a few cells.
Protect the whole sheet.

Can you select and/or copy the cells?

Please let me know.

Thanks,
Mike B.
Expert:  Mike B replied 5 years ago.
Customer

I think I found it! It's a setting in the Protect Sheet dialog box...

When you protect a sheet, you can make it selectable or not... Please see the screenshot below:

graphic

When those checkboxes are unchecked, you will not be able to select or copy any of the cells that are locked or unlocked.

Please double-check this setting in your workbook when you go to protect the sheet.

Thanks,
Mike B.
Customer: replied 5 years ago.
OK -
New workbook. Added data to some cells. Cells Locked by default. Protected worksheet.
I can Select and Edit cells that have the data.

Regarding Protect Sheet dialog box - You're right - if I uncheck those boxes I can't select or copy them - but I need to be able to select them so I can copy them.i
Expert:  Mike B replied 5 years ago.
Customer

What I was trying to say was, that you need to make sure that those checkboxes in the Protect Sheet dialog box are checked ON, this way you can still protect the sheet from being edited, AND you'll be able to select and copy the cells.

Thanks,
Mike B.
Customer: replied 5 years ago.
Mike - sorry this is taking so much time and apologize if I'm not describing the issue correctly,

but

Those boxes are checked ON, the sheet is Protected, the cells are Unlocked, and I can Select and Copy and, unfortunatly, Edit
Expert:  Mike B replied 5 years ago.
Ok, it's not a problem... MS just happened to make this protect/unprotect/lock/unlocked business a bit confusing...

Please make sure the cells are LOCKED, not UNlocked... this will prevent editing.

Thanks,
Mike B.
Customer: replied 5 years ago.
Sorry - I said the cells are Unlocked
I should have said:
"Those boxes are checked ON, the sheet is Protected, the cells are Locked, and I can Select and Copy and, unfortunately, Edit"
Expert:  Mike B replied 5 years ago.
Ok... now that is weird...

Let's try this:
On the sheet that you would like to have protected...
Press CTRL + A (this will just highlight all the cells on the sheet)
Right-click anywhere on any of the cells
Select Format Cells...
Click on the Protection tab
Does it look like this?
graphic

Or like this:
graphic

Please let me know.

Thanks,
Mike B.
Customer: replied 5 years ago.
The 2nd. Locked is checked
Expert:  Mike B replied 5 years ago.

That's a good one...

Can you download this file and see what happens when you try to edit the contents of the cells?

http://wikisend.com/download/969164/Book1.xls

Customer: replied 5 years ago.
I can Select (and Copy) but not Edit. Just what I'm looking for.

I'm using Excel 2002, SP3
Expert:  Mike B replied 5 years ago.
Well, at least that's something...

I wonder if it has something to do with your version of Excel... Can you send me your file (even though it's going to have the broXXXXX XXXXXnks and no add-in)... I will see if I can protect the sheet for you and see if that works?

Just upload your file to Wikisend, like I mentioned earlier.

Thanks,
Mike B.
Customer: replied 5 years ago.
http://wikisend.com/download/499174/DR_Reconciliation.xls (Wikisend is very cool)

The new sheet is "Copy Sheet"

But Mike, this is all above and beyond what you thought you were getting into. Feel free to bail out with my thanks. XXXXX'll give you an Excellent rating.

And I have to leave now for a couple of hours.
Expert:  Mike B replied 5 years ago.
No worries... I like the challenge!

Here is your locked file: http://wikisend.com/download/595988/DR_Reconciliation.xls

It wasn't protected...

Thanks,
Mike B.
Customer: replied 5 years ago.
it wasn't protected because the add-in wasn't available.

I'm not sure what to expect with the file you returned. I open it I can't select from "Copy Sheet".

But I really have to go. I assume justanswe will have timedout when I get back. You can reach me atXXX@XXXXXX.XXX

Thanks
Expert:  Mike B replied 5 years ago.
Customer

Just bookmark this page: http://www.justanswer.com/questions/28o5b-protect-cell-excel-changed

You can come back here any time, and we can continue...

Thanks,
Mike B.
Expert:  Mike B replied 5 years ago.
Customer

As it appears, the add-in is unprotecting the cells in your file. The "Book1.xls" file I sent you worked as expected, but yours didn't... I did the same procedure on both files, the only difference was that your file has the add-in.

I would talk to the programmer that wrote the add-in and see if they can modify to suit your needs.

Mike B, Information Analyst
Category: Computer
Satisfied Customers: 280
Experience: 15+ years experience with computers, programming, tech support
Mike B and 6 other Computer Specialists are ready to help you
Customer: replied 5 years ago.
That sounds like a good idea - thanks for your help

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you! RP Austin, TX
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
 
 
 

Meet The Experts:

 
 
 
  • Andy

    Computer Consultant

    Satisfied Customers:

    5311
    11yr exp, Comp Engg, Internet expert, Web developer, SEO
< Last | Next >
  • http://ww2.justanswer.com/uploads/EN/Engineer1010/2012-6-9_132423_jaj12a.64x64.jpg Andy's Avatar

    Andy

    Computer Consultant

    Satisfied Customers:

    5311
    11yr exp, Comp Engg, Internet expert, Web developer, SEO
  • http://ww2.justanswer.com/uploads/BA/barrenrock/2011-10-19_215925_JamesJAFinal.64x64.jpg James's Avatar

    James

    Sr. Computer Support Expert

    Satisfied Customers:

    8376
    20 years of experience building, fixing and servicing PCs and operating systems.
  • http://ww2.justanswer.com/uploads/zeyank/2009-09-26_154244_P8110079.png Ryan H.'s Avatar

    Ryan H.

    Computer Support Specialist

    Satisfied Customers:

    1741
    A+ Certified Technician - 10 Years experience working with all types of computer systems.
  • http://ww2.justanswer.com/uploads/JA/jadedangel57/2011-11-8_193134_janenewsm.64x64.jpg Jane Lefler's Avatar

    Jane Lefler

    Sr Prog Analyst / Technician

    Satisfied Customers:

    0
    Computer Programmer / Technician/ Consultant 16+ years
  • http://ww2.justanswer.com/uploads/RO/robmpreston/2013-9-23_233814_mijiFZm.64x64.jpg RPI Solutions's Avatar

    RPI Solutions

    Support Specialist

    Satisfied Customers:

    3476
    5+ Years in IT, BS in Computer Science
  • http://ww2.justanswer.com/uploads/BA/barunrath/2012-7-5_201954_Profilepic2.64x64.jpg B. Rath's Avatar

    B. Rath

    Computer Support Specialist

    Satisfied Customers:

    8671
    Certified Computer/Networking Support Specialist.
  • http://ww2.justanswer.com/uploads/FS/fszcze/2012-6-18_181848_500test.64x64.jpg Frederick S.'s Avatar

    Frederick S.

    Computer Specialist

    Satisfied Customers:

    7240
    Computer technician and founder of a home PC repair company.