clearing unlocked fields on a protected sheet
 

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

clearing unlocked fields on a protected sheet
 

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


"Dick Minter" wrote:

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

clearing unlocked fields on a protected sheet
 

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

"Dick Minter" wrote in message
...
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

clearing unlocked fields on a protected sheet
 

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
=============================

--
steveB

Remove "AYN" from email to respond
"Dick Minter" wrote in message
...
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

clearing unlocked fields on a protected sheet
 

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" wrote:

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
=============================

--
steveB

Remove "AYN" from email to respond
"Dick Minter" wrote in message
...
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

clearing unlocked fields on a protected sheet
 

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


--
steveB

Remove "AYN" from email to respond
"Dick Minter" wrote in message
...
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" wrote:

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
=============================

--
steveB

Remove "AYN" from email to respond
"Dick Minter" wrote in message
...
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

clearing unlocked fields on a protected sheet
 

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

"Dick Minter" wrote in message
...
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" wrote:

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
=============================

--
steveB

Remove "AYN" from email to respond
"Dick Minter" wrote in message
...
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