Importing current date by scanning barcode [add the date the barcode is scanned]

[Vikeman4]

I would like to be able to import the current date by scanning a barcode so I’m wondering how I’d go about setting that up

[Vikeman4]

When the barcode is scanned I want the current date when the barcode is scanned, so for example if I scanned the barcode today it would import 15-05-2025 and then if I scanned the same barcode tomorrow it would import 16-05-2025

[joshua4]

So, if the barcode is ‘PRODUCT123’ after scanning you want the current cell to hold PRODUCT123 and, say, the one left of it to hold 16-05-2025 (or whatever the scan date is)?

[Vikeman4]

yes exactly

[joshua4]

Here is a solution from Gemini AI. It takes any change in a cell in column C and enters the current local date and time in the cell to the left, so in column B.
.
If you just want the date and no time, you can format away the time using cell formatting or change the Now() in the macro to Date(), or both. I thought time might be handy, so I changed Gemini’s code to Now() from Date().
.
Note that overwriting the same value is considered to be a change. Programming in more “smarts” would be pretty straightforward by hand, or by giving Gemini the code and a request for additional functionality and see what it comes up with. It batted 1000 on this one, but sometimes it hallucinates API calls against UNO (the Calc API).

Sub InsertDateToLeftOfColumnC(oEvent As Object)
    Dim oSheet As Object
    Dim oChangedCell As Object
    Dim oTargetCell As Object
    Dim iChangedColumn As Integer
    Dim iChangedRow As Integer

    ' Get the sheet where the change occurred
    oSheet = oEvent.Spreadsheet

    ' Check if the event is a single cell change (most common for direct input)
    If oEvent.supportsService("com.sun.star.sheet.SheetCellRange") Then
        oChangedCell = oEvent ' The oEvent itself is the changed cell range object

        ' Get the column and row index of the changed cell
        ' LibreOffice uses 0-based indexing for columns and rows
        ' Column C is index 2 (A=0, B=1, C=2, etc.)
        iChangedColumn = oChangedCell.CellAddress.Column
        iChangedRow = oChangedCell.CellAddress.Row

        ' Check if the changed cell is in Column C (index 2)
        If iChangedColumn = 2 Then ' Column C
            ' Get the cell to the left of the changed cell
            ' Column index for the left cell will be iChangedColumn - 1
            oTargetCell = oSheet.getCellByPosition(iChangedColumn - 1, iChangedRow)

            ' Insert the current date into the target cell
            ' Use the Date() function to get the current date as a Date type
            ' Calc automatically formats Date types into a date display based on cell format
            oTargetCell.Value = Now()
            
            ' Optional: You might want to ensure the cell is formatted as a date.
            ' If you want a specific date format, you can apply a number format to the cell.
            ' For example, for "YYYY-MM-DD":
            ' oTargetCell.NumberFormat = oSheet.NumberFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE_ISO_8601)
            ' Or if you want a custom format:
            ' Dim oNumberFormatService As Object
            ' Dim lFormatKey As Long
            ' oNumberFormatService = ThisComponent.NumberFormats
            ' lFormatKey = oNumberFormatService.addNew("DD/MM/YYYY", oTargetCell.CharLocale) ' Or use Locale object
            ' oTargetCell.NumberFormat = lFormatKey
            
        End If
    ElseIf oEvent.supportsService("com.sun.star.sheet.SheetCellRanges") Then
        ' This macro is designed for single cell changes for simplicity.
        ' If multiple cells are changed (e.g., paste operation), you'd need
        ' to iterate through oEvent.Count and oEvent.getByIndex(i)
        ' to check each changed cell individually.
        ' For now, we'll ignore multiple cell changes for this specific logic.
    End If
End Sub

How to Use [also from Gemini]:

1. Open your LibreOffice Calc document.
2. Press Alt + F11 to open the LibreOffice Basic IDE.
3. In the left pane (Libraries), navigate to your document (or “My Macros & Dialogs” if you want it globally available), then Standard (or create a new module if you prefer).
4. Paste the entire Sub InsertDateToLeftOfColumnC(...) End Sub code into the module, replacing any previous macro code if it’s the same macro name, or adding it as a new one.
5. Save the module (File > Save).
6. Close the Basic IDE.
7. Right-click on the sheet tab at the bottom of your Calc window.
8. Choose Sheet Events....
9. Select “Content changed” from the list.
10. Click the Assign Macro... button.
11. Browse to your macro (MyCellChangeMacro or InsertDateToLeftOfColumnC if you renamed it) and select it.
12. Click OK twice.

Now, test it by typing something into a cell in Column C on that sheet. The cell immediately to its left (in Column B) should automatically get the current date inserted.
.
DateInsertionEvent.ods (16.1 KB)