Selecting only unlocked cells to clear
 

Hello,
Can anyone give me the VB code to select all the unlocked-only cells on a
page or in a defined range, to clear them?

Thanks,
Randy

Selecting only unlocked cells to clear
 

Hi Randy,

I am not aware of any method of selecting unlocked cells, so I would try
something like:

''===========================
Public Sub ClearUnlockedCells()
Dim rcell As Range
Dim rng As Range
Dim CalcMode As Long

Set rng = ActiveSheet.UsedRange '<<======== CHANGE

With Application
.ScreenUpdating = False
CalcMode = .Calculation
.Calculation = xlManual
End With

For Each rcell In rng.Cells
If rcell.Locked = False Then
rcell.ClearContents
End If
Next rcell

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub
'<<===========================


---
Regards,
Norman



"RAP" wrote in message
...
Hello,
Can anyone give me the VB code to select all the unlocked-only cells on a
page or in a defined range, to clear them?

Thanks,
Randy

Selecting only unlocked cells to clear
 

Randy, here is one way,

Sub Clear_Unlocked_Cells()
Dim Cel As Range
Const Password = "123" '**Change password here**
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=Password
For Each Cel In Range("A1:N25")
If Cel.Locked = False Then Cel.Formula = ""
Next
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"RAP" wrote in message
...
Hello,
Can anyone give me the VB code to select all the unlocked-only cells on a
page or in a defined range, to clear them?

Thanks,
Randy

Selecting only unlocked cells to clear
 

Hi Paul,

Can you not clear unlocked cells without unprotecting / reprotecting the
worksheet?


---
Regards,
Norman



"Paul B" wrote in message
...
Randy, here is one way,

Sub Clear_Unlocked_Cells()
Dim Cel As Range
Const Password = "123" '**Change password here**
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=Password
For Each Cel In Range("A1:N25")
If Cel.Locked = False Then Cel.Formula = ""
Next
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"RAP" wrote in message
...
Hello,
Can anyone give me the VB code to select all the unlocked-only cells on a
page or in a defined range, to clear them?

Thanks,
Randy

Selecting only unlocked cells to clear
 

RAP

You need to test the Locked property of the cells, e.g.

For Each cell in Range
If cell.Locked = False Then
cell.ClearContents
End If
Next cell
--
HTH

Simon


"RAP" wrote:

Hello,
Can anyone give me the VB code to select all the unlocked-only cells on a
page or in a defined range, to clear them?

Thanks,
Randy

Selecting only unlocked cells to clear
 

Norman, my bad, yes you can do it, must have been to early in the morning
:)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Norman Jones" wrote in message
...
Hi Paul,

Can you not clear unlocked cells without unprotecting / reprotecting the
worksheet?


---
Regards,
Norman



"Paul B" wrote in message
...
Randy, here is one way,

Sub Clear_Unlocked_Cells()
Dim Cel As Range
Const Password = "123" '**Change password here**
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=Password
For Each Cel In Range("A1:N25")
If Cel.Locked = False Then Cel.Formula = ""
Next
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"RAP" wrote in message
...
Hello,
Can anyone give me the VB code to select all the unlocked-only cells on
a
page or in a defined range, to clear them?

Thanks,
Randy