vba code

  • Thread starter Thread starter CandiC
  • Start date Start date
C

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?
 
Hi,

Here's one way

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


Mike
 
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
 
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
Click to expand...
 
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
 
Back
Top