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/t/macro-real-time-compare-function-for-calc/27937
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
