Stopping function of checkbox when unchecked

Discuss the spreadsheet application

Stopping function of checkbox when unchecked

Postby Teadout2007 » Sat Jul 11, 2020 11:49 pm

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!
OpenOffice 4.0.1 on Windows 10
Teadout2007
 
Posts: 5
Joined: Sat Jul 11, 2020 11:07 pm

Re: Stopping function of checkbox when unchecked

Postby Zizi64 » Sun Jul 12, 2020 6:42 am

Please upload your ODF type sample file with the embedded macro code and the checkbox.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9725
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Stopping function of checkbox when unchecked

Postby Teadout2007 » Sun Jul 12, 2020 6:55 am

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

Thanks!
OpenOffice 4.0.1 on Windows 10
Teadout2007
 
Posts: 5
Joined: Sat Jul 11, 2020 11:07 pm

Re: Stopping function of checkbox when unchecked

Postby Zizi64 » Sun Jul 12, 2020 7:08 am

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.
Last edited by Zizi64 on Sun Jul 12, 2020 7:46 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9725
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Stopping function of checkbox when unchecked

Postby Zizi64 » Sun Jul 12, 2020 7:20 am

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.
Last edited by Zizi64 on Sun Jul 12, 2020 7:48 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9725
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Stopping function of checkbox when unchecked

Postby robleyd » Sun Jul 12, 2020 7:21 am

See How to attach a document for details regarding attaching a document.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3750
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Stopping function of checkbox when unchecked

Postby Teadout2007 » Sat Jul 18, 2020 6:33 pm

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())
OpenOffice 4.0.1 on Windows 10
Teadout2007
 
Posts: 5
Joined: Sat Jul 11, 2020 11:07 pm

Re: Stopping function of checkbox when unchecked

Postby Teadout2007 » Sat Jul 18, 2020 6:37 pm

Maybe this will work for posting the macro...
Attachments
PasteAdd Macro.ods
(12.07 KiB) Downloaded 56 times
OpenOffice 4.0.1 on Windows 10
Teadout2007
 
Posts: 5
Joined: Sat Jul 11, 2020 11:07 pm

Re: Stopping function of checkbox when unchecked

Postby Zizi64 » Sun Jul 19, 2020 10:12 am

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9725
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Stopping function of checkbox when unchecked

Postby Teadout2007 » Sat Jul 25, 2020 8:03 pm

Haven't had a chance yet to work it some more, but thank you for the help!
OpenOffice 4.0.1 on Windows 10
Teadout2007
 
Posts: 5
Joined: Sat Jul 11, 2020 11:07 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot], mike.stirton and 7 guests