[Solved] Macro? To Clear All Chosen Cells to Zero Value?

[mahogan]

I have created a series of spreadsheets in one file in Calc, I want to send this to others so they can use some of the functions, such as select from a series of drop downs and enter amounts in other cells. Once the users choose the information it will give them the resulted data at the end of the sheet. Once they have this information, the users will want to begin again with a new set of data and drop down information to derive another data combination result. Rather than have them begin with the previously selected data from their first entry, etc., I am trying to find a way for the users to click on a "Button" to have then clear all the previously entered data they either entered or chose from the drop down (ie - lookup table). This will help the users be less confused and apt to make an error for the next batch of data result they are seeking. Anyone have any helpful hints? Thanks in advance.

[FJCC]

If you want to set the same set of cells to zero every time, you can use a variant of the code at the bottom of this thread
Just set oCell.value to zero after calling getCellrangeByName() for each cell.

[MrProgrammer]

Can't you just use File, Reload?

[mahogan]

If you want to set the same set of cells to zero every time, you can use a variant of the code at the bottom of this thread
Just set oCell.value to zero after calling getCellrangeByName() for each cell.

Do I input the code into the RESET cell, or run a Macro (which I am clueless). Iv'e tried several attempts at running a Macro, but just don't seem to get it.

I have attached a sample file, I'm not sure how to get this to work.

[mahogan]

Can't you just use File, Reload?

The problem with reloading is that if the file was saved with data changes that were not ZERO, then it would reload the last state with changes, and odds are users will save the file with Non-Zero Values. Thus the need for a reset button and it would ease use of calculations as there will be numerous one right after another. Essentially this would be like a Clear button on a Calculator.

[FJCC]

I modified your document to show what a solution would look like. It is too late here for me to explain the whole thing. A couple of points
1. The macro is attached to the document, but it is not in any cell. Press ALT+F11 and you will get a dialog that includes, among other things a little icon with the document name. Click on the + sign and then again on the + sign next to the folder (actually called a library) Standard. Click on Module1 and then select Edit. You will see the macro code.
2. Your button was not really a button. Buttons are created with the Form Controls tool bar accessed through View -> Tool bars -> Form Controls. I added a button and tied it to the macro.

[mahogan]

Thanks for the help, I see how to get to the Macro code, it makes sense. And so do the Button controls. However, when clicking on the Reset Button, it does nothing. Not sure if I am doing something wrong. In the Button Control, I see the Macro script is assigned to the Mouse Button Released. I experimented with Pressed, etc. Still am unable to get it to reset the values, they remain the same.

[Villeroy]

Generic solution to put anything except arrays into any selection on one sheet: http://www.oooforum.org/forum/viewtopic.phtml?t=55790

In many cases a set of scenarios (one of which containing the initial status) provides a more elegant solution.
Scenario "New Empty" in http://user.services.openoffice.org/en/ ... hp?id=3004

[FJCC]

Certainly check out Villeroy's suggestions. As for the button not working on the sheet I posted, the control is probably in design mode, which allows you to edit its properties but renders the button inoperable . Bring up the Form Controls tool bar and click on the top right icon. The icon contains a pencil, ruler and drawing triangle. That icon will toggle the button in and out of design mode.