[Solved] Switching places of values with macro (LibO Calc)

Help with installation and general system troubleshooting questions concerning the office suite LibreOffice.

[Solved] Switching places of values with macro (LibO Calc)

Postby mshuttunen76 » Mon Oct 10, 2016 9:07 am

I've made some help texts for different buttons, with this function:
Code: Select all   Expand viewCollapse view
=IF(A13=1;"";IF(A13=2;"CLEAR VALUES ADDED ON THIS SHEET"))


and a button with a macro that turns the help texts on/off simply by changing the places of the values in A13 and A14 by using A15 as temporary cell.

It works like a charm in OpenOffice Calc and with MS Excel (different file) also, but it won't work in LibreOffice Calc. The values disappears or one of the numbers changes. I've tried changing the format of the values, but nothing seems to help.

I tried the same in a new worksheet, but the same thing happened.

You guys have any ideas how to make it work?

I have LibreOffice ver 5.1.5.2
Attachments
help.jpg
Last edited by Hagar Delest on Mon Oct 10, 2016 1:48 pm, edited 2 times in total.
Reason: tagged [Solved].
OpenOffice 4.1.1
Windows 10
mshuttunen76
 
Posts: 11
Joined: Fri Sep 04, 2015 6:04 pm

Re: switching places of values with macro with libreoffice c

Postby Zizi64 » Mon Oct 10, 2016 9:43 am

Can you upload your example .ods file and the macro code here?
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: switching places of values with macro with libreoffice c

Postby mshuttunen76 » Mon Oct 10, 2016 9:56 am

Here's the macro code made with the recorder:

Code: Select all   Expand viewCollapse view
sub Main
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 = "$AI$13"

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 = "$AI$15"

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 = "SVD"
args4(1).Name = "FormulaCommand"
args4(1).Value = 0
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())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$AI$13"

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

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

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$AI$14"

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

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

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$AI$13"

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

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

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

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "$AI$14"

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

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

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$AI$15"

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

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

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "ToPoint"
args15(0).Value = "$AI$14"

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

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

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

rem ----------------------------------------------------------------------
dim args17(0) as new com.sun.star.beans.PropertyValue
args17(0).Name = "ToPoint"
args17(0).Value = "$AI$15"

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

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

rem ----------------------------------------------------------------------
dim args19(0) as new com.sun.star.beans.PropertyValue
args19(0).Name = "ToPoint"
args19(0).Value = "$A$1"

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


end sub


I tried to make the .ods file smaller in size, but it was over 0,5Mb. You can download the file from: https://goo.gl/xQC7dL
OpenOffice 4.1.1
Windows 10
mshuttunen76
 
Posts: 11
Joined: Fri Sep 04, 2015 6:04 pm

Re: switching places of values with macro with libreoffice c

Postby mshuttunen76 » Mon Oct 10, 2016 12:06 pm

Zizi64 wrote:Can you upload your example .ods file and the macro code here?


I changed the values to A and B and format cells to text. The macro works if I run it through the dropdown menu Tools --> Macros --> Run Macro.

In the original file with 1 and 2...If I run the macro from Tools --> Macros --> Run Macro, it complains something about cell protection, even though those cells aren't protected.
OpenOffice 4.1.1
Windows 10
mshuttunen76
 
Posts: 11
Joined: Fri Sep 04, 2015 6:04 pm

Re: switching places of values with macro with libreoffice c

Postby mshuttunen76 » Mon Oct 10, 2016 12:58 pm

I solved the problem with a workaround. I changed the changing values to A and B with cell format 'text'.

The Form Controls Button for Help On/Off did not for some reason work right, so I remade all the buttons with the drawing tool rectangle and assigned the macros to them. Now it works.

It's funny how the other buttons made with Form Control does work.
OpenOffice 4.1.1
Windows 10
mshuttunen76
 
Posts: 11
Joined: Fri Sep 04, 2015 6:04 pm


Return to LibreOffice

Who is online

Users browsing this forum: No registered users and 4 guests