Macro to Protect All except a few Ranges

[SamAnnElizabeth]

Hey all,

I think this should be pretty straight forward, but I'm struggling a little. (I'm still learning VBA.) I'm trying to protect a workbook, but leave a few ranges available for co-workers to edit. I have the ranges set up so I can go to: Review Ribbon: Changes Group: Allow Users to Edit Ranges. (Here my ranges are already define) Protect Sheet... (I have my permissions set up already) OK. I'd like to automate this process so it is performed when the sheet is closed. I've found how to protect a while workbook, but I want to do all except my defined ranges. Is there a VBA template someone can show me to follow?

Thanks guys! This forum has been very helpful.

 

[ps1]

Try reading this http://www.mrexcel.com/forum/excel-questions/690334-help-macros-unlocking-selected-locked-cells.html

 

[SamAnnElizabeth]

Ok, I think I'm close, but that thread was not what I was looking for. Here's a rough version of my VBA code, can you tell me what I need to change?

Sub Protect()
'
' Protect Macro
' Protects the workbook upon closing.
'
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=False, AllowUsingPivotTables:=True
Range(Actual_Date, Comments).Select
Selection.Locked = False
Sheets("Review_Dates").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub


"Actual_Date" and "Comments" are some named ranges I have. I get a 'compile error' when I try to run this. Help!

 

[ps1]

Ok, I think I'm close, but that thread was not what I was looking for. Here's a rough version of my VBA code, can you tell me what I need to change?

Sub Protect()
'
' Protect Macro
' Protects the workbook upon closing.
'
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=False, AllowUsingPivotTables:=True
Range("Actual_Date", "Comments").Select
Selection.Locked = False
Sheets("Review_Dates").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub


"Actual_Date" and "Comments" are some named ranges I have. I get a 'compile error' when I try to run this. Help!

Is that what you get when you use the macro recorder?

I do believe you require quotation marks " " (see above)around Actual_Date, Comments

 

[SamAnnElizabeth]

No, it's what I wrote based on the information you provided me in the link. The debugger is saying that there's a problem with me having 'Sub Protect()' at the beginning and 'Private Sub' later down. Can you help me with this?

 

[ps1]

Oh yes, remove the sub protect() and makesure the rest of the code is in the THIS WORKBOOK section and not as a MODULE. I don't think you require any of this code though.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=False, AllowUsingPivotTables:=True

 

[SamAnnElizabeth]

I don't know what I'm doing wrong. I have this as my VBA and when I run it, the dialogue box to select a macro comes up, and my Macro is not on the list! Here's what I'm doing:



'
' Protect Macro
' Protects the workbook upon closing.
'
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
Range("Actual_Date", "Comments", "Current_Gateway", "Rating", "Uncontained_Issues").Select
Selection.Locked = False
Sheets("Review_Dates").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub

 

[ps1]

Ok, this part

Range("Actual_Date", "Comments", "Current_Gateway", "Rating", "Uncontained_Issues").Select

needs to be

Range("Actual_Date", "Comments").select
Range("Current_Gateway", "Rating").select
Range("Uncontained_Issues").Select

I assume you would need some code to make the Review_Dates sheet visable?

Also it states that you require the spreadsheet to be protected once it is closed so you will need instead of

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)

This

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 

[ps1]

Here is my code that I have tested - adjust ranges/sheets to suit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("A1", "b2").Select
Range("c3", "d4").Select
Selection.Locked = False
Sheet(2).Select
ActiveWindow.SelectedSheets.Visible = False
End Sub

 

[SamAnnElizabeth]

I don't think this does at all what I need. I need it to protect the rest of the workbook, this only unprotects the selected ranges, which wouldn't be locked in the first place. How can I be sure to hide other sheets? I think I need to make them visible first?