lock a range question
 

what would be the best way to do this:

if an x is entered in a cell, lock a range of cells in that same row so
nothing in that range could be entered?

--


Gary

lock a range question
 

If you were to start out with all cells on the sheet unlocked then you
could use a worksheet change event similar to this. This checks entry in
columm A and then locks columns C,D and E on that row:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 _
And Target.Value = "x" Then
Me.Unprotect Password:="mypassword"
Range(Cells(Target.Row, 3), Cells(Target.Row, 5)).Locked = True
Me.Protect Password:="mypassword"
End If
End Sub

Hope this helps
Rowan

Gary Keramidas wrote:
what would be the best way to do this:

if an x is entered in a cell, lock a range of cells in that same row so
nothing in that range could be entered?

lock a range question
 

thanks rowan, i had to change the target.count to target ,row.

here is more info.

the sheet is protected, except where users enter data. the verification
column is locked, only the supervisor can enter an x in the verification
column. she unprotects the sheet and verifies the data in v4:z4. if it's ok,
she enters and x in aa4. she does this for rows 4 to potentially, 56.

this same procedure happens in column af4 for range ab4:ae4.

with your code, i adapted it to work in row 4. it locked the range of cells.
so when the supervisor protects the sheet after verifying, the user could
not go back to that line and change anything. don't need any passwords, all
restrictions are through gpo.

so i need to further adapt to for the 2nd range and for rows 4 through 56.

i'll try and see what i can come up with. thanks for the start.

--


Gary


"Rowan Drummond" wrote in message
...
If you were to start out with all cells on the sheet unlocked then you
could use a worksheet change event similar to this. This checks entry in
columm A and then locks columns C,D and E on that row:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 _
And Target.Value = "x" Then
Me.Unprotect Password:="mypassword"
Range(Cells(Target.Row, 3), Cells(Target.Row, 5)).Locked = True
Me.Protect Password:="mypassword"
End If
End Sub

Hope this helps
Rowan

Gary Keramidas wrote:
what would be the best way to do this:

if an x is entered in a cell, lock a range of cells in that same row so
nothing in that range could be entered?

lock a range question
 

rowan:

this seems to work for 1 cell, how do i select columns 11 through 16?

Private Sub Worksheet_Change(ByVal Target As Range)
For x = 4 To 56
If Target.Row = x And Target.Column = 27 _
And Target.Value = "x" Then
Range(Cells(Target.Row, x), Cells(Target.Column,
11)).Locked = True
End If
Next
End Sub

--


Gary


"Rowan Drummond" wrote in message
...
If you were to start out with all cells on the sheet unlocked then you
could use a worksheet change event similar to this. This checks entry in
columm A and then locks columns C,D and E on that row:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 _
And Target.Value = "x" Then
Me.Unprotect Password:="mypassword"
Range(Cells(Target.Row, 3), Cells(Target.Row, 5)).Locked = True
Me.Protect Password:="mypassword"
End If
End Sub

Hope this helps
Rowan

Gary Keramidas wrote:
what would be the best way to do this:

if an x is entered in a cell, lock a range of cells in that same row so
nothing in that range could be entered?

lock a range question
 

this appears to work, can anyone verify?

Private Sub Worksheet_Change(ByVal Target As Range)
For z = 4 To 56
For y = 11 To 16
If Target.Row = z And Target.Column = 27 _
And Target.Value = "x" Then
Range(Cells(Target.Row, z), Cells(Target.Row, y)).Locked =
True
End If
Next
Next
End Sub


--


Gary


"Rowan Drummond" wrote in message
...
If you were to start out with all cells on the sheet unlocked then you
could use a worksheet change event similar to this. This checks entry in
columm A and then locks columns C,D and E on that row:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 _
And Target.Value = "x" Then
Me.Unprotect Password:="mypassword"
Range(Cells(Target.Row, 3), Cells(Target.Row, 5)).Locked = True
Me.Protect Password:="mypassword"
End If
End Sub

Hope this helps
Rowan

Gary Keramidas wrote:
what would be the best way to do this:

if an x is entered in a cell, lock a range of cells in that same row so
nothing in that range could be entered?

lock a range question
 

Hi Gary

I was about to respond with this:

The target.count is in there to check that only one cell has been
changed before firing the event. This stops the event running if a user
pastes data onto the sheet, selects a range and hits delete etc etc.
Feel free to remove it if you like, otherwise

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("AA4:AA56")) Is Nothing _
And Target.Value = "x" Then
Range(Cells(Target.Row, 22), Cells(Target.Row, 26)).Locked = True
ElseIf Not Intersect(Target, Range("AF4:AF56")) Is Nothing _
And Target.Value = "x" Then
Range(Cells(Target.Row, 28), Cells(Target.Row, 31)).Locked = True
End If
End If
End Sub

Does this answer your question?
Regards
Rowan


Gary Keramidas wrote:
rowan:

this seems to work for 1 cell, how do i select columns 11 through 16?

Private Sub Worksheet_Change(ByVal Target As Range)
For x = 4 To 56
If Target.Row = x And Target.Column = 27 _
And Target.Value = "x" Then
Range(Cells(Target.Row, x), Cells(Target.Column,
11)).Locked = True
End If
Next
End Sub

lock a range question
 

I would be a surprised if that did give the desired result. The format
for refering to a range using the cells method is:
Range(Cells(row,column),Cells(row,column))
so you are looping through columns 4 to 56 and columns 11 to 16.
You might want just one statement:
range(cells(target.row,11),cells(target.row,16)).l ocked = true. See my
earlier reply. Aslo I am not sure where columns 11 to 16 is coming from
- I thought we were aiming for columns V to Z.

Regards
Rowan

Gary Keramidas wrote:
this appears to work, can anyone verify?

Private Sub Worksheet_Change(ByVal Target As Range)
For z = 4 To 56
For y = 11 To 16
If Target.Row = z And Target.Column = 27 _
And Target.Value = "x" Then
Range(Cells(Target.Row, z), Cells(Target.Row, y)).Locked =
True
End If
Next
Next
End Sub

lock a range question
 

rowan:

i think that's it. do you think i need to have a way to unlock the cell if
the x in the verification column is deleted. it most likely won't happen,
but end users have a way of doing the unexplained.


thanks again

--


Gary


"Rowan Drummond" wrote in message
...
Hi Gary

I was about to respond with this:

The target.count is in there to check that only one cell has been changed
before firing the event. This stops the event running if a user pastes
data onto the sheet, selects a range and hits delete etc etc. Feel free to
remove it if you like, otherwise

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("AA4:AA56")) Is Nothing _
And Target.Value = "x" Then
Range(Cells(Target.Row, 22), Cells(Target.Row, 26)).Locked = True
ElseIf Not Intersect(Target, Range("AF4:AF56")) Is Nothing _
And Target.Value = "x" Then
Range(Cells(Target.Row, 28), Cells(Target.Row, 31)).Locked = True
End If
End If
End Sub

Does this answer your question?
Regards
Rowan


Gary Keramidas wrote:
rowan:

this seems to work for 1 cell, how do i select columns 11 through 16?

Private Sub Worksheet_Change(ByVal Target As Range)
For x = 4 To 56
If Target.Row = x And Target.Column = 27 _
And Target.Value = "x" Then
Range(Cells(Target.Row, x), Cells(Target.Column,
11)).Locked = True
End If
Next
End Sub

lock a range question
 

that was a mis-typed line by me

--


Gary


"Rowan Drummond" wrote in message
...
I would be a surprised if that did give the desired result. The format for
refering to a range using the cells method is:
Range(Cells(row,column),Cells(row,column))
so you are looping through columns 4 to 56 and columns 11 to 16.
You might want just one statement:
range(cells(target.row,11),cells(target.row,16)).l ocked = true. See my
earlier reply. Aslo I am not sure where columns 11 to 16 is coming from -
I thought we were aiming for columns V to Z.

Regards
Rowan

Gary Keramidas wrote:
this appears to work, can anyone verify?

Private Sub Worksheet_Change(ByVal Target As Range)
For z = 4 To 56
For y = 11 To 16
If Target.Row = z And Target.Column = 27 _
And Target.Value = "x" Then
Range(Cells(Target.Row, z), Cells(Target.Row,
y)).Locked = True
End If
Next
Next
End Sub

lock a range question
 

As I am sure you know if there is even the remotest possibility of a
user doing something unexpected they are almost certain to do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("AA4:AA56")) Is Nothing Then
If Target.Value = "x" Then
Range(Cells(Target.Row, 22), Cells(Target.Row, 26)) _
.Locked = True
ElseIf Target.Value = "" Then
Range(Cells(Target.Row, 22), Cells(Target.Row, 26)) _
.Locked = False
End If
ElseIf Not Intersect(Target, Range("AF4:AF56")) Is Nothing Then
If Target.Value = "x" Then
Range(Cells(Target.Row, 28), Cells(Target.Row, 31)) _
.Locked = True
ElseIf Target.Value = "" Then
Range(Cells(Target.Row, 28), Cells(Target.Row, 31)) _
.Locked = False
End If
End If
End If
End Sub

Regards
Rowan

Gary Keramidas wrote:
rowan:

i think that's it. do you think i need to have a way to unlock the cell if
the x in the verification column is deleted. it most likely won't happen,
but end users have a way of doing the unexplained.


thanks again

lock a range question
 

seems to work as advertised.

i appreciate your time

--


Gary


"Rowan Drummond" wrote in message
...
As I am sure you know if there is even the remotest possibility of a user
doing something unexpected they are almost certain to do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("AA4:AA56")) Is Nothing Then
If Target.Value = "x" Then
Range(Cells(Target.Row, 22), Cells(Target.Row, 26)) _
.Locked = True
ElseIf Target.Value = "" Then
Range(Cells(Target.Row, 22), Cells(Target.Row, 26)) _
.Locked = False
End If
ElseIf Not Intersect(Target, Range("AF4:AF56")) Is Nothing Then
If Target.Value = "x" Then
Range(Cells(Target.Row, 28), Cells(Target.Row, 31)) _
.Locked = True
ElseIf Target.Value = "" Then
Range(Cells(Target.Row, 28), Cells(Target.Row, 31)) _
.Locked = False
End If
End If
End If
End Sub

Regards
Rowan

Gary Keramidas wrote:
rowan:

i think that's it. do you think i need to have a way to unlock the cell
if the x in the verification column is deleted. it most likely won't
happen, but end users have a way of doing the unexplained.


thanks again

lock a range question
 

You're welcome!

Gary Keramidas wrote:
seems to work as advertised.

i appreciate your time

lock a range question
 

rowan:

i changed 2 things and it seems to work ok.

1) If Target.Value = "x" Or Target.Value = "X" Then
to allow for either case

2) Range(Cells(Target.Row, "V"), Cells(Target.Row, "Z"))
to make it easier for me to see which columns. i don't like column numbers.

thanks


Gary


"Rowan Drummond" wrote in message
...
You're welcome!

Gary Keramidas wrote:
seems to work as advertised.

i appreciate your time

lock a range question
 

You could simplify the first one with
If UCase(Target.Value) = "X" Then

Regards
Rowan

Gary Keramidas wrote:
rowan:

i changed 2 things and it seems to work ok.

1) If Target.Value = "x" Or Target.Value = "X" Then
to allow for either case

2) Range(Cells(Target.Row, "V"), Cells(Target.Row, "Z"))
to make it easier for me to see which columns. i don't like column numbers.

thanks


Gary


"Rowan Drummond" wrote in message
...

You're welcome!

Gary Keramidas wrote:

seems to work as advertised.

i appreciate your time

lock a range question
 

i did that first, but wasn't sure that's what i wanted or not.

thanks

--


Gary


"Rowan Drummond" wrote in message
...
You could simplify the first one with
If UCase(Target.Value) = "X" Then

Regards
Rowan

Gary Keramidas wrote:
rowan:

i changed 2 things and it seems to work ok.

1) If Target.Value = "x" Or Target.Value = "X" Then
to allow for either case

2) Range(Cells(Target.Row, "V"), Cells(Target.Row, "Z"))
to make it easier for me to see which columns. i don't like column
numbers.

thanks


Gary


"Rowan Drummond" wrote in message
...

You're welcome!

Gary Keramidas wrote:

seems to work as advertised.

i appreciate your time

lock a range question
 

rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)
--


Gary


"Rowan Drummond" wrote in message
...
I would be a surprised if that did give the desired result. The format for
refering to a range using the cells method is:
Range(Cells(row,column),Cells(row,column))
so you are looping through columns 4 to 56 and columns 11 to 16.
You might want just one statement:
range(cells(target.row,11),cells(target.row,16)).l ocked = true. See my
earlier reply. Aslo I am not sure where columns 11 to 16 is coming from -
I thought we were aiming for columns V to Z.

Regards
Rowan

Gary Keramidas wrote:
this appears to work, can anyone verify?

Private Sub Worksheet_Change(ByVal Target As Range)
For z = 4 To 56
For y = 11 To 16
If Target.Row = z And Target.Column = 27 _
And Target.Value = "x" Then
Range(Cells(Target.Row, z), Cells(Target.Row,
y)).Locked = True
End If
Next
Next
End Sub

lock a range question
 

Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires every
time the user clicks on a different sheet and will not track changes to
individual sheets.

You should have Worksheet_Change event behind each sheet you want to
monitor.

Regards
Rowan

Gary Keramidas wrote:
rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

You did a great job working with Gary and I'm hoping this will be a simple
question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls a macro
to change data on other sheets. I have that one cell password protected so
that the manager can make a change to that cell, without giving out the
password for the entire sheet. Everything works great, except once the
manager enters the password for that cell, running my code to protect the
sheet again still leaves that cell open. Is there any code that can relock
that particular cell that I can use at the end of the macro that runs when
the manager makes a change you are aware of?

"Rowan Drummond" wrote:

Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires every
time the user clicks on a different sheet and will not track changes to
individual sheets.

You should have Worksheet_Change event behind each sheet you want to
monitor.

Regards
Rowan

Gary Keramidas wrote:
rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

How have you assigned a password to that individual cell and how does
the manager unlock it? Also show the code you are currently using to
protect the sheet.

Regards
Rowan

David wrote:
You did a great job working with Gary and I'm hoping this will be a simple
question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls a macro
to change data on other sheets. I have that one cell password protected so
that the manager can make a change to that cell, without giving out the
password for the entire sheet. Everything works great, except once the
manager enters the password for that cell, running my code to protect the
sheet again still leaves that cell open. Is there any code that can relock
that particular cell that I can use at the end of the macro that runs when
the manager makes a change you are aware of?

"Rowan Drummond" wrote:


Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires every
time the user clicks on a different sheet and will not track changes to
individual sheets.

You should have Worksheet_Change event behind each sheet you want to
monitor.

Regards
Rowan

Gary Keramidas wrote:

rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

I have just seen your other post. What is HideAllWageData doing? Post
the code if possible or at least the bits that are unprotecting and
reprotecting the sheet. Also post the code for UseChangePassword.

Once you have set up the alloweditrange protecting the sheet should
re-enable this. You shouldn't need to set it again with code, unless you
are trying to change the password.

Regards
Rowan

David wrote:
You did a great job working with Gary and I'm hoping this will be a simple
question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls a macro
to change data on other sheets. I have that one cell password protected so
that the manager can make a change to that cell, without giving out the
password for the entire sheet. Everything works great, except once the
manager enters the password for that cell, running my code to protect the
sheet again still leaves that cell open. Is there any code that can relock
that particular cell that I can use at the end of the macro that runs when
the manager makes a change you are aware of?

"Rowan Drummond" wrote:


Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires every
time the user clicks on a different sheet and will not track changes to
individual sheets.

You should have Worksheet_Change event behind each sheet you want to
monitor.

Regards
Rowan

Gary Keramidas wrote:

rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

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

"Rowan Drummond" wrote:

How have you assigned a password to that individual cell and how does
the manager unlock it? Also show the code you are currently using to
protect the sheet.

Regards
Rowan

David wrote:
You did a great job working with Gary and I'm hoping this will be a simple
question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls a macro
to change data on other sheets. I have that one cell password protected so
that the manager can make a change to that cell, without giving out the
password for the entire sheet. Everything works great, except once the
manager enters the password for that cell, running my code to protect the
sheet again still leaves that cell open. Is there any code that can relock
that particular cell that I can use at the end of the macro that runs when
the manager makes a change you are aware of?

"Rowan Drummond" wrote:


Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires every
time the user clicks on a different sheet and will not track changes to
individual sheets.

You should have Worksheet_Change event behind each sheet you want to
monitor.

Regards
Rowan

Gary Keramidas wrote:

rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

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).ChangePa ssword _
password:="wages"

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

End Sub

"Rowan Drummond" wrote:

I have just seen your other post. What is HideAllWageData doing? Post
the code if possible or at least the bits that are unprotecting and
reprotecting the sheet. Also post the code for UseChangePassword.

Once you have set up the alloweditrange protecting the sheet should
re-enable this. You shouldn't need to set it again with code, unless you
are trying to change the password.

Regards
Rowan

David wrote:
You did a great job working with Gary and I'm hoping this will be a simple
question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls a macro
to change data on other sheets. I have that one cell password protected so
that the manager can make a change to that cell, without giving out the
password for the entire sheet. Everything works great, except once the
manager enters the password for that cell, running my code to protect the
sheet again still leaves that cell open. Is there any code that can relock
that particular cell that I can use at the end of the macro that runs when
the manager makes a change you are aware of?

"Rowan Drummond" wrote:


Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires every
time the user clicks on a different sheet and will not track changes to
individual sheets.

You should have Worksheet_Change event behind each sheet you want to
monitor.

Regards
Rowan

Gary Keramidas wrote:

rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

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

David wrote:
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

"Rowan Drummond" wrote:


How have you assigned a password to that individual cell and how does
the manager unlock it? Also show the code you are currently using to
protect the sheet.

Regards
Rowan

David wrote:

You did a great job working with Gary and I'm hoping this will be a simple
question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls a macro
to change data on other sheets. I have that one cell password protected so
that the manager can make a change to that cell, without giving out the
password for the entire sheet. Everything works great, except once the
manager enters the password for that cell, running my code to protect the
sheet again still leaves that cell open. Is there any code that can relock
that particular cell that I can use at the end of the macro that runs when
the manager makes a change you are aware of?

"Rowan Drummond" wrote:



Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires every
time the user clicks on a different sheet and will not track changes to
individual sheets.

You should have Worksheet_Change event behind each sheet you want to
monitor.

Regards
Rowan

Gary Keramidas wrote:


rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from each
sheet and put in in the workbook module so it can monitor multiple sheets,
it doesn't work.
when i select column aa or af and the sheet is protected, get a debug
error. if i unprotect and enter an x, it doesn't lock the range.

why would it act differently, the only difference in code is the first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

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

Rowan Drummond wrote:
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

David wrote:

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

"Rowan Drummond" wrote:


How have you assigned a password to that individual cell and how does
the manager unlock it? Also show the code you are currently using to
protect the sheet.

Regards
Rowan

David wrote:

You did a great job working with Gary and I'm hoping this will be a
simple question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls
a macro to change data on other sheets. I have that one cell
password protected so that the manager can make a change to that
cell, without giving out the password for the entire sheet.
Everything works great, except once the manager enters the password
for that cell, running my code to protect the sheet again still
leaves that cell open. Is there any code that can relock that
particular cell that I can use at the end of the macro that runs
when the manager makes a change you are aware of?

"Rowan Drummond" wrote:



Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires
every time the user clicks on a different sheet and will not track
changes to individual sheets.

You should have Worksheet_Change event behind each sheet you want
to monitor.

Regards
Rowan

Gary Keramidas wrote:


rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from
each sheet and put in in the workbook module so it can monitor
multiple sheets, it doesn't work.
when i select column aa or af and the sheet is protected, get a
debug error. if i unprotect and enter an x, it doesn't lock the
range.

why would it act differently, the only difference in code is the
first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

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" wrote:

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

Rowan Drummond wrote:
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

David wrote:

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

"Rowan Drummond" wrote:


How have you assigned a password to that individual cell and how does
the manager unlock it? Also show the code you are currently using to
protect the sheet.

Regards
Rowan

David wrote:

You did a great job working with Gary and I'm hoping this will be a
simple question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls
a macro to change data on other sheets. I have that one cell
password protected so that the manager can make a change to that
cell, without giving out the password for the entire sheet.
Everything works great, except once the manager enters the password
for that cell, running my code to protect the sheet again still
leaves that cell open. Is there any code that can relock that
particular cell that I can use at the end of the macro that runs
when the manager makes a change you are aware of?

"Rowan Drummond" wrote:



Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires
every time the user clicks on a different sheet and will not track
changes to individual sheets.

You should have Worksheet_Change event behind each sheet you want
to monitor.

Regards
Rowan

Gary Keramidas wrote:


rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from
each sheet and put in in the workbook module so it can monitor
multiple sheets, it doesn't work.
when i select column aa or af and the sheet is protected, get a
debug error. if i unprotect and enter an x, it doesn't lock the
range.

why would it act differently, the only difference in code is the
first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

You're welcome. Thanks for the feedback.

David wrote:
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" wrote:


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

Rowan Drummond wrote:

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

David wrote:


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

"Rowan Drummond" wrote:



How have you assigned a password to that individual cell and how does
the manager unlock it? Also show the code you are currently using to
protect the sheet.

Regards
Rowan

David wrote:


You did a great job working with Gary and I'm hoping this will be a
simple question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls
a macro to change data on other sheets. I have that one cell
password protected so that the manager can make a change to that
cell, without giving out the password for the entire sheet.
Everything works great, except once the manager enters the password
for that cell, running my code to protect the sheet again still
leaves that cell open. Is there any code that can relock that
particular cell that I can use at the end of the macro that runs
when the manager makes a change you are aware of?

"Rowan Drummond" wrote:




Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires
every time the user clicks on a different sheet and will not track
changes to individual sheets.

You should have Worksheet_Change event behind each sheet you want
to monitor.

Regards
Rowan

Gary Keramidas wrote:



rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from
each sheet and put in in the workbook module so it can monitor
multiple sheets, it doesn't work.
when i select column aa or af and the sheet is protected, get a
debug error. if i unprotect and enter an x, it doesn't lock the
range.

why would it act differently, the only difference in code is the
first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)

lock a range question
 

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

"Rowan Drummond" wrote:

You're welcome. Thanks for the feedback.

David wrote:
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" wrote:


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

Rowan Drummond wrote:

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

David wrote:


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

"Rowan Drummond" wrote:



How have you assigned a password to that individual cell and how does
the manager unlock it? Also show the code you are currently using to
protect the sheet.

Regards
Rowan

David wrote:


You did a great job working with Gary and I'm hoping this will be a
simple question and you can help.
I have a cell D255, that a person can enter a Y or y into that calls
a macro to change data on other sheets. I have that one cell
password protected so that the manager can make a change to that
cell, without giving out the password for the entire sheet.
Everything works great, except once the manager enters the password
for that cell, running my code to protect the sheet again still
leaves that cell open. Is there any code that can relock that
particular cell that I can use at the end of the macro that runs
when the manager makes a change you are aware of?

"Rowan Drummond" wrote:




Hi Gary

The workbook sheetselection change event is very different from the
worksheet change event. The Workbook_SheetSelectionChange fires
every time the user clicks on a different sheet and will not track
changes to individual sheets.

You should have Worksheet_Change event behind each sheet you want
to monitor.

Regards
Rowan

Gary Keramidas wrote:



rowan:
ran into a problem:

if i put the code on each sheet it works fine. if i remove it from
each sheet and put in in the workbook module so it can monitor
multiple sheets, it doesn't work.
when i select column aa or af and the sheet is protected, get a
debug error. if i unprotect and enter an x, it doesn't lock the
range.

why would it act differently, the only difference in code is the
first line?
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object,
ByVal Target As Range)
instead of
Private Sub Worksheet_Change(ByVal Target As Range)