Clear all unlocked cells (protected sheets)

[keessieb]

In a workbook with several protected sheets, I would like to give an option for the user to clear all not protected cells. Just a button on sheet one what says "clear all". Can it be done? (all not protected cells are yellow, is there a way to say "clear all yellow cells?)"

Thanks in advance!

 

[Iridium]

This should get you started:

http://j-walk.com/ss/excel/tips/tip99.htm

A bit of a tweak here and there and you're sorted!

HTH

 

[parry]

Heres a version specific to your requirements. Note this only works on the current active sheet (not all sheets). If you dont know how to add a button or add the code post again

Sub ClearUnlockedCells()
    Dim WorkRange As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.ClearContents
    Next Cell
End Sub

hth

 

[keessieb]

Okay, let's get started! Thanks!

 

[keessieb]

Help! Parry's code doesn't seem to work with merged cells! Can this be solved?

 

[keessieb]

And another question: to use the same code for all worksheets, do I have to put the code in every sheet or can I use the code from one place? (Every sheet a button, button get's the code from one place)

 

[parry]

mmm interesting that clearcontents doesnt work with merged cells. Nevermind, heres an alternative...

Clear contents in just the active sheet

Sub ClearUnlockedCells()
    Dim WorkRange As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell
End Sub

Clear contents in all sheets

Sub ClearUnlockedAllSheets()
Dim Cell As Range, Sht As Worksheet
  
For Each Sht In Worksheets
    For Each Cell In Sht.UsedRange
    If Cell.Locked = False Then Cell.Value = ""
    Next Cell
Next Sht
End Sub

 

[keessieb]

Thanks a lot, this will make it much easier for the users!

Have a nice day.

 

[airborne4905]

This works great! I just need to add 1 thing to this which I have on another workbook. I need to advance the Date of cell H1 +7 days on the first sheet only. Below is the line that I stuck in another macro that I have but I tried to put it in this one and it doesn't work the same.


Worksheets("KENNETH-HUBERT").Range("H1").Value = Worksheets("KENNETH-HUBERT").Range("H1").Value + 7

 

[gkairu]

mmm interesting that clearcontents doesnt work with merged cells. Nevermind, heres an alternative...

Clear contents in just the active sheet

Sub ClearUnlockedCells()
    Dim WorkRange As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell
End Sub

Clear contents in all sheets

Sub ClearUnlockedAllSheets()
Dim Cell As Range, Sht As Worksheet
 
For Each Sht In Worksheets
    For Each Cell In Sht.UsedRange
    If Cell.Locked = False Then Cell.Value = ""
    Next Cell
Next Sht
End Sub

Is it possible to change the values in the unlocked cells to 0(zero) when the button is clicked?