Clear.Contents does not work when Password protected for sheet

[chparkervatech]

Clear.Contents does not work when Password protected for sheet. Please help. Sheet's name is "Project SL Assessment Checklist".

Private Sub CommandButton1_Click()

Range("I47:K59").ClearContents
Range("G12:J42").ClearContents

ActiveSheet.Range("H12").Select

End Sub

[GeneralDisarray]

You need to unprotect the sheet before you clear the contents, then you can re-protect if you like.

Like:

Private Sub CommandButton1_Click()

With Sheets("Project SL Assessment Checklist")
    .Unprotect ("Your Password")
    .Range("I47:K59").ClearContents
    .Range("G12:J42").ClearContents
    .Protect ("Your Password")
End With

ActiveSheet.Range("H12").Select

End Sub

[chparkervatech]

This worked fine on another sheet that was password protected with only one clear contents script. Why does adding another clear.contents line (two) require you to modify the code as such?

[TMS]

Perhaps the cell is unlocked on one sheet and locked on the other.

[GeneralDisarray]

You can designate an area to allow edits when the sheet is protected. In general, the code can only clear or edit what the user can.

[chparkervatech]

I have the appropriate cells unlocked for their editing when protected. However, I think the issue may be because some of these cells (fields for input) are merged?

How do you account for merged cells in clear contents? For example, the first "field" (H12:I12) is merged and the sheet contains various "fields" / merged cells on down to G42:J42.

I assume there is a problem with the following? All cells in the merged range are unlocked.

Range("G12:J42").ClearContents

[teylyn]

This is where "does not work" is not good enough a description. What error do you get?

[chparkervatech]

Regardless, your solution worked. Thanks!

[teylyn]

If you protect the sheet with VBA and use the parameter UserInterfaceOnly:=True, then you can write into protected cells, too. Whether or not cells are merged does not matter.

The blue and the yellow cells in the screenshot are merged. All cells are locked.

2016-08-30_9-04-38.gif