Posted to microsoft.public.excel.programming
|
|
unprotecting pswd protected sht
Just add the password as a paramater to both the Protect and Unprotect
methods. E.g.
Worksheets(1).Unprotect "fred"
--
Regards,
Bill Lunney
www.billlunney.com
"James Warburton" wrote in message
...
What I need to be able to do (and can't) is to unprotect
and re-protect a password-protected worksheet within a
piece of code.
I've tried recording but all I get
is "ActiveSheet.Unprotect" even when I use a password to
unprotect. If I use that in my code the user is prompted
to enter the password, but I don't want to give the user
the password. Perhaps there's a way of getting the
password into the password prompt using code??
The reason for all this is that while I don't want the
validations settings on my workesheet to be alterable by
the user, I do want them to be alterable by the code I am
attaching to the sheet (these would be driven by the user
making certain entries on the sheet).
Sub james()
' NEED TO UNPROTECT THE SHEET HERE
Columns(2).Select
If Range("C5").Value = "The ACAD Centre" Then
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Proc_list_3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ElseIf Range("C5").Value = "University College London
Hospitals" Then
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Proc_list_2"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Proc_list_1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Range("C5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=RTLIst2"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("B1:B10").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly,
AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'NEED TO RE-PROTECT THE SHEET HERE
Range("C5").Select
End Sub
|