How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Brandon M. Your Own Question
Brandon M.
Brandon M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6977
Experience:  10+ Years Mac Support as contractor and currently an System Administrator for law firm
12024030
Type Your Microsoft Office Question Here...
Brandon M. is online now
A new question is answered every 9 seconds

I hope you can help. I need a macro to protect

This answer was rated:

I hope you can help. I need a macro to protect multiple worksheets in a workbook (not all but only ones specified) can I do this with the worksheet names? Your help would be greatly appreciated. Please note one of the sheets I do not want protected contains a pivot table, I need the filter to be able to be used.


BeBoo :

Hello and welcome to JustAnswer! My name is XXXXX XXXXX I would be glad to assist you with your issue today. Please note that I am in eastern time (UTC -5:00) and am generally online between the hours of 10 AM and 4 PM and some evenings between 7 PM and 11 PM.

BeBoo :

It is possible to single out the sheets. The code you provided already works, correct? But it protects each sheet, right?

BeBoo :

If so, you just need a simple if statement or a switch case. Switch cases are easier to use and understand. Let me know when you are around and I can explain.

Customer:

Hi Brandon,

Customer:

Is it possible for you to amend my script to allow for individual sheets

BeBoo :

Yes, does the existing script work?

Customer:

yes

BeBoo :

Great. Just after the For Each line, you'll want to do something like this:

BeBoo :

Select Case wsheet.Name
Case "Sheet1"
wsheet.Protect Password:="123"
Case "Sheet2"
wsheet.Protect Password:="456"
End Select

BeBoo :

All you need to do is add more Case statements for the sheet names

Customer:

the password XXXXX be the same right?

Customer:

just one more thing if I can, if I want to unlock sheets at start of script and lock at end what is the start of the script to lock if I put Const mypass As String = into script it does not work

Customer:

ie after this but before end sub

Customer:

End With
Sheets("Debtors Historical Data").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors EV Raw Data").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors C Flow").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors Summ").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors Details").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors Input").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select

BeBoo :

Sorry, I am not sure I am following - You want to unlock all of the sheets using the mypass string, right?

Customer:

I would like to do so without requesting password XXXXX lock without requesting password XXXXX only as part of another macro script, I have to refresh a pivot table and if cells are locked it will not let me use the filter

Customer:

This is the script with running macro to unprotect and protect

Customer:

Application.Run "'Debtors Final.xlsm'!UnProtectSheets"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Debtors Input").Select
Range("A1").Select
Sheets("Debtors EV Raw Data").Select
Range("A1").Select
Sheets("Debtors Historical Data").Select
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Sheets("Debtors Input").Select
Range("O3").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("P3").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("O3").Select
ActiveCell.FormulaR1C1 = _
"=IF(IFERROR(VLOOKUP(RC[-8],'Debtors Historical Data'!R2C1:R10003C21,'Debtors Historical Data'!RC15),""No Ref"")=1,IFERROR(VLOOKUP(RC[-8],'Debtors Historical Data'!R2C1:R10003C21,15),""No Ref""),R10921C15)"
Range("O3").Select
Selection.AutoFill Destination:=Range("O3:O10003")
Range("O3:O10003").Select
Range("P3").Select
ActiveCell.FormulaR1C1 = _
"=IF(IFERROR(VLOOKUP(RC[-9],'Debtors Historical Data'!R2C1:R10003C21,'Debtors Historical Data'!RC16),""No Ref"")=1,IFERROR(VLOOKUP(RC[-9],'Debtors Historical Data'!R2C1:R10003C21,16),""No Ref""),R10214C16)"
Range("P3").Select
Selection.AutoFill Destination:=Range("P3:P10003")
Range("P3:P10003").Select
Range("Q3").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-10],'Debtors Historical Data'!R[-1]C1:R[10000]C20,17),"""")"
Range("Q3").Select
Selection.AutoFill Destination:=Range("Q3:Q10921")
Range("Q3:Q10921").Select
Range("O3:Q10921").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("O3:O10003").Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Bw$3:$Bw$920"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("P3:P10003").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Bv$3:$Bv$214"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Sheets("Debtors Historical Data").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors EV Raw Data").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors C Flow").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors Summ").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors Details").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Sheets("Debtors Input").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Range("A1").Select
Application.Run "'Debtors Final.xlsm'!ProtectSheets"
End Sub

Customer:

it stops here

Customer:


ActiveCell.SpecialCells(xlCellTypeSameValidation).Select

BeBoo :

Without having the actual workbook, you would just need to do the same thing with the For loop except do wsheet.Unprotect Password:="123"

Brandon M. and other Microsoft Office Specialists are ready to help you

Related Microsoft Office Questions