Macro to clear unlocked cells in locked workbook

kafka

New Member
Joined
Jun 2, 2012
Messages
42
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
You might try applying a defined name to these cells and then apply the clear function in VBA
 
Upvote 0
I agree with Dave, but to answer your question yes you can do this.

Try the following code below.
Code:
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
 
Upvote 0
This should clear contents of all unlocked cells in all sheets put code in This Workbook
Code:
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
 
Last edited:
Upvote 0
This should clear contents of all unlocked cells in all sheets put code in This Workbook
Code:
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?
 
Upvote 0
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
 
Upvote 0
This should clear contents of all unlocked cells in all sheets put code in This Workbook
Code:
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!
 
Upvote 0
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)...
Code:
Sub ClearUnlockedCells()
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  Cells.Replace "*", "", SearchFormat:=True
  Application.FindFormat.Clear
End Sub
 
Last edited:
Upvote 0
I know the technique is "different", but this macro seems to work (whether the cells are merged or not)...
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,211,851
Messages
6,104,362
Members
447,902
Latest member
chriswebs23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top