How to Launch a Macro Only When a Parameter is Met?

English
#1

Is there a way to launch a macro only when a certain condition is met?
Illustrated is an example where input from a row of cells A3:E3 will be moved to the next row in a list (To a row A12:E12 in this example). I have recorded a macro to do this. The macro lauches when “Save Donation” is clicked.
However: I do not want the macro to move the row unless the values in cells D3 and E3 match. If they do not match… the macro should abort its mission.
(Illustration attached)

Example 1
Example 11056×496 30.8 KB

MacroTestToday.ods (27.4 KB)

#2

Always prefer to attach an example file instead of an image if there isn’t a specific issue concerning the view.

#3

OK… I attached the file (I think).

#4 
sub Main
	doc = ThisComponent
	sheet = doc.CurrentController.ActiveSheet
	
	cell1 = sheet.getCellRangeByName("D3")
	cell2 = sheet.getCellRangeByName("E3")
	
	If cell1.Value <> cell2.Value Then
		Msgbox "No Match"
		Exit Sub
	End IF

	source = sheet.getCellRangeByName("A3:E3")
	target = sheet.getCellRangeByName("A8")
	target = get_next_free_cell(target)
	row = target.CellAddress.Row
	target = sheet.getCellRangeByPosition(0, row, 4, row)
	
	target.DataArray = source.DataArray
	source.clearContents(31)
	
End Sub

Function get_next_free_cell(cell)
	cursor = cell.SpreadSheet.createCursorByRange(cell)
	cursor.gotoEnd()
	col = cell.CellAddress.Column
	row = cursor.RangeAddress.EndRow + 1
	get_next_free_cell = cell.SpreadSheet.getCellByPosition(col, row)
End Function
#5

Thank you. Please consider that I ‘recorded’ the macro using Tools:Record Macro. That is my level of expertise.
Also, consider that the conditional formatting and formula in cell F3 has not one thing to do with the macro MacroTestToday.ods:Standard:Module1:Main.
What I am looking for is an understandable (to me) parameter to enter, or box to tick somewhere, that will launch the macro ‘Main’ only when D3 equals E3.
I don’t mean to be a pest.
Thanks.

#6

There is a more traditional forum here (the English “Macros” branch): :
Apache OpenOffice Community Forum - Macros and UNO API - (View forum) .
There you may ask your question a second time (linking to it here), and it’s possible that a specialist for helping with recorded macros will answer.
From my point of view written macros require some studies into the API documentation, and some knowledge about Basic.
There are. however, guides and the famous texts by Andrew Pitonyak which will help you.
Recorded macros are long, convoluted, and their means are extremely peculiar and badly documented - if at all. I can only understand the most simple ones - and yours is not simple.
There is no way of starting the understanding with recorded macros. They only are a means for cases where they can be helpful without being understood.
I created a complete solution for you (in 2 versions) specialised to the example you gave. The macros are short and rather clear. However, you won’t understand them without the mentioned studies.
See attachment.
ask106745copyOrMoveDataFromInputPositionToFinal.ods (22.7 KB)

#7

That makes exactly the code.
You tried it at least?

#9

Thanks for your response.

#10

Where should I copy and paste the coding? Should I make another button and Assign Macro to it?

#12

Thank you.

#14

If the “you” means me:
I was more than once interrupted when I prepared my example, and when it was done, I posted the results without sufficiently looking around.
Sorry. I appreciate your contributions much.

#8

Strictly targetting at the original question (but replacing “parameter” with “condition”, you can get a solution using the HYPERLINK() function nested into an IF() formula.
This solution should also work with a recorded macro, but I only demonstrate it using the written macros in my next attachment:
ask106745copyOrMoveDataFromInputPositionToFinal2.ods (22.8 KB)

#11

This is incredible stuff… but is there any way to insert a macro into a formula, such as:

IF(D3=D4,RuntheMacro:Standard:Module1:Main)

Something simple and stupid like that? Just a ‘CONDITION’ before a macro executes. that can be assigned to a button?

(Of course this something for the Libre Suggestion Box for Worthless Idiots, Like Most of Us, in the real world.) :slight_smile:

#13

I always deprecate incomplete IF() expressions, but basically the HYPERLINK() function as applied in cells E4 and E2 respectively of the demonstrating sheets in my example can exactly do this. However, it doesn’t “insert the macro” but creates a link (hyperlink as the name says) in URI-fashion by which you can call the macro.
The relevant difference as compared to referencing resources of the internet is the so-callled scheme, the part before the colon. Intending to call a macro from inside a LibreOffice application this must be vnd.sun.star.script . Behind the colon must follow point-separated the (case regarding) names of the Library, the Module, and the Routine, in your case Standard.Module1.Main (where the automatic names are not recommendable). Since libraries can be created for different scriptring languages, and can be provided either via the user profile (here addressed as “application”) or by the current component (“document”), the URI has in addition a Query Part separated as a whole by the question mark and internally by the ampersand into “equations” describing the needed information.

===Edit===
Having defined a few new named ranges, I recorded a slightly shortened macro and linked it into the next sheet as described above. See new attachment:
MacroTestTodayOrYesterdayQuestionmark.ods (24.5 KB)
===/Edit===

#15

Ah! I see!
So to speak…
… vnd.sun.star.script: -is like clicking Tool>Macro>Run Macro
… Standard. -is like selecting Standard in the document’s macros
… shortenedRecording. -is the name you gave the module
… recordedShort?language=Basic&location=document -is the script language and name you gave the macro inside the document
This gives me something to parrot to see if I can make it rational to me, so I can work with it.
Thank you.

#16

One last question: When I click the hyperlink, a warning pop-up comes up even though I have the lowest Macro Security option selected. Is there any way to turn that off?

Warning pop-up
Warning pop-up714×202 12.4 KB

Thanks again.

#17

You seem to be decisive about learning more. Therefore:
Since the original subject contained “Parameter” in place of “Condition” you may be interested in the fact that extensions to the Query-String adding ampersand-delimited equations is a relevant way to pass parameters to Sub-routines (not Calc UDF functions) which is generally not simple. However, the recorded part of any Sub can’t evaluate them. You would need to provide helper routines, and to insert calls to them into the “macros”.
`?language=Basic&location=document&dtappend=yes&userange=Q1:Q4
Have a lot of fun.
(The text above was already prepared. Now your add-on.)

Never do so!
The behaviour of the sofware you describe is known to me. I tend to consider it a bug to handle it this way for a not yet saved document.
However, there’s a simple workaround:
Save the document to a location of your file system and reload it with medium macro security. Now you can permit document macros for the session, and the annoying alert will no longer be shown.
If you define one or more folders as “trusted file locations” you may store to such a file location and will not be prompted for macro permission when loading from there.
Be careful, however! Malign code might otherwise sweep your storage.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.