I currently have a macro that locks the worksheet once it is passed a month
end date so that no more data can be entered on that worksheet
The worksheet locks fine, however the user is still able to use the
unprotect feature, despite the password been entered when I ran the macro

Private Sub Worksheet_Activate()
' LockSheet After a Specified Date

Dim CheckDate As Date
Dim CurrentDate As Date

CheckDate = Range("IU1").Value
CurrentDate = Range("IV1").Value

If CurrentDate CheckDate Then

ActiveSheet.Protect Password:=Range("IV2")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Else

ActiveSheet.Unprotect Password:=Range("IV2")

End If

End Sub

Any advise would be appreciated
TIA

mmc308 wrote:
I currently have a macro that locks the worksheet once it is passed a month
end date so that no more data can be entered on that worksheet
The worksheet locks fine, however the user is still able to use the
unprotect feature, despite the password been entered when I ran the macro

Private Sub Worksheet_Activate()
' LockSheet After a Specified Date

Dim CheckDate As Date
Dim CurrentDate As Date

CheckDate = Range("IU1").Value
CurrentDate = Range("IV1").Value

If CurrentDate CheckDate Then

ActiveSheet.Protect Password:=Range("IV2")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Else

ActiveSheet.Unprotect Password:=Range("IV2")

End If

End Sub

Any advise would be appreciated
TIA

I ran your code (xl2003) and had to supply the password I typed into
IV2 for protection to be removed so the code is fine.

Ken Johnson

This line of code protects the sheet...
ActiveSheet.Protect Password:=Range("IV2")
Since you do not specify it will use the active sheet. Do you have the
password in Cell IV2 of the active sheet? if that cell is blank then there is
no password and the spreadsheet will act as you have indicted. Either fill in
the password on the active sheet or premise Range("IV2") with the sheet the
password comes from.

--
HTH...

Jim Thomlinson


"mmc308" wrote:

I currently have a macro that locks the worksheet once it is passed a month
end date so that no more data can be entered on that worksheet
The worksheet locks fine, however the user is still able to use the
unprotect feature, despite the password been entered when I ran the macro

Private Sub Worksheet_Activate()
' LockSheet After a Specified Date

Dim CheckDate As Date
Dim CurrentDate As Date

CheckDate = Range("IU1").Value
CurrentDate = Range("IV1").Value

If CurrentDate CheckDate Then

ActiveSheet.Protect Password:=Range("IV2")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Else

ActiveSheet.Unprotect Password:=Range("IV2")

End If

End Sub

Any advise would be appreciated
TIA

Jim

Thanks for the reply

Michael

"Jim Thomlinson" wrote:

This line of code protects the sheet...
ActiveSheet.Protect Password:=Range("IV2")
Since you do not specify it will use the active sheet. Do you have the
password in Cell IV2 of the active sheet? if that cell is blank then there is
no password and the spreadsheet will act as you have indicted. Either fill in
the password on the active sheet or premise Range("IV2") with the sheet the
password comes from.

--
HTH...

Jim Thomlinson


"mmc308" wrote:

I currently have a macro that locks the worksheet once it is passed a month
end date so that no more data can be entered on that worksheet
The worksheet locks fine, however the user is still able to use the
unprotect feature, despite the password been entered when I ran the macro

Private Sub Worksheet_Activate()
' LockSheet After a Specified Date

Dim CheckDate As Date
Dim CurrentDate As Date

CheckDate = Range("IU1").Value
CurrentDate = Range("IV1").Value

If CurrentDate CheckDate Then

ActiveSheet.Protect Password:=Range("IV2")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Else

ActiveSheet.Unprotect Password:=Range("IV2")

End If

End Sub

Any advise would be appreciated
TIA

Ken
Thanks for your help your right, it does work, although for a period of
opening the workbook it would'nt, recreated this book same code all is OK
many Thanks Michael

"Ken Johnson" wrote:


mmc308 wrote:
I currently have a macro that locks the worksheet once it is passed a month
end date so that no more data can be entered on that worksheet
The worksheet locks fine, however the user is still able to use the
unprotect feature, despite the password been entered when I ran the macro

Private Sub Worksheet_Activate()
' LockSheet After a Specified Date

Dim CheckDate As Date
Dim CurrentDate As Date

CheckDate = Range("IU1").Value
CurrentDate = Range("IV1").Value

If CurrentDate CheckDate Then

ActiveSheet.Protect Password:=Range("IV2")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Else

ActiveSheet.Unprotect Password:=Range("IV2")

End If

End Sub

Any advise would be appreciated
TIA

I ran your code (xl2003) and had to supply the password I typed into
IV2 for protection to be removed so the code is fine.

Ken Johnson