• 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.

Error Code Debug Advice - Run-time error 1004: Unable to set Locked property of Range class

Opulence12

New Member
Hi All,

I am very new to VBA and I am trying to figure out the VBA code in excel but i encountered the error code(Run-time error 1004: Unable to set Locked property of Range class) after I protected the sheet after inserting the code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("G106").Value = "N.A." Then

Range("J106:K106").Locked = True

Else

Range("J106:K106").Locked = False

End If

End Sub
When i pressed debug, the error highlighted was this: Range("J106:K106").Locked = True
However, they are not merged cells so I do not know what I did wrong. These 2 cells (J106:K106) are 2 separate cells with drop-down lists using data validation. However, if G106 shows the value "N.A.", the cells (J106:K106) are to be locked and prevented from entering the value.

I actually intended to use this VBA code from cells G100 to G106 to link to J100 to K106 respectively.

Please help!
 
Last edited by a moderator:

vbacoder1962

New Member
My dear, you should first unprotect the sheet, put the data and then protect it again, all through code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.UnProtect <Password goes here>
    If Range("G106").Value = "N.A." Then
          Range("J106:K106").Locked = True
    Else
          Range("J106:K106").Locked = False
    End If
     ActiveSheet.Protect <Password goes here>, <password goes here 2nd time>
End Sub
 

Opulence12

New Member
My dear, you should first unprotect the sheet, put the data and then protect it again, all through code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.UnProtect <Password goes here>
    If Range("G106").Value = "N.A." Then
          Range("J106:K106").Locked = True
    Else
          Range("J106:K106").Locked = False
    End If
     ActiveSheet.Protect <Password goes here>, <password goes here 2nd time>
End Sub
hey there, thank you for replying! I encountered another error - 'Syntax Error'.
 

vletm

Excel Ninja
Opulence12
What else do You have there?
What kind of 'protection' do You would like to set?
= Have You set any protection-options?
... below code works ... without errors ...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect
    JK = False
    If Range("G106") = "N.A." Then JK = True
    Range("J106:K106").Locked = JK
    ActiveSheet.Protect
End Sub
... is it usable to swap 'Locked' after every change of Worksheet?
 
Top