lock a range question

[Guest]

To protect the cell, using Tools/Protection/Allow Users to Edit
Ranges/Creating a new Range (just that one cell) and giving it a password.
I added most of my unprotect code in the HideAllWageData macro (the macro
will go to 30 pages and change a flag to match what is in cell D255 - this
flag controls conditional formating on each page and dictates whether payroll
informaion is displayed on those 30 pages). The first part of the macro
unlocks all the sheets, it then does it thing on each page, comes back to the
starting page and relocks all the pages in the workbook. I found that by
doing this, when Y or y is selected, the unprotect/protect features in the
macro relocks cell D255 as well. However, if anything other than Y or y is
entered, the cell remains unlocked. I need to have it locked back. I tried
using my protect code if neither condition was met, but got into a loop that
I had to stop manually. Same code, just different condition I guess. That's
why I'm trying to call something when the value is neither Y or y to relock
the cell.
Here is the HideAllWageData macro - haven't got to the part of changing the
value on each page yet...that should be the simple part (PS - the password is
hidden in cell CA3 on the same worksheet I'm running the macro from:

Public Sub HideAllWageData()

' UnprotectAllSheets Macro
' Macro recorded 12/11/2005 by David L Perkins
'
'
Dim password As String
Application.ScreenUpdating = False

Worksheets("Global Setup").Select
Range("CA3").Select
password = Range("CA3").Value
Range("D255").Select

If password = "" Then
'do nothing
Else
For Each ws In Worksheets
ws.Activate
ActiveSheet.Unprotect (password)
Next ws
End If

Worksheets("Index").Visible = xlSheetHidden
Worksheets("Global Setup").Select
Worksheets("Global Setup").Rows("13").Hidden = True
Range("D255").Select
Application.ScreenUpdating = True

'Start changing flags on each sheet here.

MsgBox "It Works To Here!"

' ProtectAllSheets Macro
' Macro recorded 12/11/2005 by David L Perkins
'
'
Application.ScreenUpdating = False

If password = "" Then
'do nothing
Else
For Each ws In Worksheets
ws.Activate
ActiveSheet.Protect (password)
Next ws
End If

Worksheets("Index").Visible = xlSheetHidden
Worksheets("Global Setup").Select
Range("CA3").Select
Range("CA3").Value = password
Range("D255").Select

Application.ScreenUpdating = True

End Sub

 

[Guest]

Here's the UseChangePassword code, modified a bit from the MS site

Sub UseChangePassword()

Dim wksOne As Worksheet

Set wksOne = Application.ActiveSheet

' Establish a range that can allow edits
' on the protected worksheet.
wksOne.Protection.AllowEditRanges.Add _
Title:="HideWageData", _
Range:=Range("D255"), _
password:="wages"

'.MsgBox "Cells A1 to A4 can be edited on the protected worksheet."

' Change the password.
wksOne.Protection.AllowEditRanges.Item(1).ChangePassword _
password:="wages"

MsgBox "The password for these cells has been changed."

End Sub

 

[Rowan Drummond]

Just unprotecting and reprotecting the sheet will reset range D255's
password so you could try your event like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$255" And UCase(Target.Value) = "Y" Then
Call HideAllWageData
ElseIf Target.Address = "$D$255" Then
Me.Unprotect Password:=Range("CA3").Value
Me.Protect Password:=Range("CA3").Value
End If
End Sub

Hope this helps
Rowan

 

[Rowan Drummond]

Or easier to read:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$255" Then
If UCase(Target.Value) = "Y" Then
Call HideAllWageData
Else
Me.Unprotect Password:=Range("CA3").Value
Me.Protect Password:=Range("CA3").Value
End If
End If
End Sub

Regards
Rowan

 

[Guest]

Thanks so much...I knew I was messing up something that really could be
simple. Thank you again so much for taking your time to help me!

David

 

[Rowan Drummond]

You're welcome. Thanks for the feedback.

Thanks so much...I knew I was messing up something that really could be
simple. Thank you again so much for taking your time to help me!

David

:

 

[Guest]

I thought I would share with you the final code I settled on. Handles entries
that are neither Y or N and calls seperate routines based on the 2 valid
responses. Thanks again so much!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$255" Then
If UCase(Target.Value) = "Y" Then
Call HideAllWageData
MsgBox "The Wages For All Plumbers Has Been Hidden."
Else
If UCase(Target.Value) = "N" Then
Call ShowAllWageData
MsgBox "The Wages For All Plumbers Are Now Visible."
Else
Me.Unprotect password:=Range("CA3").Value
Me.Protect password:=Range("CA3").Value
MsgBox "Enter A Valid Response Y or N."
End If
End If
End If
End Sub