Macro for formatting cells in protected sheets

Dspace

New Member
Joined
Nov 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Good morning, I have a macro to unprotect a worksheet, hide empty rows & then reprotect the worksheet. I want to allow users to color code (format cells) after his macro run. It seems like an easy fix but I'm struggling. Any help would be appreciated as my boss is really wanting this enhancement. Code below

Sub HideAllRows()
'Hides everything without a quantity in column H. Used for all three sales teams as well as Bid Desk when hiding everything if we've added something custom
Unprotect Password:="Test"
For Each cell In Range("H18:H458")
cell.EntireRow.Hidden = cell.Value = 0
Next cell

allowformatingcells = true (this is the line that I'm playing with that isn't working)

Protect Password:="Test"
End Sub

Thanks a bunch for any help provided
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Go to the Review tab, click “Protect Sheet” then tick whatever boxes you are allowing users to do while the Sheet is protected
 
Upvote 0
Try adding this line:
ActiveSheet.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
 
Upvote 0
Solution
Thanks Candyman8019! that did the trick. I had to add that line of code after the password protect. Much appreciated.
 
Upvote 0
After further testing I don't think this solution fully solves my problem.

If I enter that line of code before the the password protect it will not allow highlighting.
If I enter that line of code after the password protect it allows highlighting but the worksheet is not protected.

Any ideas?
Thanks a bunch.
 
Upvote 0
Remove your old “protect” line. You should only require one of them.
 
Upvote 0
Essentially they should be combined:
ActiveSheet.Protect password:=“Test”, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
 
Upvote 0
That did the trick. Thank you! Just needed to combine them to put the password protection within the code you provided like below. Again, much appreciated.

ActiveSheet.Protect Password:="Test", AllowFormattingCells:=True
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top