Solved

Cells unlocked then locked when worksheet activated

Posted on 2013-12-02
5
283 Views
Last Modified: 2013-12-02
Folks,
In the attached workbook are two worksheets:
MonthlyTodayFindBirthdays
Sheet2
In the worksheet labeled "MonthlyTodayFindBirthdays" range L3:M14 and cell O2 are not locked.
When I protect the worksheet "MonthlyTodayFindBirthdays" I protect the worksheet as follows:
"Checked Select unlocked cells"
"Checked Format cells"
and then password protect the worksheet (password is 123memphis)
After protecting the worksheet I select the cells protected and unprotected and everything works fine. I have not left the worksheet.
However, when I select "sheet2" and return and activate "MonthlyTodayFindBirthdays" cells L3:M14 and O2 are now locked.
In looking at the Worksheeet.Activate I need to do some housekeeping in the "Practice" section. Maybe that's what's causing my cells to go from unlocked to locked?
Locked-cells.xlsm
0
Comment
Question by:Frank Freese
  • 3
  • 2
5 Comments
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 500 total points
ID: 39691188
fh_freese,

What cause your sheet to relock itself is

Worksheets("MonthTodayFindBirthdays").Range("L3:M14").Clear

Open in new window


Clear will clear all the formats, including the toggle of Locked and Unlocked.

Is there any reason for you to clear the cells, then set the format up again?

If not, why don't you use
Worksheets("MonthTodayFindBirthdays").Range("L3:M14").ClearContents

Open in new window

so that the content is removed but the format remains?
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39691234
If you really need to clear the cells then reestablish all the formats and borders, add the following to the end of your code.

Worksheets("MonthTodayFindBirthdays").Unprotect password:="123memphis"
Worksheets("MonthTodayFindBirthdays").Range("L3:M14").Locked = False
Worksheets("MonthTodayFindBirthdays").Protect password:="123memphis"

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 39691257
Harry,
The reason for clearing the data in the range where the user can practice is that if I don't and they return to the Main Menu, the information they were working on is still there. When they return to the worksheet I want the worksheet to be "clean" - does this make sense to you?
I see what you are doing and the same thing crossed my mind. I just did not know it was possible.
I'll play around with it tonight after I get home and get back with you.
Thanks for the explanation - I did not know that about Clear but it makes sense now.
Frank
0
 

Author Closing Comment

by:Frank Freese
ID: 39691412
thanks...I "think" I've got it, but not sure yet how to implement your suggestion. I'll repost if I need more help
Appreciate it!
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39691442
Anytime.

I'm glad I can help!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2013 Issues 11 45
Web Query 1 20
MS Excel Multi Sheet Formula 13 32
SKip past fields with no data 6 14
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 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