Clearing unlocked cell contents macro

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

HYCH

Would like to run a macro with the following for several pages, but
only want to clear contents of unlocked cell within the worksheet,

rather than scrolling through and doing it manually?

any help with this would be wonderful

Steve
 
Try this one. Note that the sheet has to be unprotected to use the
SpecialCells function.

Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell As Range
Dim pWord As String

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "NotThisSheet" Then
sh.Unprotect Password:=pWord
For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
If cell.Locked = False Then
cell.Clear
End If
Next
sh.Protect Password:=pWord
End If
Next
End Sub

Regards,
Per
 
Try this one. Note that the sheet has to be unprotected to use the
SpecialCells function.

Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell As Range
Dim pWord As String

For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "NotThisSheet" Then
        sh.Unprotect Password:=pWord
        For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
            If cell.Locked = False Then
                cell.Clear
            End If
        Next
        sh.Protect Password:=pWord
    End If
Next
End Sub

Regards,
Per
Click to expand...



Per, thank you very much for your assistance, the sheets are
protected, but without password, so can cover this aspect with simply
unprotecting the sheet and re applying after this macro has run, have
22 worksheets, but only need to clear data in the first 7,

as i read your code please excuse me if i am wrong but this would
unprotect every sheet?

would i need to adjust the code to just unprotect Sheet1:Sheet7

Just some clarification

Again many thanks

Steve
 
Per, thank you very much for your assistance, the sheets are
protected, but without password, so can cover this aspect with simply
unprotecting the sheet and re applying after this macro has run, have
22 worksheets, but only need to clear data in the first 7,

as i read your code please excuse me if i am wrong but this would
unprotect every sheet?

would i need to adjust the code to just unprotect Sheet1:Sheet7

Just some clarification

Again many thanks

Steve- Hide quoted text -

- Show quoted text
Click to expand...



have used the code below that was provided for me by pers, but am
getting an error message

Run time error '1004'
No cells were found


There are actually unlocked cells in the spreadsheets with information
in them that was expecting to be cleared

any help with this please
am using office 2003

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("w96").Value <> 0 Then
Dim sh As Worksheet
Dim cell As Range
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "wages sheet" Then
sh.Unprotect
For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
If cell.Locked = False Then
cell.Clear
End If
Next
sh.Protect
End If
Next
End If
End Sub


Steve
 
There has to be a sheet with no information to delete. I have added an
error check.

As you asked in your previous post, I have changed the loop, so the
macro loop through Sheet1:Sheet7

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("w96").Value <> 0 Then
Dim sh As Worksheet
Dim cell As Range
Dim c

For s = 1 To 7
Set sh = Worksheets("Sheet" & s)
sh.Unprotect
On Error Resume Next
c = sh.Cells.SpecialCells(xlCellTypeConstants).Count
If Err.Number = 0 Then
For Each cell In
sh.Cells.SpecialCells(xlCellTypeConstants)

If cell.Locked = False Then
cell.Clear
End If
Next
sh.Protect
End If
On Error GoTo 0
Next
End If
End Sub

Regards,
Per
 
There has to be a sheet with no information to delete. I have added an
error check.

As you asked in your previous post, I have changed the loop, so the
macro loop through Sheet1:Sheet7

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("w96").Value <> 0 Then
    Dim sh As Worksheet
    Dim cell As Range
    Dim c

    For s = 1 To 7
        Set sh = Worksheets("Sheet" & s)
        sh.Unprotect
        On Error Resume Next
        c = sh.Cells.SpecialCells(xlCellTypeConstants).Count
        If Err.Number = 0 Then
            For Each cell In
sh.Cells.SpecialCells(xlCellTypeConstants)

                If cell.Locked = False Then
                    cell.Clear
                End If
            Next
            sh.Protect
        End If
        On Error GoTo 0
    Next
End If
End Sub

Regards,
Per










- Show quoted text -
Click to expand...

Per,

Your an absolute star, many thanks for your assistance

works perfectly

Steve
 
Back
Top