ExcelBanter - unprotecting pswd protected sht

Much appreciated Harald, thanks.

James
-----Original Message-----
Hi James

Sub test()
ActiveSheet.Unprotect ("PWD")
MsgBox "Open !"
ActiveSheet.Protect ("PWD")
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"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

.