Formatting cells on unlocked range on protected sheet

krissibacon

New Member
Joined
Sep 29, 2006
Messages
1
Hi I am creating a spreadsheet, that hides certain rows when a radio button is pressed. Also certain rows need to be protected, and some rows still available for the user to update (text, Checkboxes, borders). I have used a macro attached to do the protection and row hiding and have used the Range option are under the protection menu to allow the rows I need to be available for edit.

My problem lies is in the area available for editing, as I cannot place a check box, or format the cells, I however do not want these actions to be available in the protected area of the worksheet.

Sub Contractor()
'
' Contractor Macro
'

Sheets("Day 1").Select
ActiveSheet.Protect Password:="checklist", Contents:=True, Scenarios:=True, DrawingObjects:=True
Sheets("Week 1").Select
ActiveSheet.Unprotect Password:="checklist"
Rows("11:11").Select
Range("B11").Activate
Selection.EntireRow.Hidden = True
Rows("42:44").Select
Range("B42").Activate
Selection.EntireRow.Hidden = Tru
ActiveSheet.Protect Password:="checklist", Contents:=True, Scenarios:=True, DrawingObjects:=True
Sheets("Month 1").Select
ActiveSheet.Unprotect Password:="checklist"
Rows("12:12").Select
Selection.EntireRow.Hidden = True
Rows("18:22").Select
Selection.EntireRow.Hidden = True
Rows("28:28").Select
Selection.EntireRow.Hidden = True
ActiveSheet.Protect Password:="checklist", Contents:=True, Scenarios:=True, DrawingObjects:=True
Sheets("Demographics").Select

End Sub

Sub Staff()
'
' Staff Macro
'
'
Sheets("Week 1").Select
ActiveSheet.Unprotect Password:="checklist"
Rows("11:11").Select
Range("B11").Activate
Selection.EntireRow.Hidden = False
Rows("42:44").Select
Range("B42").Activate
Selection.EntireRow.Hidden = False
ActiveSheet.Protect Password:="checklist", Contents:=True, Scenarios:=True
Sheets("Month 1").Select
ActiveSheet.Unprotect Password:="checklist"
Rows("12:12").Select
Selection.EntireRow.Hidden = False
Rows("18:22").Select
Selection.EntireRow.Hidden = False
Rows("28:28").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Protect Password:="checklist", Contents:=True, Scenarios:=True
Sheets("Day 1").Select
ActiveSheet.Protect Password:="checklist", Contents:=True, Scenarios:=True
Sheets("Demographics").Select
End Sub
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,020
Members
410,646
Latest member
LegenDSlayeR
Top