Bug 118276 - FILEOPEN XLSX Basic code with Validity and Sheets does not work in Calc
Summary: FILEOPEN XLSX Basic code with Validity and Sheets does not work in Calc
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
6.2.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: Macro-VBA Cell-Validity
  Show dependency treegraph
 
Reported: 2018-06-20 16:32 UTC by Gabor Kelemen
Modified: 2019-06-13 22:41 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file from Excel with a Validity value set as macro condition (24.75 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-06-20 16:32 UTC, Gabor Kelemen
Details
The example file saved by Calc (22.71 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-06-20 16:32 UTC, Gabor Kelemen
Details
The original and the saved file in Excel and Calc side by side (499.88 KB, image/png)
2018-06-20 16:37 UTC, Gabor Kelemen
Details
The original and the exported files structure in Excel (427.20 KB, image/png)
2018-06-20 16:38 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2018-06-20 16:32:00 UTC
Created attachment 142969 [details]
Example file from Excel with a Validity value set as macro condition

If we try to run the macro in the attached file, the Sheets will not hide if we set the L9 cell to OK using validity setting and the Object Catalog will not work correctly.

Steps to reproduce:
1. Create a new spreadsheet with Microsoft Excel 2016/2013
2. Set a validity for a cell (“L9”) and choose the List type. Set two value (OK and NOT OK)
3. Create 8 Sheets.
4. Add this macro code to the Sheet1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("L9").Address Then
    If Target.Value = "OK" Then
        Sheets("Munka8").Visible = True
        Sheets("Munka7").Visible = True
        Sheets("Munka6").Visible = True
        Sheets("Munka5").Visible = True
    Else
        Sheets("Munka8").Visible = False
        Sheets("Munka7").Visible = False
        Sheets("Munka6").Visible = False
        Sheets("Munka5").Visible = False
    End If
End If
End Sub

5. Set the value of the “L9” cell to “OK” and NOT OK. (It will work)
6. Save the file as *.XLSM.
7. Open the file with LibreOffice Calc and set the value of the “L9” cell to “OK” and “NOT OK”. (It will not work)
8. Save as the file in LibreOffice Calc.
9. Open in Microsoft Excel.

Actual results: The macro does not work. If we open the file in LibreOffice Calc and open the Object Catalog, we can see the Document Objects. But If we double click on the “Sheet1” (we use a macro here) LibreOffice adds a new Sheet1 module to the Modules and we cannot open our macro code from the Object Catalog. If we save the file and reopen with Microsoft Excel the Object Catalog will be completely wrong.

Expected results:
The macro should work and the Object Catalog should work correctly if we want to open a Document Object.


Version: 6.0.0.0.beta1+
Build ID: 29228e83df009cf76ac819ed024527be1092f065
CPU threads: 4; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:libreoffice-6-0, Time: 2017-12-04_23:15:34
Locale: hu-HU (hu_HU); Calc: group threaded
Comment 1 Gabor Kelemen 2018-06-20 16:32:24 UTC
Created attachment 142970 [details]
The example file saved by Calc
Comment 2 Gabor Kelemen 2018-06-20 16:37:04 UTC
Created attachment 142971 [details]
The original and the saved file in Excel and Calc side by side
Comment 3 Gabor Kelemen 2018-06-20 16:38:29 UTC
Created attachment 142972 [details]
The original and the exported files structure in Excel
Comment 4 Buovjaga 2018-06-27 13:20:36 UTC
Repro with document

Win 8.1 32-bit
MSO 2013
LibreOffice Version: 6.2.0.0.alpha0+
Build ID: e1a77d0affef507d597e7dceb5514073658332df
CPU threads: 4; OS: Windows 6.3; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-06-27_04:47:54
Locale: fi-FI (fi_FI); Calc: group threaded