Ask Your Question
1

Macro / Real Time "compare" function for calc

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

maxmax27 gravatar image

updated 2017-09-25 19:47:03 +0200

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 close merge delete

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
Login/Signup to Answer

Question Tools

2 followers

Stats

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

Seen: 174 times

Last updated: Sep 29 '17