VBA not working with Protected Sheet

[Don Fardie]

I have the VBA coded onto sheet1 of my workbook to clear contents, but as soon as I protect the sheet I get run errors. Have seen posts where you add Unprotect and Protect with your code in between them, but it's not working. I can get it to run without the Run Error pop up, but cells do not clear as they do when the sheet has no protection.
This is the code I have and not sure what the fix is!....Thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C16:C34")) Is Nothing Then
Range("I" & Target.Row & ":S" & Target.Row).ClearContents
End If
Application.EnableEvents = True
Application.EnableEvents = False
If Not Intersect(Target, Range("C38:C56")) Is Nothing Then
Range("D" & Target.Row & ":P" & Target.Row).ClearContents
End If
Application.EnableEvents = True
End Sub

 

[Joe4]

If your sheet is protected (and cells locked), you will definitely need to unprotect it before clearing cells. I do not see anything in your code unprotecting the cells.
Also, are you trying to clear individual cells or multiple cells at once.

Also, not sure why you have these two rows in a row:

[COLOR=#333333]Application.EnableEvents = True[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]

The second one is the last one to process, so there is no point to the first one.

 

[Joe4]

I think your code needs to look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range

    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="password"

    Set rng = Intersect(Target, Range("C16:C34"))
    If Not rng Is Nothing Then
        For Each cell In rng
            Range("I" & cell.Row & ":S" & cell.Row).ClearContents
        Next cell
    End If

    Set rng = Intersect(Target, Range("C38:C56"))
    If Not rng Is Nothing Then
        For Each cell In rng
            Range("D" & cell.Row & ":P" & cell.Row).ClearContents
        Next cell
    End If
    
    ActiveSheet.Protect Password:="password"
    Application.EnableEvents = True
    
End Sub

Change "password" to your actual password (or remove that "Password:="password" argument altogether if you are protecting without a password).

 

[Don Fardie]

The code I posted above is shown without any Protect/ Unprotect being added. This is the code I entered when building the sheet. when I was done and protected the sheet I then realized the problems. To answer your questions on the 2 rows I wanted 2 cell range areas to be cleared across the rows. The upper range of cells is different from the one below, so that is why there are stacked codes. I was given help doing the first range of cells and then realized I needed another area to be cleared just below the upper range, so I added the second code. I'll be the first to admit I am new at this and this may not be the correct way of doing it, but it worked! Hope that answered your questions

 

[Joe4]

Did you try the code I put in my last reply (making any changes to "password")?

Your original quote would have a problem if your "Target" was more than one cell at a time. That is why I have the extra loop looping through the intersected range.

 

[Don Fardie]

That was it...Thank you so much. I was going crazy trying to figure that out!!!! Thanks again......Don

 

[Joe4]

You are welcome.
Glad I was able to help.