Description: The file "test_add_list.xlsm" has a script for the Workbook_Open event, which, when opening the file, re-binds the drop-down list to the column cells (through data validation). The lists are linked with the usual VBA code. The problem is that, for reasons I don't understand, this script works correctly in Excel, and in Calc it works without errors, but the necessary directory is not linked. (screenshot) There are no problems when opening in Calc a file with an already linked list. If I save a file in which the script worked incorrectly, and then try to open it again, as in Excel and Calc, the file opens with errors, and the Workbook_open script does not work completely. If I understood correctly, then the whole difficulty is that Calc addresses other sheets in a different way. in Excel = guides!$A$3:$A$9 in Calc = $guides.$A$3:$A$9 Steps to Reproduce: 1. Open a file containing the workbook_open script that adds lists to cells. 2. Allow macros to be executed. 3. We get an incorrect result. Actual Results: We get an incorrect result (on screenshot). Expected Results: The lists must be added correctly in accordance with the VBA code. Reproducible: Always User Profile Reset: No Additional Info: The presence of this problem has been tested on LibreOffice version 7.6.4.1, on Windows 10/11 and Linux Mint 21.3 operating systems. Resetting the user profile settings was not applied.
Created attachment 194109 [details] test_add_list.xlsm Normal file, before script execute.
Created attachment 194110 [details] test_add_list_with_err.xlsm, file after script execute, contain errors
Created attachment 194111 [details] scr_err_list.jpg. Screenshot for demonstration the error after script execute.
Repro using Version: 24.2.3.2 (X86_64) / LibreOffice Community Build ID: 433d9c2ded56988e8a90e6b2e771ee4e6a5ab2ba CPU threads: 24; OS: Windows 10.0 Build 22631; UI render: default; VCL: win Locale: en-GB (ru_RU); UI: en-GB Calc: CL threaded Changing the Workbook_Open macro to use "=guides.$A$3:$A$9" instead of "=guides!$A$3:$A$9", and executing it manually, fixes the Validity drop-down to list items from 2019 to 2025. To require Calc syntax in VBA-specific calls is a bug, it must take Excel-specific syntax, and convert internally. The code pointer is ScVbaValidation::Add [1], which eventually calls XSheetCondition::setFormula1 / setFormula2 UNO API, which indeed takes Calc-standard syntax. The strings must be converted prior to passing there (see e.g. ScVbaRange::setFormulaValue [2], which is used in ScVbaRange::setFormula / setFormulaR1C1 / setFormulaLocal). [1] https://opengrok.libreoffice.org/xref/core/sc/source/ui/vba/vbavalidation.cxx?r=b3a90323#233 [2] https://opengrok.libreoffice.org/xref/core/sc/source/ui/vba/vbarange.cxx?r=0cd07e1f#1677