How to reset all enteries on the worksheet?

[DORI]

Is it possible to press a cell on the worksheet to clear all entered data in
the entire worksheet?
I have a worksheet that users enter several data in different pages and then
print a report. The next user will have to clear all eneries and then enter
their own data before printing the next report. At this time, we close the
program and reopen it which is a bit of pain, just wondering if we could
create a key (let call it "Reset all Forms" on the worksheet to press and
clear all data.
I appreciate any comments in advance.
Dori

[FinRazel]

Try This:
Add dummy data everywhere you want deletable.
Go to Tools->Macro->Record New Macro
Name your macro, click OK
Holding down Ctrl, click everywhere you will delete, this will highlight
them all at the same time.
Hit the delete key
Stop recording your Macro (the little box button on the record macros toolbar)
Add a button to you worksheet using the Forms Toolbar.
Right-click on the button, click "assign macro"
Highlight your new macro.

Your good to go.
--
Anne Murray


"DORI" wrote:

> Is it possible to press a cell on the worksheet to clear all entered data in
> the entire worksheet?
> I have a worksheet that users enter several data in different pages and then
> print a report. The next user will have to clear all eneries and then enter
> their own data before printing the next report. At this time, we close the
> program and reopen it which is a bit of pain, just wondering if we could
> create a key (let call it "Reset all Forms" on the worksheet to press and
> clear all data.
> I appreciate any comments in advance.
> Dori

[Gord Dibben]

See possible solution(s) in .programming group.


Gord Dibben Excel MVP

On Wed, 30 Nov 2005 12:26:04 -0800, "DORI" <[email protected]>
wrote:

>Is it possible to press a cell on the worksheet to clear all entered data in
>the entire worksheet?
>I have a worksheet that users enter several data in different pages and then
>print a report. The next user will have to clear all eneries and then enter
>their own data before printing the next report. At this time, we close the
>program and reopen it which is a bit of pain, just wondering if we could
>create a key (let call it "Reset all Forms" on the worksheet to press and
>clear all data.
>I appreciate any comments in advance.
>Dori

[DORI]

Thank you so much FinRazel. I followed your instructions and it worked. My
only question is if this works only for the active worksheet. It would be
great if I could apply this to the entire workbook to clear multiple
worksheets with one click.
Dori

"FinRazel" wrote:

> Try This:
> Add dummy data everywhere you want deletable.
> Go to Tools->Macro->Record New Macro
> Name your macro, click OK
> Holding down Ctrl, click everywhere you will delete, this will highlight
> them all at the same time.
> Hit the delete key
> Stop recording your Macro (the little box button on the record macros toolbar)
> Add a button to you worksheet using the Forms Toolbar.
> Right-click on the button, click "assign macro"
> Highlight your new macro.
>
> Your good to go.
> --
> Anne Murray
>
>
> "DORI" wrote:
>
> > Is it possible to press a cell on the worksheet to clear all entered data in
> > the entire worksheet?
> > I have a worksheet that users enter several data in different pages and then
> > print a report. The next user will have to clear all eneries and then enter
> > their own data before printing the next report. At this time, we close the
> > program and reopen it which is a bit of pain, just wondering if we could
> > create a key (let call it "Reset all Forms" on the worksheet to press and
> > clear all data.
> > I appreciate any comments in advance.
> > Dori

[FinRazel]

Yes, this method will work on multiple worksheets. Simply make all of your
selections for one worksheet, hit delete, then move on to the next worksheet.
When you are finished deleting the final worksheet, stop recording your
macro.

Cheers!
--
Anne Murray


"DORI" wrote:

> Thank you so much FinRazel. I followed your instructions and it worked. My
> only question is if this works only for the active worksheet. It would be
> great if I could apply this to the entire workbook to clear multiple
> worksheets with one click.
> Dori

[Ron Coderre]

I've had pretty good luck using Excel Scenarios to re-initialize a worksheet:

First, set all input cells to the default value you want them to have.
Tools>Scenarios
Click: Add
Scenario Name: Initialize
Changing Cells: (Select the cells to be returned to their current value)
Click: OK
You'll be given the option to change the values of the cells if you need to.
Click: OK, when done editing
Click: Close

To test:
Change values in the input cells.
Then: Tools>Scenarios
Select: Initialize
Click: Show
All changed values will be returned to their default value.

Limitations:
Scenarios work on worksheets, not workbooks. So, if you have a large number
of sheets to initialize, you could still set up the Scenarios but you'd
probably want to use VBA to sequentially show them for each sheet.

Option Explicit
Sub RunInitializations()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
On Error Resume Next
sht.Scenarios("Initialize").Show
On Error GoTo 0
Next sht
End Sub

Does that help?

***********
Regards,
Ron


"DORI" wrote:

> Is it possible to press a cell on the worksheet to clear all entered data in
> the entire worksheet?
> I have a worksheet that users enter several data in different pages and then
> print a report. The next user will have to clear all eneries and then enter
> their own data before printing the next report. At this time, we close the
> program and reopen it which is a bit of pain, just wondering if we could
> create a key (let call it "Reset all Forms" on the worksheet to press and
> clear all data.
> I appreciate any comments in advance.
> Dori

[DORI]

Thanks Anne. You have been very helpful.
Dori

"FinRazel" wrote:

> Yes, this method will work on multiple worksheets. Simply make all of your
> selections for one worksheet, hit delete, then move on to the next worksheet.
> When you are finished deleting the final worksheet, stop recording your
> macro.
>
> Cheers!
> --
> Anne Murray
>
>
> "DORI" wrote:
>
> > Thank you so much FinRazel. I followed your instructions and it worked. My
> > only question is if this works only for the active worksheet. It would be
> > great if I could apply this to the entire workbook to clear multiple
> > worksheets with one click.
> > Dori
>

[DORI]

Thanks Ron for the alternative way of doing this. So much to learn from
knowledgable people on this site. I went by VBA code and assigning a macro to
a button so that everything clears up with one click.
Dori

"Ron Coderre" wrote:

> I've had pretty good luck using Excel Scenarios to re-initialize a worksheet:
>
> First, set all input cells to the default value you want them to have.
> Tools>Scenarios
> Click: Add
> Scenario Name: Initialize
> Changing Cells: (Select the cells to be returned to their current value)
> Click: OK
> You'll be given the option to change the values of the cells if you need to.
> Click: OK, when done editing
> Click: Close
>
> To test:
> Change values in the input cells.
> Then: Tools>Scenarios
> Select: Initialize
> Click: Show
> All changed values will be returned to their default value.
>
> Limitations:
> Scenarios work on worksheets, not workbooks. So, if you have a large number
> of sheets to initialize, you could still set up the Scenarios but you'd
> probably want to use VBA to sequentially show them for each sheet.
>
> Option Explicit
> Sub RunInitializations()
> Dim sht As Worksheet
> For Each sht In ThisWorkbook.Worksheets
> On Error Resume Next
> sht.Scenarios("Initialize").Show
> On Error GoTo 0
> Next sht
> End Sub
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
>
> "DORI" wrote:
>
> > Is it possible to press a cell on the worksheet to clear all entered data in
> > the entire worksheet?
> > I have a worksheet that users enter several data in different pages and then
> > print a report. The next user will have to clear all eneries and then enter
> > their own data before printing the next report. At this time, we close the
> > program and reopen it which is a bit of pain, just wondering if we could
> > create a key (let call it "Reset all Forms" on the worksheet to press and
> > clear all data.
> > I appreciate any comments in advance.
> > Dori

[Gord Dibben]

DORI

Try this macro.

Sub clearall()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Cells.ClearContents
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Wed, 30 Nov 2005 19:32:02 -0800, "DORI" <[email protected]>
wrote:

>Thank you so much FinRazel. I followed your instructions and it worked. My
>only question is if this works only for the active worksheet. It would be
>great if I could apply this to the entire workbook to clear multiple
>worksheets with one click.
>Dori
>
>"FinRazel" wrote:
>
>> Try This:
>> Add dummy data everywhere you want deletable.
>> Go to Tools->Macro->Record New Macro
>> Name your macro, click OK
>> Holding down Ctrl, click everywhere you will delete, this will highlight
>> them all at the same time.
>> Hit the delete key
>> Stop recording your Macro (the little box button on the record macros toolbar)
>> Add a button to you worksheet using the Forms Toolbar.
>> Right-click on the button, click "assign macro"
>> Highlight your new macro.
>>
>> Your good to go.
>> --
>> Anne Murray
>>
>>
>> "DORI" wrote:
>>
>> > Is it possible to press a cell on the worksheet to clear all entered data in
>> > the entire worksheet?
>> > I have a worksheet that users enter several data in different pages and then
>> > print a report. The next user will have to clear all eneries and then enter
>> > their own data before printing the next report. At this time, we close the
>> > program and reopen it which is a bit of pain, just wondering if we could
>> > create a key (let call it "Reset all Forms" on the worksheet to press and
>> > clear all data.
>> > I appreciate any comments in advance.
>> > Dori

[DORI]

Hi Gord,
Thanks for the code. When I ran the macro, it gave me an error message that
I have to unprotect the sheet (I have protected certain cells with formulas
and info for users). I did that and then my entire worksheet was cleared
including all my formulas in the cells.
Dori

"Gord Dibben" wrote:

> DORI
>
> Try this macro.
>
> Sub clearall()
> Application.ScreenUpdating = False
> Dim n As Single
> For n = 1 To Sheets.Count
> Sheets(n).Cells.ClearContents
> Next n
> Application.ScreenUpdating = True
> End Sub
>
>
> Gord Dibben Excel MVP
>
> On Wed, 30 Nov 2005 19:32:02 -0800, "DORI" <[email protected]>
> wrote:
>
> >Thank you so much FinRazel. I followed your instructions and it worked. My
> >only question is if this works only for the active worksheet. It would be
> >great if I could apply this to the entire workbook to clear multiple
> >worksheets with one click.
> >Dori
> >
> >"FinRazel" wrote:
> >
> >> Try This:
> >> Add dummy data everywhere you want deletable.
> >> Go to Tools->Macro->Record New Macro
> >> Name your macro, click OK
> >> Holding down Ctrl, click everywhere you will delete, this will highlight
> >> them all at the same time.
> >> Hit the delete key
> >> Stop recording your Macro (the little box button on the record macros toolbar)
> >> Add a button to you worksheet using the Forms Toolbar.
> >> Right-click on the button, click "assign macro"
> >> Highlight your new macro.
> >>
> >> Your good to go.
> >> --
> >> Anne Murray
> >>
> >>
> >> "DORI" wrote:
> >>
> >> > Is it possible to press a cell on the worksheet to clear all entered data in
> >> > the entire worksheet?
> >> > I have a worksheet that users enter several data in different pages and then
> >> > print a report. The next user will have to clear all eneries and then enter
> >> > their own data before printing the next report. At this time, we close the
> >> > program and reopen it which is a bit of pain, just wondering if we could
> >> > create a key (let call it "Reset all Forms" on the worksheet to press and
> >> > clear all data.
> >> > I appreciate any comments in advance.
> >> > Dori
>
>

[Ron Coderre]

Thanks for the feedback. I'm glad I could help.

***********
Regards,
Ron


"DORI" wrote:

> Thanks Ron for the alternative way of doing this. So much to learn from
> knowledgable people on this site. I went by VBA code and assigning a macro to
> a button so that everything clears up with one click.
> Dori
>
> "Ron Coderre" wrote:
>
> > I've had pretty good luck using Excel Scenarios to re-initialize a worksheet:
> >
> > First, set all input cells to the default value you want them to have.
> > Tools>Scenarios
> > Click: Add
> > Scenario Name: Initialize
> > Changing Cells: (Select the cells to be returned to their current value)
> > Click: OK
> > You'll be given the option to change the values of the cells if you need to.
> > Click: OK, when done editing
> > Click: Close
> >
> > To test:
> > Change values in the input cells.
> > Then: Tools>Scenarios
> > Select: Initialize
> > Click: Show
> > All changed values will be returned to their default value.
> >
> > Limitations:
> > Scenarios work on worksheets, not workbooks. So, if you have a large number
> > of sheets to initialize, you could still set up the Scenarios but you'd
> > probably want to use VBA to sequentially show them for each sheet.
> >
> > Option Explicit
> > Sub RunInitializations()
> > Dim sht As Worksheet
> > For Each sht In ThisWorkbook.Worksheets
> > On Error Resume Next
> > sht.Scenarios("Initialize").Show
> > On Error GoTo 0
> > Next sht
> > End Sub
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> >
> > "DORI" wrote:
> >
> > > Is it possible to press a cell on the worksheet to clear all entered data in
> > > the entire worksheet?
> > > I have a worksheet that users enter several data in different pages and then
> > > print a report. The next user will have to clear all eneries and then enter
> > > their own data before printing the next report. At this time, we close the
> > > program and reopen it which is a bit of pain, just wondering if we could
> > > create a key (let call it "Reset all Forms" on the worksheet to press and
> > > clear all data.
> > > I appreciate any comments in advance.
> > > Dori

[Gord Dibben]

Aploogies Dori

I guess I should have wondered why you wanted ALL data cleared.

You just want the constants cleared <doh>

This will clear just numerics, which would include dates.

Sub cleardata()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect Password:="justme"
.Cells.SpecialCells(xlCellTypeConstants, 21).ClearContents
.Protect Password:="justme"
End With
Next ws
End Sub

No error checking. Will crash if no numeric data on a sheet.


Gord

On Thu, 1 Dec 2005 11:30:04 -0800, "DORI" <[email protected]>
wrote:

>Hi Gord,
>Thanks for the code. When I ran the macro, it gave me an error message that
>I have to unprotect the sheet (I have protected certain cells with formulas
>and info for users). I did that and then my entire worksheet was cleared
>including all my formulas in the cells.
>Dori
>
>"Gord Dibben" wrote:
>
>> DORI
>>
>> Try this macro.
>>
>> Sub clearall()
>> Application.ScreenUpdating = False
>> Dim n As Single
>> For n = 1 To Sheets.Count
>> Sheets(n).Cells.ClearContents
>> Next n
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Wed, 30 Nov 2005 19:32:02 -0800, "DORI" <[email protected]>
>> wrote:
>>
>> >Thank you so much FinRazel. I followed your instructions and it worked. My
>> >only question is if this works only for the active worksheet. It would be
>> >great if I could apply this to the entire workbook to clear multiple
>> >worksheets with one click.
>> >Dori
>> >
>> >"FinRazel" wrote:
>> >
>> >> Try This:
>> >> Add dummy data everywhere you want deletable.
>> >> Go to Tools->Macro->Record New Macro
>> >> Name your macro, click OK
>> >> Holding down Ctrl, click everywhere you will delete, this will highlight
>> >> them all at the same time.
>> >> Hit the delete key
>> >> Stop recording your Macro (the little box button on the record macros toolbar)
>> >> Add a button to you worksheet using the Forms Toolbar.
>> >> Right-click on the button, click "assign macro"
>> >> Highlight your new macro.
>> >>
>> >> Your good to go.
>> >> --
>> >> Anne Murray
>> >>
>> >>
>> >> "DORI" wrote:
>> >>
>> >> > Is it possible to press a cell on the worksheet to clear all entered data in
>> >> > the entire worksheet?
>> >> > I have a worksheet that users enter several data in different pages and then
>> >> > print a report. The next user will have to clear all eneries and then enter
>> >> > their own data before printing the next report. At this time, we close the
>> >> > program and reopen it which is a bit of pain, just wondering if we could
>> >> > create a key (let call it "Reset all Forms" on the worksheet to press and
>> >> > clear all data.
>> >> > I appreciate any comments in advance.
>> >> > Dori
>>
>>