Protect Three Columns
Results 1 to 4 of 4

Thread: Protect Three Columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2006
    Posts
    166
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Protect Three Columns

    Hello,

    I would like to protect 3 columns using VBA. Protection should go as long as there is data.

    I measure the end of my data in column A and to find the last row I use:

    Code:
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Now, to protect 3 columns I used this code, but it doesn't work.
    Code:
    ws.Unprotect Password:="123"
    ws.Cells.Locked = False
    ws.Range("C" & LastRow).Locked = True
    ws.Range("D" & LastRow).Locked = True
    ws.Range("E" & LastRow).Locked = True
    ws.Protect Password:="123", UserInterFaceOnly:=True
    I appreciate any help on this.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,672
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Protect Three Columns

    Quote Originally Posted by barim View Post
    Code:
    ws.Range("C" & LastRow).Locked = True
    Hi

    You are locking the last cell in column C.
    Don't you want to lock all the cells above too?
    Last edited by pgc01; Aug 8th, 2019 at 05:53 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Apr 2006
    Posts
    166
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Protect Three Columns

    Thanks for the response pgc01, yes, I would like to lock all the cells above. Actually, I found a solution by locking the whole column,

    Code:
    Columns("C").Locked = True
    I would really like to lock only where data ends. What would be solution to that?

    Thanks again for your response.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,672
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Protect Three Columns

    Try:

    Code:
    Sub lockSomeCells()
    Dim ws As Worksheet
    Dim LastRow As Long
    
    Set ws = Worksheets("Sheet1")
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ws.Unprotect Password:="123"
    ws.Cells.Locked = False
    ws.Range("C1:E" & LastRow).Locked = True
    ws.Protect Password:="123", UserInterFaceOnly:=True
    
    End Sub
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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