Using control to clear spreadsheet

[Guest]

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?

I want to use a control (button) to click that will clear the sheet but not
clear the formulas and calulation cells.

 

[Ron de Bruin]

Hi dvonj

Select all cells in the range
You can use F5
Special>Constants
OK
Press the delete button

Record a macro to get the code when you do this manual

 

[JE McGimpsey]

What about label cells???

To clear everything:

Public Sub ClearConstants()
On Error Resume Next 'in case no constants
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub

To clear only numeric constants:

Public Sub ClearNumberConstants()
On Error Resume Next 'in case no constants
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, _
xlNumbers).ClearContents
On Error GoTo 0
End Sub


To clear constants from only certain areas

Public Sub ClearInputConstants()
Const sInputAreas As String = "B2:C20, E2:F20, J5"
On Error Resume Next 'in case no constants
ActiveSheet.Range(sInputAreas).SpecialCells( _
xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub

 

[Guest]

I placed a buttom on the sheet and want to click it to clear the sheet. How
do I get the button to do this?

 

[Ron de Bruin]

J.E posted code
Have you try this to get the code ?

 

[Guest]

Yes there are lables and such that I don't want to lose. All I want to clear
are the cells that I entered data in.

 

[Ron de Bruin]

You can add the range in the code maybe

Range("A2:A100,C2:C50,D2").SpecialCells(xlCellTypeConstants).ClearContents

 

[JE McGimpsey]

That's what my third macro does...<g>

 

[Ron de Bruin]

Hi J.E

Not looked at your code examples but it seems the OP also not see it .

 

[Guest]

So now that I have the macro how do I get it to run when I click the control
button?
This is what I have so far;
Private Sub CommandButton1_Click()
Public Sub ClearInputConstants()
Const sInputAreas As String = "E5:E9, E12:E16, C20:C29, D2029,
C33:C39, D3339, C43:C46, D4346, C50:C52, D5052, C56:C60, D5660,
C64:C70, D6470, H20:H28, I20:I28, H32:H37, I32:I37, H41:H44, I41:I44,
H48:H50, I48:I50, H54:H56, I54:I56, H60:H63, I60:I63"
On Error Resume Next 'in case no constants
ActiveSheet.Range(sInputAreas).SpecialCells( _
xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub

End Sub

 

[JE McGimpsey]

If you're using a Forms Toolbar button, you just need to associate the
macro with the button - right-click it and choose Assign Macro...

If, as appears likely here, you're using a Controls Toolbox control,
delete the "Public Sub ClearInputConstants()" and first "End Sub" lines,

 

[Guest]

Ok thanks that worked.

If you're using a Forms Toolbar button, you just need to associate the
macro with the button - right-click it and choose Assign Macro...

If, as appears likely here, you're using a Controls Toolbox control,
delete the "Public Sub ClearInputConstants()" and first "End Sub" lines,

 

[Guest]

One more thing, is there a way to have a message pop up asking if the user is
sure they are ready to clear the sheet? Then click "OK" to continue or
"CANCEL" to not clear and return to the sheet?

 

[David]

=?Utf-8?B?ZHZvbmo=?= wrote

One more thing, is there a way to have a message pop up asking if the
user is sure they are ready to clear the sheet? Then click "OK" to
continue or "CANCEL" to not clear and return to the sheet?

Paste something like this early in the macro: (correct wordwrap after
pasting).

If MsgBox("Warning!! This Action Will Clear The Sheet!" & Chr(13) &
"Continue?", vbCritical + vbYesNo) = vbNo Then Exit Sub