Using control to clear spreadsheet
 

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.

Using control to clear spreadsheet
 

Hi dvonj

Select all cells in the range
You can use F5
SpecialConstants
OK
Press the delete button

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"dvonj" wrote in message ...
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.

Using control to clear spreadsheet
 

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

In article ,
dvonj wrote:

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.

Using control to clear spreadsheet
 

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.

"JE McGimpsey" wrote:

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

In article ,
dvonj wrote:

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.

Using control to clear spreadsheet
 

You can add the range in the code maybe

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"dvonj" wrote in message ...
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.

"JE McGimpsey" wrote:

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

In article ,
dvonj wrote:

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.

Using control to clear spreadsheet
 

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

In article ,
"Ron de Bruin" wrote:

You can add the range in the code maybe

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

Using control to clear spreadsheet
 

Hi J.E

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"JE McGimpsey" wrote in message ...
That's what my third macro does...<g

In article ,
"Ron de Bruin" wrote:

You can add the range in the code maybe

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

Using control to clear spreadsheet
 

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, D20:D29,
C33:C39, D33:D39, C43:C46, D43:D46, C50:C52, D50:D52, C56:C60, D56:D60,
C64:C70, D64:D70, 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" wrote:

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

In article ,
dvonj wrote:

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.

Using control to clear spreadsheet
 

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,


In article ,
dvonj wrote:

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, D20:D29,
C33:C39, D33:D39, C43:C46, D43:D46, C50:C52, D50:D52, C56:C60, D56:D60,
C64:C70, D64:D70, 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

Using control to clear spreadsheet
 

Ok thanks that worked.

"JE McGimpsey" wrote:

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,


In article ,
dvonj wrote:

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, D20:D29,
C33:C39, D33:D39, C43:C46, D43:D46, C50:C52, D50:D52, C56:C60, D56:D60,
C64:C70, D64:D70, 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

Using control to clear spreadsheet
 

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?

"JE McGimpsey" wrote:

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,


In article ,
dvonj wrote:

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, D20:D29,
C33:C39, D33:D39, C43:C46, D43:D46, C50:C52, D50:D52, C56:C60, D56:D60,
C64:C70, D64:D70, 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

Using control to clear spreadsheet
 

?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


--
David