Results 1 to 11 of 11

vba to lock the entire row

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    vba to lock the entire row

    Hi,

    Below is the code which update the timestamp in the adjustment column when the value is entered in C or E column.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("C:C"), Target)
    xOffsetColumn = -1
    
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
            If Not VBA.IsEmpty(Rng.Value) Then
                Rng.Offset(0, xOffsetColumn).Value = Now
                Rng.Offset(0, xOffsetColumn).NumberFormat = "hh:mm:ss"
            Else
                Rng.Offset(0, xOffsetColumn).ClearContents
            End If
             Next
        Application.EnableEvents = True
    
    End If
    
      Dim xCellColumn As Integer
    Dim xTimeColumn As Integer
    Dim xRow, xCol As Integer
    xCellColumn = 5   ' a column where cell value is changed
    xTimeColumn = 6    ' a column where time stamp is updated
    xRow = Target.Row
    xCol = Target.Column
    If Target.Text <> "" Then
        If xCol = xCellColumn Then
           Cells(xRow, xTimeColumn) = Now()
           End If
         
         End Sub
    Now my last step is to lock the entire row when E column is updated or lock all filled cell. So next user update on the the next cell/row which is not updated. I tried lot of option, but my entire sheet is getting locked instead just a row.
    Last edited by shiva_reshs; 08-22-2018 at 07:01 AM.
    Keep the Forum clean :


    1. Use [ code ] code tags [ /code ]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. Show appreciation to those who have helped you by clicking * Add Reputation below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Lock entire Row if Value in Col A = X
    By walsha23 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-31-2017, 07:00 AM
  2. Lock entire Row if Value in Col A = X
    By walsha23 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-26-2017, 10:38 AM
  3. it is possible to lock entire ROW
    By baig123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2014, 05:24 AM
  4. [SOLVED] Lock entire column if data has been entered in it
    By bassterro in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-01-2014, 03:25 PM
  5. [SOLVED] Lock an entire row
    By kid_epicurus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-20-2012, 12:33 PM
  6. Replies: 13
    Last Post: 03-13-2012, 06:24 AM
  7. How do change entire worksheet to uppercase letters and lock it
    By aeromutt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2006, 12:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1