Microsoft Office Tutorials and References
In Depth Information
Deleting the Contents of Unlocked Cells
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub ClearUnlockedCells()¶
'Variable declaration¶
Dim Rng As Range, UnlockedRng As Range, Cll As Range¶
Dim Sht As Worksheet¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
'Loop through all the worksheets in the active workbook¶
For Each Sht In ActiveWorkbook.Worksheets¶
'Do it if the sheet is protected¶
If Sht.ProtectContents Then¶
On Error Resume Next¶
'Delete the previous range¶
Set Rng = Nothing¶
Set UnlockedRng = Nothing¶
'Get the used range in the worksheet¶
Set Rng = Sht.UsedRange¶
'Are there any?¶
If Not Rng Is Nothing Then¶
'See which cells are locked¶
For Each Cll In Rng.Cells¶
If Not Cll.HasFormula Then¶
'check for formula in cell¶
If Not Cll.Locked Then¶
'check for locked cell¶
If UnlockedRng Is Nothing Then¶
Set UnlockedRng = Cll¶
Else¶
Set UnlockedRng = Union(Cll, UnlockedRng)¶
End If¶
End If¶
End If¶
Next Cll¶
End If¶
'Clear it if something is there¶
If Not UnlockedRng Is Nothing Then¶
'Not UnlockedRng Is Nothing is code for "UnlockedRng¶
'has something in it"¶
UnlockedRng.ClearContents¶
End If¶
End If¶
Next Sht¶
'Restore screen updating¶
Application.ScreenUpdating = True¶
End Sub¶
Exl
Search JabSto ::




Custom Search