lock a range question

[Gary Keramidas]

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 Drummond]

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]

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 Keramidas]

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 Keramidas]

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 Drummond]

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

 

[Rowan Drummond]

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)).locked = 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]

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 Keramidas]

that was a mis-typed line by me

--


Gary

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)).locked = 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

 

[Rowan Drummond]

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]

seems to work as advertised.

i appreciate your time

 

[Rowan Drummond]

You're welcome!

 

[Gary Keramidas]

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]

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

Regards
Rowan

 

[Gary Keramidas]

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

thanks

 

[Gary Keramidas]

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)

 

[Rowan Drummond]

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

 

[Guest]

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]

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

 

[Rowan Drummond]

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