vba code

[CandiC]

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 H]

Hi,

Here's one way

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


Mike

 

[Gord Dibben]

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

 

[Gord Dibben]

Very neat!


Gord

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]

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