clearing unlocked fields on a protected sheet

[Guest]

I know that my cmd button code can unlock a worksheet, then step through a
list of named ranges to clear, but is there a better way, say using "For
each...next" to step trhough each cell in a named range to test for the
"unlocked" cells, then clearing? Can you address each cell as a range in a
collection of ranges using For...each?

DM

 

[Guest]

Yes, there is an easy way to step through your range, e.g:
Dim CheckCell as Range

For Each CheckCell in Range("MyNamedRange").Cells
If CheckCell.Locked=False then CheckCell.ClearContents
Next CheckCell

 

[Doug Glancy]

Dick,

Do you mean something like this?

Sub test()
Dim cell As Range
Dim range_to_clear As Range

With ActiveSheet
Set range_to_clear = Union(.Range("test_range_1"),
..Range("test_range_2"))
End With
For Each cell In range_to_clear
If cell.Locked = False Then
cell.ClearContents
End If
Next cell
End Sub

hth,

Doug Glancy

 

[STEVE BELL]

Put this in your code
replace
MsgBox cel.Address
with your code

To clear the cel use
cel.ClearContents

It is not necessary to unprotect if you are only changing the
unprotected cells.
==========================
Dim cel As Range, rng As Range

Set rng = ActiveSheet.Range("A1:A10")

For Each cel In rng
If cel.Locked = False Then
MsgBox cel.Address
End If
Next
=============================

 

[Guest]

How do you deal with merged cells? When the indicated cell is merged with
another, an error occurs. Would it be better to use a Userform with text box
objects?

DM

 

[STEVE BELL]

This replaces the contents with ""
(looks blank, but isn't)
(works on column A through rows 10)

You're right - you can't clear merged cells.
But this works.

Dim x As Long

For x = 1 To 10
If Cells(x, 1).Locked = False Then
Cells(x, 1) = ""
End If
Next

 

[Doug Glancy]

Dick,

I think you can use "MergeArea.ClearContents" as below:

Sub test()
Dim cell As Range
Dim range_to_clear As Range

With ActiveSheet
Set range_to_clear = Union(.Range("test_range_1"),
..Range("test_range_2"))
End With
For Each cell In range_to_clear
If cell.Locked = False Then
cell.MergeArea.ClearContents
End If
Next cell
End Sub

hth,

Doug