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

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?

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?

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?

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

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

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

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?

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

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

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

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)