Clear Cells, Leave Formulas

[JonnieO]

I have a spreadsheet that will be used as a template to enter data and then the sheet will be saved as a CSV file with a command button. Once it saves the file it come back to the template and asks if user wants to clear out the data. In order for this to happen and not lose the formulas the sheet must be 1. Unprotected, 2. Cells cleared of data while leaving the formulas, and 3. Sheet should be protected again.

Everything works so far, but it doesn't clear out the data. It will unlock and then lock the cell, so I'm assuming I am missing something with the clear contents portion. The snippet of code where this is happening is below. Any guidance or help would be appreciated.

With ActiveSheet.Range("A4:O34")
    If MsgBox("Are you sure you want to clear the data?", vbYesNo) = vbYes Then _
    ActiveSheet.Unprotect Password:=""
    ActiveSheet.SpecialCells(xlCellTypeConstants).ClearContents
    ActiveSheet.Protect Password:="", AllowFiltering:=True
End With

 

[JonnieO]

Okay, so I made a subtle change and it seems to work. It may not be the most elegant, but its un-protecting, clearing the data, leaving the formulas, then protecting the sheet again. Silly though, I should have known that I had to tell it what cells to clear.

With ActiveSheet.Range("A4:O34")
    If MsgBox("Are you sure you want to clear the data?", vbYesNo) = vbYes Then _
    ActiveSheet.Unprotect Password:=""
    ActiveSheet.[B][COLOR="#FF0000"]Range("A4:O34")[/COLOR][/B].SpecialCells(xlCellTypeConstants).ClearContents
    ActiveSheet.Protect Password:="", AllowFiltering:=True
End With