Deleting all unprotected cells

[mahmed1]

Hi Guys,

I have a workbook that is protected and locked however certain cells are unlocked and are not protected (I.e where i can enter values) etc...

I am trying to add a btton that will clear the form (the values in the unprotected/locked cells).

What is the VBA code to delete all values in the unprotected/locked cells in the workbook?

Many Thanks

 

[Yard]

Try this then:

Sub ClearCells()
Dim rngLook As Range, rngC As Range, rngClear As Range
Dim wks As Worksheet

If MsgBox("Are you sure you want to clear the inputs?", vbYesNo, "Warning...") <> vbYes Then Exit Sub

For Each wks In ThisWorkbook.Worksheets
    Set rngLook = wks.UsedRange
    Set rngClear = Nothing
    
    For Each rngC In rngLook.Cells
        If rngC.Locked = False Then
            If rngClear Is Nothing Then
                Set rngClear = rngC
            Else
                Set rngClear = Union(rngClear, rngC)
            End If
        End If
    Next rngC
    
    If Not rngClear Is Nothing Then
        rngClear.ClearContents
    End If

Next wks

End Sub

 

[AlphaFrog]

This should loop through the already protected sheets and clear the unlocked cells only without looping through all the used cells one at a time on each sheet.

Sub Clear_UnLocked_Cells()

    Dim ws As Worksheet
    
    If MsgBox("Are you sure you want to clear the data?", vbYesNo, "Alert!") = vbYes Then

        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            If ws.ProtectContents Then
                On Error Resume Next
                ws.UsedRange = ""
                On Error GoTo 0
            End If
        Next ws
        Application.ScreenUpdating = True

    End If

End Sub

 

[Yard]

I like that.

 

[mahmed1]

This should loop through the already protected sheets and clear the unlocked cells only without looping through all the used cells one at a time on each sheet.

Sub Clear_UnLocked_Cells()
 
    Dim ws As Worksheet
 
    If MsgBox("Are you sure you want to clear the data?", vbYesNo, "Alert!") = vbYes Then
 
        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            If ws.ProtectContents Then
                On Error Resume Next
                ws.UsedRange = ""
                On Error GoTo 0
            End If
        Next ws
        Application.ScreenUpdating = True
 
    End If
 
End Sub

Hi Alpha,

where would i need to add the code to unprotect and protect the sheet on each sheet or don't i need to add this to the code (The reason i say this is because normally for me to run a code on a protected sheet i had to add this line of code)

i.e
ActiveSheet.Unprotect Password:="Hello1"

ActiveSheet.Protect Password:="Hello1", DrawingObjects:=True, Contents:=True, Userinterfaceonly:=True

Once everything is cleared i need to select Sheet named Team-Groups Cell B6 and have this blank and have that as the active cell at the end (I have data validation in B6, I.E (Blank, 10, 12, 15, 20 etc...)

Many Many Thanks

 

[AlphaFrog]

in this case, you don't need to unprotect the sheets. In fact, this method only works on clearing the unlocked cells when the sheet is protected.

 

[mahmed1]

in this case, you don't need to unprotect the sheets. In fact, this method only works on clearing the unlocked cells when the sheet is protected.

Thank You

Once everything is cleared i need to select Sheet named Team-Groups Cell B6 and have this blank and have that as the active cell at the end

(I have data validation in B6, I.E (Blank, 10, 12, 15, 20 etc...)

 

[AlphaFrog]

Sub Clear_UnLocked_Cells()
    
    Dim ws As Worksheet
    
    If MsgBox("Are you sure you want to clear the data?", vbYesNo, "Alert!") = vbYes Then
        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            If ws.ProtectContents Then
                On Error Resume Next
                ws.UsedRange = ""
                On Error GoTo 0
            End If
        Next ws
        [COLOR="Red"]Application.Goto Reference:=Worksheets("Team-Groups").Range("B6"), Scroll:=True[/COLOR]
        Application.ScreenUpdating = True
    End If
    
End Sub

 

[mahmed1]

Sub Clear_UnLocked_Cells()
 
    Dim ws As Worksheet
 
    If MsgBox("Are you sure you want to clear the data?", vbYesNo, "Alert!") = vbYes Then
        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            If ws.ProtectContents Then
                On Error Resume Next
                ws.UsedRange = ""
                On Error GoTo 0
            End If
        Next ws
        [COLOR=red]Application.Goto Reference:=Worksheets("Team-Groups").Range("B6"), Scroll:=True[/COLOR]
        Application.ScreenUpdating = True
    End If
 
End Sub

Hi Alpha Thank You so much

Another quick question, in this part of the code, which part makes the cell B6 = ""?

Application.Goto Reference:=Worksheets("Team-Groups").Range("B6"), Scroll:=True
This works fine however i need the active cell to be B6 which works fab however i need the scoll true to be in cell A1

 

[AlphaFrog]

If B6 is unlocked and on a protected sheet, it will be cleared with the others. If not, then this would...

Worksheets("Team-Groups").Range("B6").ClearContents

Scroll to A1 select B6

        Application.Goto Reference:=Worksheets("Team-Groups").Range("A1"), Scroll:=True
        Application.Goto Reference:=Worksheets("Team-Groups").Range("B6"), Scroll:=False
        Application.ScreenUpdating = True

 

[mahmed1]

If B6 is unlocked and on a protected sheet, it will be cleared with the others. If not, then this would...

Worksheets("Team-Groups").Range("B6").ClearContents

Scroll to A1 select B6

        Application.Goto Reference:=Worksheets("Team-Groups").Range("A1"), Scroll:=True
        Application.Goto Reference:=Worksheets("Team-Groups").Range("B6"), Scroll:=False
        Application.ScreenUpdating = True

Hi cells


<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message --> B6 is unlocked and on a protected sheet (A1 is protected) however needed the page to stay at a1 for viewing purposes

Thanks