Macro / Real Time "compare" function for calc


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.

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:

	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( "" )
			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( "" )
				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


Thank You! it works perfectly!

Really… Thank You!