CODEWITHSUNDEEP
vbaexcel
vivek padelkar
vivek padelkar

Reputation: 313

how to lock in between rows in excel vba

I want to lock the in between row's of excel sheet depending the value of the two column's , I have following code with me but it's makes entire sheet protected.
the code is :

there is another problem when the loop goes to else part it throws "unable to set Locked property of the range class" the code is :

Do While xlsht.Cells(i, 1) <> vbNullString
            If (CStr(xlsht.Cells(i, 54).Value) <> "" And (CStr(Format(xlsht.Cells(i, 55).Value, "dd-MMM-yyyy")) = CStr(Format(Now, "dd-MMM-yyyy")))) Then
                    .Cells.Locked = False
                    .Range("A" & i & " : " & "BH" & i).Cells.Locked = True
                    .Range("A" & i & " : " & "BH" & i).Interior.Color = RGB(255, 255, 0)
                    .Protect Password:=admin
            Else
                    .Cells.Locked = False
                    .Range("A" & i & " : " & "AC" & i).Cells.Locked = True
                    .Range("AE" & i & " : " & "AT" & i).Cells.Locked = True
                    .Range("BB" & i & " : " & "BH" & i).Cells.Locked = True
                    .Protect Password:=admin
            End If
                i = i + 1
            Loop

    End With

Upvotes: 2

Views: 899

Answers (2)

R3uK
R3uK

Reputation: 14537

By default, the entire sheet is Locked (property of a Range or Cell).

And you can only Protect an ENTIRE sheet.

So you'll have to unLock the rest of the sheet first!

i = 1
With xlsht
    .Unprotect Password:=admin
    .Cells.Locked = False
    Do While xlsht(i, 1) <> vbNullString
       If .Cells(i, 54).Values = "abc" And .Cells(i, 55).Values = "def" Then
           'here is checking the column depends the row is get lock or not
           .Range("A" & i & ":BH" & i).Cells.Locked = True
           i = i + 1
       End If
    Loop
    .Protect Password:=admin
End With 'xlsht

Second question

i = 1
With xlsht
    .Unprotect Password:=admin
    .Cells.Locked = False
    Do While .Cells(i, 1).Value <> vbNullString
        If CStr(.Cells(i, 54).Value) <> vbNullString And CDate(.Cells(i, 55).Value) = Date Then
            With .Range("A" & i & ":BH" & i)
                .Cells.Locked = True
                .Interior.Color = RGB(255, 255, 0)
            End With '.Range("A" & i & ":BH" & i)
        Else
            .Range("A" & i & ":AC" & i).Cells.Locked = True
            .Range("AE" & i & ":AT" & i).Cells.Locked = True
            .Range("BB" & i & ":BH" & i).Cells.Locked = True
        End If
        i = i + 1
    Loop
    .Protect Password:=admin
End With 'xlsht

Upvotes: 2

user3598756
user3598756

Reputation: 29421

you may be after something like this:

    Dim i As Long

    i = 1
    With Worksheets("mySheetName") '<--| change "mySheetName" to your actual sheet name
        Do While .Cells(i, 1) <> ""
            If (.Cells(i, 54).Value = "abc" And .Cells(i, 55).Value = "def") Then Intersect(.Range("A:BH"), .Rows(i)).Locked = True
        i = i + 1
        Loop
        .Protect Password:="admin"
    End With

Upvotes: 4

Related Questions