?
Solved

Protect worksheet but NOT Conditional Formatting

Posted on 2013-12-04
22
Medium Priority
?
291 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
[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
  • 11
  • 6
  • 3
22 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39696185
post your worksheet here
0
 
LVL 34

Expert Comment

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

Author Comment

by:Frank Freese
ID: 39696346
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:andrew_man
ID: 39696370
0
 

Author Comment

by:Frank Freese
ID: 39696418
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
ID: 39696427
Review -> Unprotected Sheet

I have not set the password!
0
 

Author Comment

by:Frank Freese
ID: 39696475
Did you change any of the code? Things appear to be working now
0
 
LVL 34

Expert Comment

by:Norie
ID: 39696494
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
ID: 39696570
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 34

Expert Comment

by:Norie
ID: 39696666
Are you unlocking any cells?
0
 

Author Comment

by:Frank Freese
ID: 39696670
 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
ID: 39696704
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
ID: 39697069
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
ID: 39697184
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
ID: 39697303
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
ID: 39697304
No any points for us! Why?

But, we have contributed!
0
 

Author Comment

by:Frank Freese
ID: 39698129
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
ID: 39698158
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
ID: 39698186
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
ID: 39698259
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

752 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