Forum Discussion

Christina333's avatar
Christina333
Copper Contributor
May 17, 2021

Lock cell without protecting worksheet

Hi all,    Hope everyone is doing well there.  I would like to seek advise from experts here on how to 'lock certain cells without protecting the worksheet.' Is it possible to do so?    My compa...
excel
ajitjosh's avatar
ajitjosh
Copper Contributor
May 17, 2023

Hi There, This works perfectly, I have a question, How do i make it work in a macro when i need to copy the same range of cells to a few lines below?

can you please help,

Thanks

email address removed for privacy reasons

HansVogelaar's avatar
HansVogelaar
MVP
May 17, 2023

ajitjosh 

This is a long discussion. Which code are you referring to?

  • ajitjosh's avatar
    ajitjosh
    Copper Contributor
    May 18, 2023

    Hi Hans,

    we are talking about the locking of cell, or a range of cells ( for a formula as an example) without protecting the sheet,

    Please  advise how to deal with this,

    Thanks HansVogelaar 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 18, 2023

      ajitjosh 

      In the code

       

       

      Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2,C2,A4,C4"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

       

       

      you can change the range "A2,C2,A4,C4" to refer to the entire range or set of ranges that you want to "lock", for example:

       

          If Not Intersect(Range("D2:F50, H2:K50"), Target) Is Nothing Then

       

      • ajitjosh's avatar
        ajitjosh
        Copper Contributor
        May 18, 2023
        Ok, Will try that and revert, Many Thanks for the quick turn around..

Resources