Solved

Protect worksheet but NOT Conditional Formatting

Posted on 2013-12-04
22
236 Views
Last Modified: 2013-12-11
Folks,
I have a range from L3:N10 that I need to have unlocked. The rest of the worksheet needs to be locked. No problem, I've got that understood.
However, when I lock a worksheet Conditional Formatiing is not available. I would like to be able to not lock out Conditional Formatting.
0
Comment
Question by:Frank Freese
  • 11
  • 6
  • 3
22 Comments
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
post your worksheet here
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
When you protect the sheet check Format Cells/Columns/Rows in the Allow list.
0
 

Author Comment

by:Frank Freese
Comment Utility
Folks,
The attached file is worksheet protected "123memphis". You can move from this worksheet to "Sheet1" and back with the the command butgton "Exit" or "Return".
This worksheet is an example of a training workbook.
There objective is to have the user practice this exercise. The "Display Data" buttom gives them an idea on what the answer should look like. The "Step-By'Step Solution" command button allows them to look at the solution or print it out. The "Tray Again!" command button clears all data and let's the user start over. This exercise has the use of Conditional Formatting. When I protect the worksheet Conditional Formatting is disabled. Therefore, I'm looking for a way to protect the worksheet except the "Practice" area.
When the worksheet opens I clear all everything so the user sees a blank worksheet and must click on "Display Data" to proceed.
NeedConditionalFormatting.xlsm
0
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
0
 

Author Comment

by:Frank Freese
Comment Utility
Andrew,
Thanks...I still see Conditional Formatting from the ribbon as disabled though. Is there any way to enable that feature (Conditional Formatting) when a worksheet has been protected?
0
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
Review -> Unprotected Sheet

I have not set the password!
0
 

Author Comment

by:Frank Freese
Comment Utility
Did you change any of the code? Things appear to be working now
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
fh_freeze

The way to allow formatting, including conditional formatting, is to check the items in the list I suggested.
0
 

Author Comment

by:Frank Freese
Comment Utility
I am selecting the following when I protect the worksheet:
1. Select locked cells
2. Select unlocked cells
3. Format cells
4. Format columns
5. Format rows

However, when I leave and return that activates the worksheet I do this:

Worksheets("WeekEndsShaded").Unprotect Password:="123memphis".
.
.
.
Worksheets("WeekEndsShaded"). Password:="123memphis"

leaving only
1. Select locked cells
2. Select unlocked cells

selected. This results in Conditonal Formatting not enabled. Is there a way to add the addtional unprotected properties of:
3. Format cells
4. Format columns
5. Format rows

through VBA?
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Are you unlocking any cells?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Frank Freese
Comment Utility
 ActiveSheet.Protect AllowFormattingCells:=True
  ActiveSheet.Protect AllowFormattingColumns:=True
  ActiveSheet.Protect AllowFormattingRows:=True

Open in new window


I have found this but I can't seem to make it stick before or after I protect a worksheet. It looks to be possible, but again my objective is to protect the areas needed while aloowing for Conditional Formatting.
0
 

Author Comment

by:Frank Freese
Comment Utility
Yes, I am unlocking cells.
It's just that when I protect the worksheet Conditional Formatting is not enabled.
0
 

Author Comment

by:Frank Freese
Comment Utility
I truly believe that there is a way to work through this.

I've tried this getting an "Object required" errror.

Worksheets("WeekEndsShaded").Protect.AllowFormattingCells = True
0
 

Accepted Solution

by:
Frank Freese earned 0 total points
Comment Utility
Folks,
Problem solved....
In Excel under the Review tab in the Changes bar there is an option "Allow User to Edit Ranges". That solved my problem.
Thanks for everyone's interest. I can't award myself points though.
0
 

Author Comment

by:Frank Freese
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for fh_freese's comment #a39697184

for the following reason:

I was able to solve the problem myself this time.
0
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
No any points for us! Why?

But, we have contributed!
0
 

Author Comment

by:Frank Freese
Comment Utility
Andrew,
I am grateful for experts insight, opinion and participation - I've used EE many times and will continue to do so.
One has to carefully draw a line from simply contributing to offering a solution. I posted many comments after you posted yours. I did not see where you addressed those comments. What you offered up did not resolve my issue. I am very generous in point distribution and comments are important, providing they add value towards solving the problem.
On this post I was able to research a working solution on my own. Had you pointed me in that direction you'd receive all the points. Unfortunately that was not the case.
I am not aware of a policy from EE that requires points to be awaded for participation. If so, would the monitor please correct me and I'll adjust for points.
You a valued and trusted contributor and I hope to see you more in the future awarding points to you.
Respectfully,
Frank
0
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
First of all, I am not begger for a point!  I found your have already said "Thanks" to me!

Do you think no one contribute to your problem or give you some insight?  If yes, I am no more objection!

By the way, the points cannot turn into any money to us!  Just an encourage!  We are free to contribute our knowledge.

Keep in touch!
0
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
My point is "Give a man a fish, and you feed him for a day; show him how to catch fish, and you feed him for a lifetime."
0
 

Author Comment

by:Frank Freese
Comment Utility
Andrew,
In studying responses to my posted threads I take time to see how the suggestion(s) or solution(s) contribute towards resolving my post. I was encouraged by your post with your attached file. However, the problem continued so I then suspected it had something to do with something in my code for Worksheet_Activate.

I contacted Ron de Bruin, an expect on Conditional Formatting from Holland, about my problem and he pointed out that the option "Check Format Cells" must be selected before protecting the workbook in order to keep Conditional Formatting" enabled. I did that, password protected the worksheet but the problem did not go away. I then returned to my suspected culprit - my code in the Worksheet_Actrivate section. It was then that I noticed the option "Allow Users to Edit Ranges". When I "cleaned up" my code and used the new option, problem solved. I'd never paid any attention to that option before, and I did want to make sure I shared my findings with others.

I need to be more clear in my reason(s) for when awarding points, in fairness to all. I'll work on that and I appreciate you wanting to know. Hopefully, this answered you quesion.

Thanks again, and look for more posts from me going forward.
Frank
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now