none
VBA for locking cells in a row on entering data in last column RRS feed

  • Question

  • Hi,

    I have an excel wherein different users need to enter data in different rows. For example one user will enter data in A2:G2. I am trying to lock cells A2:G2, when a user makes an entry into the last cell "G2", so the other users wont be able to delete the data. The next user then enters data in A3:G3 and so on. 

    Could anyone help me with the code for this.

    Thanks,

    Mayur

    Monday, March 5, 2018 7:18 AM

Answers

  • Before creating the code, make sure that the cells in columns A:G are not locked, and that the worksheet has been protected.

    Right-click the sheet tab.

    Select View Code from the context menu.

    Copy the following event procedure into the worksheet module.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("G:G"), Target) Is Nothing Then
            Me.Unprotect 'Password:="Secret"
            Intersect(Range("G:G"), Target).Offset(, -6).Resize(, 7).Locked = True
            Me.Protect 'Password:="Secret"
        End If
    End Sub

    If you protected the worksheet with a password, substitute that password into the code and remove the apostrophes in 'Password:="Secret

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mayur 9671 Monday, March 5, 2018 7:18 PM
    Monday, March 5, 2018 8:50 AM

All replies

  • Before creating the code, make sure that the cells in columns A:G are not locked, and that the worksheet has been protected.

    Right-click the sheet tab.

    Select View Code from the context menu.

    Copy the following event procedure into the worksheet module.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("G:G"), Target) Is Nothing Then
            Me.Unprotect 'Password:="Secret"
            Intersect(Range("G:G"), Target).Offset(, -6).Resize(, 7).Locked = True
            Me.Protect 'Password:="Secret"
        End If
    End Sub

    If you protected the worksheet with a password, substitute that password into the code and remove the apostrophes in 'Password:="Secret

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Mayur 9671 Monday, March 5, 2018 7:18 PM
    Monday, March 5, 2018 8:50 AM
  • Hi Mayur 9671,

    I'm not sure if I can understand what you want to achieve.

    I suppose you want to let users input one by one (one after another, one line and next line). 
    This is a sample code:
    Private Sub Worksheet_Activate()
        Application.ScreenUpdating = False
        ' ---
        Dim rowCnt As Long: rowCnt = Rows.Count
        Dim lastRow As Integer: lastRow = Cells(rowCnt, 1).End(xlUp).Row
        ' --- hiden rows
        Rows(CStr(lastRow) & ":999999").Hidden = True
        ' --- visible row
        Rows("1").Hidden = False
        Rows(lastRow + 1).Hidden = False
        Cells(lastRow + 1, 1).Select
        ' ---
        ActiveWindow.ScrollRow = lastRow + 1
        ActiveWindow.FreezePanes = True
        ' ---
        Application.ScreenUpdating = True
    End Sub

    Regards,

    Ashidacchi >> http://hokusosha.com/

    Monday, March 5, 2018 12:00 PM
  • That exactly does what I needed!

    Thank you Hans

    Monday, March 5, 2018 7:19 PM
  • I just wanted to protect the row after the entry is made in column G so that another user cannot modify the data.

    Thanks Ashidacchi

    Monday, March 5, 2018 7:22 PM
  • Hi Mayur 9671,
    Congratulations on getting answer.  I had a good time to think algorithm and make code.
    Regards,

    Ashidacchi >> http://hokusosha.com/

    Monday, March 5, 2018 10:46 PM