Clear unlocked cells

[Noz2k]

Probably doing something really obvious wrong, but all I want to do is clear the contents of all unlocked cells in a worksheet when the worksheet is activated

here is the code I have which is giving me an error

Private Sub Worksheet_Activate()
Sheets("Sheet1").Unprotect "password"
Dim r   As Range
Set r = Sheets("Sheet1").Range("E3:K25")
For Each Cell In r
If Cell.Locked = False Then
Cell.ClearContents
End If
Next
Sheets("Sheet1").Protect "password"
End Sub

Where am I going wrong?

 

[kpark91]

It's probably not this but just a little minor stuff:
Dim cell As Range.
Use Worksheets("Sheet1") instead of Sheets("Sheet1")

 

[Noz2k]

Thanks, but unfortunately no difference

 

[kpark91]

What kind of error is it giving and where is it giving?

 

[Noz2k]

Application Defined or Object Defined error.

The sheet name is definitely correct.

Just realised that the reason why is because I am using merged cells, which always complicate things.

Any suggestions on how to deal with this,

uses .mergearea or something doesn't it?

 

[Noz2k]

Got it going

Worksheets("Sheet1").Unprotect "password"
Dim cl As Range
 For Each cl In Sheets("Sheet1").Range("E3:K25").Cells
                            If cl.Locked = False Then
                                If cl.MergeCells = True Then
                                    Set clMerge = Range(cl.Address).MergeArea
                                    clMerge.ClearContents
                                Else
                                    cl.ClearContents
                                End If
                            End If
                                Next cl
Worksheets("Sheet1").Protect "password"

Thanks for the help

 

[kpark91]

Oic.
Forgot we had to use .Cells for looping xD

Thanks for coming back and putting up the working code.

 

[Noz2k]

I try to always post up the working code just in case somebody has a similar problem. I know how many I have been able to solve just by googling and then adapting somebody elses solution to match my criteria.

Plus people are helping for free, so it's important to thank them.