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

[Hagar Delest]

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

[Zizi64]

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

[mshuttunen76]

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

[mshuttunen76]

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.

[mshuttunen76]

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.