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
Solved

Unlocking specific cells in an Xcel locked worksheet.

Posted on 2013-01-22
8
358 Views
Last Modified: 2013-02-24
I write VB Xcel programs for local organizations - the purpose is to create summary reports from database worksheets.  Saves the organization mucho time.

I also need to have the summary report worksheet locked so the organization workers do not change the structure of the summary report which would display data in the wrong cells.

My problem is that I also need to have some of the cells in the locked summary report worksheet not locked.  The purpose is to have the user select the worksheet used by VB, ie,  current year or previous year database worksheet for example.

When entering VB sub I unlock the worksheet with:  ActiveSheet.Unprotect

And when exiting the sub: ActiveSheet.Protect.

Need to keep the user needed data entry cells unlocked.

Any help would greatly appreciated.

AcDcRed
0
Comment
Question by:AcDcRed
  • 4
  • 3
8 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38807333
Hi, AcDcRed.

In the attached, Sheet1 is locked, but the user can change the yellow cells as their Locked gflag has been set off.

To unlock a cell, right click, select "Format cells...", select the "Protection" Tab, untick "Locked".

(The flag only has effect when the the sheet itself is protected.)

Regards,
Brian.Locked.xls
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 100 total points
ID: 38807372
And from VBA, while the worksheet is unprotected, set the appropriate value (True or False) for the Locked property for the range objects representing your data entry cells.
0
 

Author Closing Comment

by:AcDcRed
ID: 38807667
Both solutions basically were the same only worked differently.  They were sufficient to allow me to accomplish my end.

Thanks,

AcDcRed
0
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.

 
LVL 26

Expert Comment

by:redmondb
ID: 38807808
AcDcRed,

I understand that this was not an important matter for you, but it would be a good learning for us to know why the answers were deficient.

Thanks,
Brian.
0
 

Author Comment

by:AcDcRed
ID: 38807877
Brian,

The answers were not deficient only worded (not worked) different.  One term that I did not know was "gflag".  But your explanation following the use of that term was what I needed;

Format Cells / Protection tab / Locked

Regards,

AcDcRed

.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38808020
Thanks, AcDcRed.

I've just noticed that you're a new member - a belated welcome!

While the points allocated to a Question are simply a measure of its importance to you, the Grade is more objective.

Unless there's some shortfall in the answer, Grade A is the norm. On the other hand, if you feel that a lower Grade is appropriate then please give a brief explanation to help improve future answers.

(BTW, "gflag" sounds fascinating but it's simply a typo for "flag"!)

Regards,
Brian.
0
 

Author Comment

by:AcDcRed
ID: 38808057
Brian,

Thanks for the advice - will keep it in mind when I enter another question.

Thanks also for making an new menber welcome.

Regards,

AcDcRed
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38808099
All the best, AcDcRed!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

860 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