Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Cells unlocked then locked when worksheet activated
Posted on 2013-12-02
Folks,
In the attached workbook are two worksheets:
MonthlyTodayFindBirthdays
Sheet2
In the worksheet labeled "MonthlyTodayFindBirthdays
When I protect the worksheet "MonthlyTodayFindBirthdays
"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
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
In the attached workbook are two worksheets:
MonthlyTodayFindBirthdays
Sheet2
In the worksheet labeled "MonthlyTodayFindBirthdays
When I protect the worksheet "MonthlyTodayFindBirthdays
"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
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
[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
3-
2
5 Comments
fh_freese,
What cause your sheet to relock itself is
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
What cause your sheet to relock itself is
Worksheets("MonthTodayFindBirthdays").Range("L3:M14").Clear
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
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"
Active 6 days ago
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
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
Active 6 days ago
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!
Appreciate it!
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 6 hours left to enroll
622 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.