• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Protect worksheet but NOT Conditional Formatting

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
Frank Freese
Asked:
Frank Freese
  • 11
  • 6
  • 3
1 Solution
 
andrew_manCommented:
post your worksheet here
0
 
NorieCommented:
When you protect the sheet check Format Cells/Columns/Rows in the Allow list.
0
 
Frank FreeseAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
andrew_manCommented:
0
 
Frank FreeseAuthor Commented:
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
 
andrew_manCommented:
Review -> Unprotected Sheet

I have not set the password!
0
 
Frank FreeseAuthor Commented:
Did you change any of the code? Things appear to be working now
0
 
NorieCommented:
fh_freeze

The way to allow formatting, including conditional formatting, is to check the items in the list I suggested.
0
 
Frank FreeseAuthor Commented:
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
 
NorieCommented:
Are you unlocking any cells?
0
 
Frank FreeseAuthor Commented:
 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
 
Frank FreeseAuthor Commented:
Yes, I am unlocking cells.
It's just that when I protect the worksheet Conditional Formatting is not enabled.
0
 
Frank FreeseAuthor Commented:
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
 
Frank FreeseAuthor Commented:
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
 
Frank FreeseAuthor Commented:
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
 
andrew_manCommented:
No any points for us! Why?

But, we have contributed!
0
 
Frank FreeseAuthor Commented:
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
 
andrew_manCommented:
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
 
andrew_manCommented:
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
 
Frank FreeseAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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