Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Unlocking specific cells in an Xcel locked worksheet.

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
AcDcRed
Asked:
AcDcRed
  • 4
  • 3
1 Solution
 
redmondbCommented:
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
 
Patrick MatthewsCommented:
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
 
AcDcRedAuthor Commented:
Both solutions basically were the same only worked differently.  They were sufficient to allow me to accomplish my end.

Thanks,

AcDcRed
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
redmondbCommented:
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
 
AcDcRedAuthor Commented:
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
 
redmondbCommented:
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
 
AcDcRedAuthor Commented:
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
 
redmondbCommented:
All the best, AcDcRed!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now