About Us

James Warburton

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

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
fill color a protected cell without unprotecting worksheet	taibbico	Excel Worksheet Functions	2	February 1st 10 07:44 PM
Unprotecting worksheets	hip	Excel Worksheet Functions	4	June 5th 06 07:37 AM
Unprotecting password protected workbook	Vnagpal	Excel Discussion (Misc queries)	1	December 10th 05 06:33 AM
unprotecting a worksheet	Randy123	Excel Discussion (Misc queries)	1	August 8th 05 06:11 PM
Unprotecting Cells in a Protected Worksheet	Mark F	Excel Discussion (Misc queries)	2	December 21st 04 11:13 PM

Menu

About Us