does anyone know if it is possible to write a macro to clear all unlocked
cells by the click of a button on a user form?

Hi,

Here's one way

Sub ClearCells()
ActiveSheet.Protect
On Error Resume Next
ActiveSheet.UsedRange = ""
On Error GoTo 0
ActiveSheet.Unprotect
End Sub


Mike

"CandiC" wrote:

does anyone know if it is possible to write a macro to clear all unlocked
cells by the click of a button on a user form?

To select unlocked cells and clearcontents.

Sub UnLocked_Cells()
Bob Flanagan code
Dim Cell As Range, tempR As Range, rangeToCheck As Range
'rotate through all cells in the selection
For Each Cell In ActiveSheet.UsedRange 'or a range or selection
If Not Cell.Locked Then
'add to tempR if unprotected
If tempR Is Nothing Then
'do it this way for the first unprotected cell
Set tempR = Cell
Else
'add subsequent cells this way.
Set tempR = Union(tempR, Cell)
End If
End If
Next Cell
'do this upon completion of the For..Next checking
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If

'select qualifying cells
tempR.ClearContents
End Sub


Gord Dibben MS Excel MVP

On Mon, 2 Nov 2009 11:33:01 -0800, CandiC
wrote:

does anyone know if it is possible to write a macro to clear all unlocked
cells by the click of a button on a user form?

Very neat!


Gord


On Mon, 2 Nov 2009 11:41:02 -0800, Mike H
wrote:

Hi,

Here's one way

Sub ClearCells()
ActiveSheet.Protect
On Error Resume Next
ActiveSheet.UsedRange = ""
On Error GoTo 0
ActiveSheet.Unprotect
End Sub


Mike

"CandiC" wrote:

does anyone know if it is possible to write a macro to clear all unlocked
cells by the click of a button on a user form?

Mike,

Thanks for the information, this doesn't seem to work, am I supposed to put
the name of the worksheet between the parenthesis after ClearCells? I am very
new at macros and the language, so any help would be further appreciated.
Candi C

"Mike H" wrote:

Hi,

Here's one way

Sub ClearCells()
ActiveSheet.Protect
On Error Resume Next
ActiveSheet.UsedRange = ""
On Error GoTo 0
ActiveSheet.Unprotect
End Sub


Mike

"CandiC" wrote:

does anyone know if it is possible to write a macro to clear all unlocked
cells by the click of a button on a user form?