• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lock Previous cells

rahuldev31

New Member
lets say, If copy a cell H9 using shortcut CTRL+C, then it need to lock cells H4:H8 from being copied using shortcut. same if i copy next cell H10, then cells H5:H9 should be locked. How can i implement this without error showing?
 
Off the top of my head, what you want is a Copy event for a range. Set up a subroutine that executes automatically when you copy from a cell; the subroutine locks the surrounding cells, and then ends, and you can go on to paste the cell somewhere else.

But is there a copy event? I looked in the documentation, and I don't see any events for a Range object. Come to think of it, when I trigger a subroutine for action on a cell (it's the Delete action), I run it as a worksheet event, not a range event. Ok, is there a Copy event for a worksheet object?

I don't see that either. Seems to me you're going to have to fiddle around a little. Let's see, what worksheet events are available that might do for what you want? BeforeDelete, BeforeDoubleClick, BeforeRightClick, Change...that's all I see that might help you. Suppose you set up a BeforeDoubleClick event routine; when you double-click on a cell it does the following:

1) Determine what cell you double clicked on, and perhaps decides that it's not in the range that you want to act on. (If not, exit without taking action.)

2) Lock the surrounding cells.

3) Copy the cell.

Can you make that work for you?
 
The following macro is set for Column A. You can edit the code to match Column H :

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rngProtect As Range
    Dim startRow As Long
    Dim endRow As Long

    ' Set the worksheet
    Set ws = Target.Worksheet

    ' Check if the change occurred in column I and the row is greater than or equal to 6
    If Target.Column = 1 And Target.Row >= 6 Then
        ' Calculate the range of the previous 5 cells directly above the selected cell
        startRow = Target.Row - 5
        endRow = Target.Row - 1
        Set rngProtect = ws.Range("A" & startRow & ":A" & endRow)

        ' Disable events to avoid recursion
        Application.EnableEvents = False

        ' Unprotect the sheet
        ws.Unprotect Password:="123" ' Replace "123" with your desired password

        ' Unlock all cells in the sheet
        ws.Cells.Locked = False

        ' Lock the cells in the specified range
        rngProtect.Locked = True

        ' Protect the sheet to enforce locking
        ws.Protect Password:="123", AllowSorting:=True, AllowFiltering:=True

        ' Re-enable events
        Application.EnableEvents = True
    End If
End Sub
 
Back
Top