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.

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


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


"dvonj" <dvonj@discussions.microsoft.com> wrote in message news:220A819C-5DC9-44B5-B715-2758A266CA5E@microsoft.com...
> 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.

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 <220A819C-5DC9-44B5-B715-2758A266CA5E@microsoft.com>,
dvonj <dvonj@discussions.microsoft.com> 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.

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" wrote:

> 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
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "dvonj" <dvonj@discussions.microsoft.com> wrote in message news:220A819C-5DC9-44B5-B715-2758A266CA5E@microsoft.com...
> > 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.
>
>
>

J.E posted code

>> Record a macro to get the code when you do this manual
Have you try this to get the code ?



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


"dvonj" <dvonj@discussions.microsoft.com> wrote in message news:FEA1E0DC-9F6F-4746-B296-63C0E336FC72@microsoft.com...
>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" wrote:
>
>> 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
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "dvonj" <dvonj@discussions.microsoft.com> wrote in message news:220A819C-5DC9-44B5-B715-2758A266CA5E@microsoft.com...
>> > 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.
>>
>>
>>

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 <220A819C-5DC9-44B5-B715-2758A266CA5E@microsoft.com>,
> dvonj <dvonj@discussions.microsoft.com> 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.
>

You can add the range in the code maybe

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


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


"dvonj" <dvonj@discussions.microsoft.com> wrote in message news:99A97472-BD32-472B-8FA7-858867C82751@microsoft.com...
> 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 <220A819C-5DC9-44B5-B715-2758A266CA5E@microsoft.com>,
>> dvonj <dvonj@discussions.microsoft.com> 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.
>>

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

In article <uVf1yH5SGHA.5496@TK2MSFTNGP11.phx.gbl>,
"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote:

> You can add the range in the code maybe
>
> Range("A2:A100,C2:C50,D2").SpecialCells(xlCellTypeConstants).ClearContents

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" <jemcgimpsey@mvps.org> wrote in message news:jemcgimpsey-503055.13255619032006@msnews.microsoft.com...
> That's what my third macro does...<g>
>
> In article <uVf1yH5SGHA.5496@TK2MSFTNGP11.phx.gbl>,
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote:
>
>> You can add the range in the code maybe
>>
>> Range("A2:A100,C2:C50,D2").SpecialCells(xlCellTypeConstants).ClearContents

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 <220A819C-5DC9-44B5-B715-2758A266CA5E@microsoft.com>,
> dvonj <dvonj@discussions.microsoft.com> 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.
>

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 <26D49661-A666-412A-9FB5-B15701A7388D@microsoft.com>,
dvonj <dvonj@discussions.microsoft.com> 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

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 <26D49661-A666-412A-9FB5-B15701A7388D@microsoft.com>,
> dvonj <dvonj@discussions.microsoft.com> 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
>

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 <26D49661-A666-412A-9FB5-B15701A7388D@microsoft.com>,
> dvonj <dvonj@discussions.microsoft.com> 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
>

=?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


--
David