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

Kimpnc

New Member
Joined
Aug 17, 2015
Messages
13
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.
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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