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:

Sub ClearCells()
Dim rngLook As Range, rngC As Range, rngClear As Range
Set rngLook = ActiveSheet.UsedRange
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
End Sub

 

[mahmed1]

Try this:

Sub ClearCells()
Dim rngLook As Range, rngC As Range, rngClear As Range
Set rngLook = ActiveSheet.UsedRange
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
End Sub

Hi All

Sometimes you might accidentally click the button and clear the form. Can i have the code like in an input box giving an option yes or no saying "are you sure you want to clear the data"?

Thanks

 

[Yard]

Add this line after the Dim statement line

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

 

[mahmed1]

Try this:

Sub ClearCells()
Dim rngLook As Range, rngC As Range, rngClear As Range
Set rngLook = ActiveSheet.UsedRange
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
End Sub

Hi The code above doesn't delete the actual values that is in the unprotected cells.

It clears it it but when there is data it pulls through what was in there previously.

What i need is to delete anything in the unprotected/locked cells

Many Thanks

Any help from anyone is appreciated

 

[Yard]

Hello,
Can you explain what you mean please?

It clears it it but when there is data it pulls through what was in there previously.

The code loops through all cells in the active sheet's used range, and combines all the unlocked cells into one range, then clears the contents from that combined range.

What do you mean by "unprotected/locked cells"? A cell is either locked or unlocked, and a worksheet is either protected or unprotected.

 

[mahmed1]

Hello,
Can you explain what you mean please?

The code loops through all cells in the active sheet's used range, and combines all the unlocked cells into one range, then clears the contents from that combined range.

What do you mean by "unprotected/locked cells"? A cell is either locked or unlocked, and a worksheet is either protected or unprotected.

Hi,

I have data validation which where you can enter the number of teams etc...

Basically My worsheets are protected and cells are also locked other than certain cells where users can enter data/values (not locked). When the code runs it clears everything.

And leaves everything blank. Now say i select the number of teams. Certain cells where i have entered values come back through. i need it to delete all the values that was inputted in the unlocked cells.

 

[Yard]

I'm sorry, I just don't understand what you're telling me.

You asked for code which deletes values in the cells which are not locked. This code does that.

If you want something different, please explain in single commands what you want, for example:

1. delete values in all cells which are not locked
2. do something else
3. if x, do y, otherwise do z

Regards

 

[mahmed1]

I'm sorry, I just don't understand what you're telling me.

You asked for code which deletes values in the cells which are not locked. This code does that.

If you want something different, please explain in single commands what you want, for example:

1. delete values in all cells which are not locked
2. do something else
3. if x, do y, otherwise do z

Regards

Hi sorry if i'm not explaining myself properly

This is what i need
1. delete values in all cells which are not locked

 

[mahmed1]

Hi sorry if i'm not explaining myself properly

This is what i need
1. delete values in all cells which are not locked

Bump anyone

thank you