Macro to clear unlocked cells in locked workbook

[kafka]

I have a workbook containing a fairly large number of cells for data entry. I would like to lock the workbook so that headings, etc. cannot be overwritten but unlock the data entry cells. I would then like to create a macro which clears data from the unlocked cells. The macro would select the entire workbook but only clear the data entry cells. i have found that this can't be done by simply recording a macro. Is this possible?

 

[dave3009]

You might try applying a defined name to these cells and then apply the clear function in VBA

 

[bstory84]

I agree with Dave, but to answer your question yes you can do this.

Try the following code below.

Sub clearLocked()
Dim rng As Range
Dim C As Variant
Set rng = Sheets("Sheet1").Range("A1:F5000") 'set your range area here
    For Each C In rng
        If C.Locked = False Then
            C.ClearContents
        Else
        End If
    Next C
End Sub

 

[grizz]

This should clear contents of all unlocked cells in all sheets put code in This Workbook

Sub ClearUnlockedCells() 
 
    Dim wks As Worksheet 
 
    For Each wks In ThisWorkbook.Worksheets 
        On Error Resume Next 
        wks.UsedRange.Value = vbNullString 
        Err.Clear: On Error Goto -1: On Error Goto 0 
    Next wks 
 
    Set wks = Nothing 
 
End Sub

You could insert a button and assign the macros to it

 

[__Steve__]

This should clear contents of all unlocked cells in all sheets put code in This Workbook

Sub ClearUnlockedCells() 
 
    Dim wks As Worksheet 
 
    For Each wks In ThisWorkbook.Worksheets 
        On Error Resume Next 
        wks.UsedRange.Value = vbNullString 
        Err.Clear: On Error Goto -1: On Error Goto 0 
    Next wks 
 
    Set wks = Nothing 
 
End Sub

You could insert a button and assign the macros to it

How could this macro be modified to allow merged cells to be cleared?

 

[j3dcv]

How could this macro be modified to allow merged cells to be cleared?

I used this - it is slow, so be patient- still fasted than doing it manually. Maybe someone could speed it up?

- Note: Replace SHEETNAME with the sheet you want to clear.

Sub MyDelete()

Dim rng As Range
Dim C As Variant


' Prevents screen refreshing.
Application.ScreenUpdating = False

' Clear contents from every unlocked cell that is not merged

Set rng = Sheets("SHEETNAME").Range("A1:b44") 'set your range area here
For Each C In rng
'
On Error GoTo mc

' Whoa! I'm stuck! this cell is merged and I can't unlock it!
' What should I do now?

If C.Locked = False Then
C.Activate

If C.mergedcells = True Then
ActiveSheet.Unprotect
C.MergeCells = False
C.ClearContents
Selection.Merge
ActiveSheet.Protect
End If

Else
C.ClearContents
End If
Next C

mc:
Resume Next



Range("a1").Select


' Prevents screen refreshing.
Application.ScreenUpdating = True

End Sub

 

[j3dcv]

Actually the code works without the "Else"

 

[lray801]

This should clear contents of all unlocked cells in all sheets put code in This Workbook

Sub ClearUnlockedCells() 
 
    Dim wks As Worksheet 
 
    For Each wks In ThisWorkbook.Worksheets 
        On Error Resume Next 
        wks.UsedRange.Value = vbNullString 
        Err.Clear: On Error Goto -1: On Error Goto 0 
    Next wks 
 
    Set wks = Nothing 
 
End Sub

You could insert a button and assign the macros to it

I know I'm pretty late to the party here, but this code seemed to work at one point but it's not anymore. The code is only working if the spreadsheet is unlocked but then it clears everything. Thoughts?

Thanks!

 

[Rick Rothstein]

I know I'm pretty late to the party here, but this code seemed to work at one point but it's not anymore. The code is only working if the spreadsheet is unlocked but then it clears everything. Thoughts?

I know the technique is "different", but this macro seems to work (whether the cells are merged or not)...

Sub ClearUnlockedCells()
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  Cells.Replace "*", "", SearchFormat:=True
  Application.FindFormat.Clear
End Sub

 

[kafka]

I know the technique is "different", but this macro seems to work (whether the cells are merged or not)...

Sub ClearUnlockedCells()
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  Cells.Replace "*", "", SearchFormat:=True
  Application.FindFormat.Clear
End Sub

My thread is a year old and I have forgotten to which macro I was referring. I have recently discovered a solution to my problem which doesn't require any additions to my macro. I simply unlock the cells I want the macro to clear (Format Cells) then lock the worksheet the (Protect Sheet). I can then simply run the macro which clears all the old data. The worksheet is locked so that the cursor can only be placed on an unlocked cell (i.e. nothing else on the worksheet can be changed).

Thanks everyone for your help.