Ask Your Question
1

Macro / Real Time "compare" function for calc [closed]

asked 2017-09-25 19:46:22 +0200

maxmax27 gravatar image

updated 2020-07-29 12:02:56 +0200

Alex Kemp gravatar image

Hello,

i am in the needing to find a solution for resolving a scenario in libreoffice calc:

in column A the user will insert strings composed by 20 characters of combined letters and numbers; each time the user will press the "enter" key...

IF entered value already exist in column A:

  1. i need a acoustic warning
  2. nothing else - nothing to insert - no focus change

IF entered value does NOT exist in column A:

  1. write/insert the value in current row / column
  2. acoustic confirmation
  3. insert a new row under current
  4. focus on the new cell (always column A) under the last just written

i am totally at 0 in developing code / extesions for libreoffice...

thank you. regards.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-29 12:03:28.219363

2 Answers

Sort by » oldest newest most voted
2

answered 2017-09-27 10:19:08 +0200

librebel gravatar image

updated 2017-09-27 10:20:41 +0200

Hello @maxmax27,

Please try if the following method works for you: Just copy-paste it to your [My Macros and Dialogs].Standard Basic library, and then connect it to the Sheet's "Content Changed" event:

Sub on_Content_Changed( oCell As Object )
REM Assign this macro to the "Content Changed" event in the menu "Sheet : Sheet Events...".
REM This checks if the value entered into column A is unique, and produces a sound.
REM See: https://ask.libreoffice.org/en/question/132644/macro-real-time-compare-function-for-calc/

    Dim aRangeAddress As Object  : aRangeAddress = oCell.getRangeAddress()
''  If aRangeAddress.Sheet = 0 Then             REM Is this Cell located on the first Sheet?
        If aRangeAddress.StartColumn = 0 Then   REM Is this Cell located in Column A?

            Dim oSheet As Object : oSheet = oCell.getSpreadSheet()
            Dim oRange As Object : oRange = oSheet.getCellRangeByName( "A:A" )
            Dim oArgs            : oArgs  = Array( oRange, oCell.String )
            Dim oFunc As Object  : oFunc  = createUnoService( "com.sun.star.sheet.FunctionAccess" )
            Dim lNum As Long     : lNum   = oFunc.CallFunction( "COUNTIF", oArgs )

            If lNum > 1 Then     REM If the entered value already exists in column A;

                REM Play laser.wav; No idea if this works on other systems; else just replace it with Beep...
                REM See the SideBar Gallery:Sounds for other file names instead of "laser.wav".
                Dim oPath        : oPath   = CreateUnoService( "com.sun.star.util.PathSubstitution" )
                Dim sSound       : sSound  = oPath.substituteVariables( "$(inst)/share/gallery/sounds", True ) & "/laser.wav"
                Shell( "play '" & ConvertFromURL( sSound ) & "'" )

            Else                 REM If the entered value does NOT exist in column A;

                Beep    REM Play a system-dependent Beep tone; you cannot modify its volume.
                oRange.Rows.insertByIndex( aRangeAddress.EndRow + 1, 1 )    REM Insert a new Row below the current one.
                ThisComponent.CurrentController.Select( oRange.getCellByPosition( 0, aRangeAddress.EndRow + 1 ) )

            End If
        End If
''  End If
End Sub

HTH, lib

edit flag offensive delete link more
0

answered 2017-09-29 13:52:34 +0200

maxmax27 gravatar image

Hello!

Thank You! it works perfectly!

Really... Thank You!

edit flag offensive delete link more

Question Tools

2 followers

Stats

Asked: 2017-09-25 19:46:22 +0200

Seen: 436 times

Last updated: Sep 29 '17