Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
See possible solution(s) in .programming group.
Gord Dibben Excel MVP On Wed, 30 Nov 2005 12:26:04 -0800, "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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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. ToolsScenarios 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: ToolsScenarios 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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. ToolsScenarios 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: ToolsScenarios 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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" 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reset all enteries on the worksheet?
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" 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
copyright and worksheet protection | Excel Discussion (Misc queries) |