VBA to clear cell contents and conditional formatting but retain the formula

[MLC1980]

Hi

I have a 'Reset' button on my Excel worksheet that uses the following VBA:

Sub ResetSPLPlanner()
'
' ResetSPLPlanner Macro
'

'
    ActiveSheet.Protect UserInterFaceOnly:=True
    Range("D13:E64").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    Range("H1:H2").Select
    Selection.ClearContents
    
    End With
    Range("A1").Select
End Sub

When I first wrote the above code, the cells that I was clearing did not contain any formulas so it worked fine. However, I have now added some formula to cells D13:D64. It's worth noting that apart from the new formula to cells D13:D64, the worksheet has all the other formula cells within the worksheet locked (I do not have the formula in cells D13:D64 locked because the user still needs to be able to overwrite it).

What I need the Reset button to do now is:
Allow the VBA to turn on a protected worksheet
For Cells D13:D64 - clear the content whilst retaining the formula, set the fill colour to xlNone
For Cells E13:E64 - clear the content and set the fill colour to xlNone
For Cells H1:H2 - clear the contents

Thanks

 

[MLC1980]

Just posting again to correct a spelling mistake

Hi

I have a 'Reset' button on my Excel worksheet that uses the following VBA:

Sub ResetSPLPlanner()
'
' ResetSPLPlanner Macro
'

'
    ActiveSheet.Protect UserInterFaceOnly:=True
    Range("D13:E64").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    Range("H1:H2").Select
    Selection.ClearContents
   
    End With
    Range("A1").Select
End Sub

When I first wrote the above code, the cells that I was clearing did not contain any formulas so it worked fine. However, I have now added some formula to cells D13:D64. It's worth noting that apart from the new formula to cells D13:D64, the worksheet has all the other formula cells within the worksheet locked (I do not have the formula in cells D13:D64 locked because the user still needs to be able to overwrite it).

What I need the Reset button to do now is:
Allow the VBA to run on a protected worksheet
For Cells D13:D64 - clear the content whilst retaining the formula, set the fill colour to xlNone
For Cells E13:E64 - clear the content and set the fill colour to xlNone
For Cells H1:H2 - clear the contents

Thanks

 

[Alex Blakenburg]

Your text does not mention conditional formatting.
Ignoring conditional formatting this might work for you.
It won't clear content of cells that the user entered starting with "=" ie adding up figures in the cell.

Sub ResetSPLPlanner()
'
' ResetSPLPlanner Macro
'
    ActiveSheet.Protect UserInterFaceOnly:=True
    Range("D13:D64").SpecialCells(xlCellTypeConstants, 23).ClearContents
    Range("E13:E64").ClearContents
    
    With Range("D13:E64").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Range("H1:H2").ClearContents
    
    Range("A1").Select
End Sub

 

[MLC1980]

@Alex Blakenburg

Thanks for your reply, I do have the following conditional formatting applied to cells D13:D64

I tried the code you provided but got run-time error 1004, no cells were found

 

[Alex Blakenburg]

You must not have had any cells that did not have formulas, we will need to add error trapping for that scenario.
Also to remove conditional formatting the sheet protection needs to have been removed.

Try the below:

Sub ResetSPLPlanner()
'
' ResetSPLPlanner Macro
'
    ActiveSheet.Unprotect
    
    On Error Resume Next
    ' if it errors out all the cells are formulas - so do nothing ie do not clear contents
    Range("D13:D64").SpecialCells(xlCellTypeConstants, 23).ClearContents
    On Error GoTo 0
    Range("E13:E64").ClearContents
    
    With Range("D13:E64")
        .Cells.FormatConditions.Delete              ' Need sheet protection removed for this to work
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

    Range("H1:H2").ClearContents
    
    Range("A1").Select
    
    ActiveSheet.Protect UserInterFaceOnly:=True
End Sub

 

[MLC1980]

You must not have had any cells that did not have formulas, we will need to add error trapping for that scenario.
Also to remove conditional formatting the sheet protection needs to have been removed.

Try the below:

Sub ResetSPLPlanner()
'
' ResetSPLPlanner Macro
'
    ActiveSheet.Unprotect
   
    On Error Resume Next
    ' if it errors out all the cells are formulas - so do nothing ie do not clear contents
    Range("D13:D64").SpecialCells(xlCellTypeConstants, 23).ClearContents
    On Error GoTo 0
    Range("E13:E64").ClearContents
   
    With Range("D13:E64")
        .Cells.FormatConditions.Delete              ' Need sheet protection removed for this to work
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

    Range("H1:H2").ClearContents
   
    Range("A1").Select
   
    ActiveSheet.Protect UserInterFaceOnly:=True
End Sub

@Alex Blakenburg the problem I had with that is it deleted the conditional formatting for the next user, I still needed the conditional formatting to work for the next user.
I managed to resolve it by changing:

.cells.FormatConditions.Delete

to

.cells.FormatConditions.AddUniqueValues

Thanks for your help.