Stopping function of checkbox when unchecked

[Teadout2007]

I have set up a checkbox to add the number in a cell to the number of another cell when the box is checked. I am trying to figure out how to stop the function from repeating when I uncheck the box. I have tried using macro to minus out the second function when the box is unchecked but that did not work. I tried using the Data tab to enter or remove the value when the box was checked or not checked, but I need the value to stay in the cell when unchecked. I thought about "IF" statements but I do not know how to tie it the checkbox, or if that would even work since unchecking the box is a separate action.

What I'm trying to accomplish is a checklist that can be reset. When the box is checked it adds the value of a certain cell to the value of another and when the box is unchecked it should not do anything at all. Right now it adds the value of the first cell to the second cell both when the box is checked and again when the box is unchecked which is creating inaccurate data on my spreadsheet.

For further clarification here is an example. I have 300 in a cell and 400 in another. When the box is checked the cell with 400 has the 300 from the first cell added to it to equal 700. When I uncheck the box though it should not again add the first cell to the second making it equal 1000. It should remain at 700.

Is it possible to stop the function from repeating when the checkbox is clicked to uncheck?

Thanks for all suggestions and information!

[Zizi64]

Please upload your ODF type sample file with the embedded macro code and the checkbox.

[Teadout2007]

Okay, let me figure out how to do that and I will get that posted.

Thanks!

[Zizi64]

Okay, let me figure out how to do that and I will get that posted

Use the POSTREPLY button instead of the Quick Reply.

You will find the "Upload attachment" TAB under the post edit window.
Note: The file size limit is 128 KiB in this forum.

[Zizi64]

For further clarification here is an example. I have 300 in a cell and 400 in another. When the box is checked the cell with 400 has the 300 from the first cell added to it to equal 700. When I uncheck the box though it should not again add the first cell to the second making it equal 1000. It should remain at 700.

I suppose it, that you assigned your macro to the "Item status changed" event. You must examine the actual boolean value of the checkbox in this case.
Or use a simple Button, and its Mouse click event instead of the checkbox.

[robleyd]

See How to attach a document for details regarding attaching a document.

[Teadout2007]

Hi,

Not sure if this is how this is supposed to go in here... I honestly was confused about how to get the ODF type sample file with the embedded macro code so I could upload it. Also unsure of how to get the checkbox over here? I'm sorry if these are basic things and I need them explained. Guess I could be considered a beginner. Haha. Below is the macro just copy/pasted... hopefully that helps. The checkbox is found in the form controls toolbar. Thanks again for all the help!

Code: Select all   Expand viewCollapse view

  *****  BASIC  *****

Sub Main

End Sub


sub PasteSub
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$K$7"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$K$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(5) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"
args4(0).Value = "A"
args4(1).Name = "FormulaCommand"
args4(1).Value = 2
args4(2).Name = "SkipEmptyCells"
args4(2).Value = false
args4(3).Name = "Transpose"
args4(3).Value = false
args4(4).Name = "AsLink"
args4(4).Value = false
args4(5).Name = "MoveMode"
args4(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())


end sub

sub PasteAdd
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$K$7"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$K$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(5) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"
args4(0).Value = "A"
args4(1).Name = "FormulaCommand"
args4(1).Value = 1
args4(2).Name = "SkipEmptyCells"
args4(2).Value = false
args4(3).Name = "Transpose"
args4(3).Value = false
args4(4).Name = "AsLink"
args4(4).Value = false
args4(5).Name = "MoveMode"
args4(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())

[Teadout2007]

Maybe this will work for posting the macro...

[Zizi64]

The modified sample macro - embedded in the attachment below - shows you how to distinguish between the Unchecked->Checked / Checked-> Unchecked edges.

In other words: how to create edge-triggered control by usage the oEvent and the State of the object.

PasteAdd MacroZizi64.ods

(13.16 KiB) Downloaded 60 times

Your macro was recorded by the macro recorder. Always better to WRITE your macros based on the API functions. See my few commands in the macro code in Module "Mort2", sub "PasteAdd(oEvent as object)". Your recorded macro has a (relative) large code list. You can achieve this task by a few API callings.
The skeleton of the API solution: Get a value from a specific cell - add the value to the value of an another specific cell.
Not needed any Copy/Paste...

API: Application Programming Interface.

[Teadout2007]

Haven't had a chance yet to work it some more, but thank you for the help!