VBA to Protect ALL sheets with given name but allow editing of unlocked cells.

[Kimpnc]

Hi Im new to this forum so hope this is the type of question.
I have monthly workbooks which i use to verify data from a number of sources, most i have macros to import from supplier spreadsheets.
My problem is one field has to be manually added by someone in another department. They need to enter the data and change the cell colour.
I can do this by unlocking cells then locking each sheet individually but with 30 sheets it gets very tiresome as I have to unlock them each time data arrives for me to run my macros.
I have a macro to lock each sheet but this does not allow the format to be changed only input data.
As the person concerned keeps entering data in other cells [ to sabotage the process ] I really need to know how to lock all the relevant sheets Quickly.

Thanks for your help.

 

[Kimpnc]

Worked it out .
As my question has had quite a few views but no reply hope this will be of help if you came here with a similar issue.
Here is a snippet to put you in the right direction.

'After un-protecting all the columns which are to be left accessible
'each sheet has to be individually protected [ My range K4 just clears shading from all un-protected cells

Sheets(" Dept1## Sheet Name##").Select
ActiveSheet.Protect Password:=" ## Sheet Password##").", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingColumns:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Range("K4").Select
Sheets(" Dept2 ##Sheet Name##").Select
ActiveSheet.Protect Password:=" ## Sheet Password##").", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingColumns:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Range("K4").Select
'
'Then the workbook iteslf has to be protected to lock sheetnames etc

'protects workbook structure so page names etc can be changed
ActiveWorkbook.Protect Password:=" ## Workbook Pasword ## ", Structure:=True, Windows:=True

Sheets("Dept1").Select

 

[Msgjazz]

I've got a somewhat similar problem and was looking for help. I have a macro that protect and unprotects all the sheets on the workbook, but I can't get it to allow filtering. See below:

Sub Protect()
'
' Protect Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
'Step 1: Declare your variables

Dim ws As Worksheet

'Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets

'Step 3: Protect and loop to next worksheet

ws.Protect Password:="1234"

Next ws
End Sub
Sub Unprotect()
'
' Unprotect Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
'Step 1: Declare your variables
Dim ws As Worksheet

'Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets

'Step 3: Loop to next worksheet
ws.Unprotect Password:="1234"
Next ws


End Sub

Although I recorded a macro to get the context, I can't make it work on my current macro.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True

would greatly appreciate any help

Thanks
Jose