Locking cells on different sheets

[XrayLemi]

Hello all. I am trying to get help with locking/unlocking cells. There is plenty of information here on how to lock/unlock cells, rows, and columns with VBA if all the cells are on the same sheet. However, in my case, I need to be able to unlock specific cells on Sheet4 after the final entry on Sheet3.

This is what I have to lock/unlock cells on the same sheet.

Dim p As Range, z As Range
     Set p = Range("N6:N4999")
     Set p = Intersect(Target, p)
     If Not p Is Nothing Then
   Application.EnableEvents = False
     For Each z In p
      Select Case True
       Case 14 = z.Column 'N
        If z.Value <> "" Then
         Check = MsgBox("Are your entries correct?" & vbCrLf & "After entering yes, These values CANNOT be changed.", vbYesNo + vbQuestion, "Cell Lock Notification")
            If Check = vbYes Then
             Target.Rows.EntireRow.Locked = True
             Cells(z.Row + 1, "B").Locked = False
             Cells(z.Row + 1, "C").Locked = False
             Cells(z.Row + 1, "D").Locked = False
             Cells(z.Row + 1, "E").Locked = False
             Cells(z.Row + 1, "F").Locked = False
             Cells(z.Row + 1, "G").Locked = False
             Cells(z.Row + 1, "I").Locked = False
             Cells(z.Row + 1, "J").Locked = False
             Cells(z.Row + 1, "K").Locked = False
             Cells(z.Row + 1, "M").Locked = False
             If Cells(z.Row, "R").Value <> "" Then Copyemail 'R
             If Cells(z.Row, "S").Value <> "" Then ThisWorkbook.Save 'S
             With Me
                .Parent.Activate
                .Activate
                .Range("B" & Rows.Count).End(xlUp).Offset(1).Activate
            End With
            Else
             Cells(z.Row, "N").Value = ""
           End If
          End If
      Case Else
    End Select
   Next z
 End If

This code works perfectly for cells on the same sheet. When a value is entered in the cell in column N, it locks the entire current row being worked on, and unlocks cells B,C,D,E,F,G,I,J,K and M on the next line for data entry.

When the user gets to line 5000, I want to lock row 5000, and unlock cells B,C,D,E,F,G,I,J,K and M on row 6 in Sheet4. This is the part I cannot get working. I don't know if This code can be manipulated, or if it needs to be it's own separate lines of code to work.

Thank you in advance,
Jim

 

[portews]

All you should have to do is call out which sheet and which range you want to protect/unprotect.
Try this, I think it goes after your "Next z" line:

Range("N5000").EntireRow.Locked = True
Sheets(4).Range("B6:K6").Locked = False
Sheets(4).Range("M6").Locked = False

You can set the range for your other "Cells(z.Row + 1, "B" thru "M").Locked = False" rows as well in a similar manner as I have shown. It will save some computing time if you do it as one block.

 

[XrayLemi]

Hi Bill. This seems to work perfectly! It needed to go between the If cells and With Me lines to work like I needed. I think I was trying to make it harder than it needed to be. This was really simple. As for the older code....If it ain't broke don't fix it. Maybe I will look at it later if I have time.

Thank you very much!
Jim

 

[portews]

The only thing with putting that code where you put it is that it does that same instruction z times instead of just once.

 

[XrayLemi]

Bill Here is the complete section of final code. This will explain why I need it where it is.

Part one is for rows 6 - 4999. It only unlocks the next blank row.

Dim p As Range, z As Range
     Set p = Range("N6:N4999")
     Set p = Intersect(Target, p)
     If Not p Is Nothing Then
   Application.EnableEvents = False
     For Each z In p
      Select Case True
       Case 14 = z.Column 'N
        If z.Value <> "" Then
         Check = MsgBox("Are your entries correct?" & vbCrLf & "After entering yes, These values CANNOT be changed.", vbYesNo + vbQuestion, "Cell Lock Notification")
            If Check = vbYes Then
             Target.Rows.EntireRow.Locked = True
             Cells(z.Row + 1, "B").Locked = False
             Cells(z.Row + 1, "C").Locked = False
             Cells(z.Row + 1, "D").Locked = False
             Cells(z.Row + 1, "E").Locked = False
             Cells(z.Row + 1, "F").Locked = False
             Cells(z.Row + 1, "G").Locked = False
             Cells(z.Row + 1, "I").Locked = False
             Cells(z.Row + 1, "J").Locked = False
             Cells(z.Row + 1, "K").Locked = False
             Cells(z.Row + 1, "M").Locked = False
             If Cells(z.Row, "R").Value <> "" Then Copyemail 'R
             If Cells(z.Row, "S").Value <> "" Then ThisWorkbook.Save 'S
             With Me
                .Parent.Activate
                .Activate
                .Range("B" & Rows.Count).End(xlUp).Offset(1).Activate
            End With
            Else
             Cells(z.Row, "N").Value = ""
           End If
          End If
      Case Else
    End Select
   Next z
 End If

This is the final part where I needed the help.

 Dim e As Range, y As Range
    Set e = Range("N5000")
    Set e = Intersect(Target, e)
    If Not e Is Nothing Then
   Application.EnableEvents = False
     For Each y In e
      Select Case True
       Case 14 = y.Column 'N
        If y.Value <> "" Then
         Check = MsgBox("Are your entries correct?" & vbCrLf & "After entering yes, These values CANNOT be changed.", vbYesNo + vbQuestion, "Cell Lock Notification")
           If Check = vbYes Then
             Target.Rows.EntireRow.Locked = True
             If Cells(y.Row, "R").Value <> "" Then Copyemail 'R
             If Cells(y.Row, "S").Value <> "" Then ThisWorkbook.Save 'S
             Sheets("Book 2").Range("B6:G6").Locked = False
             Sheets("Book 2").Range("I6:K6").Locked = False
             Sheets("Book 2").Range("M6").Locked = False
             Sheets("Book 2").Select
             Else
             Cells(y.Row, "N").Value = ""
           End If
        End If
      Case Else
    End Select
   Next y
 End If

Since it is only for Row 5000, I added an additional set of code for line 5000 only. After the data in row 5000 is entered, AND, The user selects YES in a message box, The final row, (5000), and the entire sheet is locked. The next sheet is selected and unlocked with the code you gave me. But, only after the user selects yes in the message box. If I put it after that point, The next sheet would unlock regardless of a Yes / No answer.

This works PERFECT!
Thank you again for the help.
Jim