Macro for formatting cells in protected sheets

Dspace

New Member
Joined
Nov 2, 2020
Messages
6
Office Version
  1. 2010
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

julhs

Board Regular
Joined
Dec 3, 2018
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
Go to the Review tab, click “Protect Sheet” then tick whatever boxes you are allowing users to do while the Sheet is protected
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Try adding this line:
ActiveSheet.Protect AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
 
Solution

Dspace

New Member
Joined
Nov 2, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
Thanks Candyman8019! that did the trick. I had to add that line of code after the password protect. Much appreciated.
 

Dspace

New Member
Joined
Nov 2, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Remove your old “protect” line. You should only require one of them.
 

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Essentially they should be combined:
ActiveSheet.Protect password:=“Test”, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
 

Dspace

New Member
Joined
Nov 2, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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
Top