Macro assignments not saved in Marco Enabled Sheet

English
, ,
#1

Macro assignments to buttons are not saved when saving in an existing Marco Enabled Spreadsheet.

Steps to reproduce:

  1. Using Excel, Create a Macro Enabled spreadsheet (only because I was not able to figure out how to do this in LibreOffice)
  2. Insert a button
  3. Assign a Macro that simply pops up a MsgBox(“Button Pressed”)
  4. Save the file and close
  5. Open the file in LibreOffice
  6. Verify the button assignment works
  7. Save the file within LibreOffice
  8. Close and Reopen the file in LibreOffice
  9. The Macro is no longer assigned to the button. (Although the macro is still in the spreadsheet)
  10. Reassign the Macro
  11. Verify it works
  12. Save, close, and reopen the file
  13. The macro is no longer assigned

Operating System Info
Microsoft Windows [Version 10.0.19045.5487]

Libre Office version info
Version: 24.8.4.2 (X86_64) / LibreOffice Community
Build ID: bb3cfa12c7b1bf994ecc5649a80400d06cd71002
CPU threads: 24; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL threaded

#2

xlsx or odt ?

#4

The type is XSLM.

#3

Use the ODF file types for the LibreOffice. There is not 100% compatibility. between the different file types.
You can assign the StarBasic+API macros to an Event of some Form Control Element ib the Calc application too.

Make visible the Form Controls toolbar.
Switch to Design mode: Place a button onto the Sheet, and set the Control properties - Events: Assign the macro located in the document or in the Standard Library of the LibreOffice.
Finally switch OFF the Design mode of the Form Control Toolbar.

Draw a Button:
Design mode

Assign the macro:

ControlProperties
ControlProperties1179×814 55.6 KB

#5

Unfortunately, I am unable to use ODT as the ultimate consumer of the XLSM will be using Excel.
My example is simple to recreate the problem.
In reality I am using Python to modify the macro-enabled spreadsheet and using LibreOffice because it runs on Linux and retains the macro code. Everything works up to the macro assignments.

#6

Then you must buy Excel.

#7

So…tell them ( the ultimate consumer! ) to install LibreOfffice

#8

See attached file (remove the .ods extension added due to forum restrictions).
The file MyBook_LO.xlsm is saved in LibreOffice and works correctly in Excel and LibreOffice.
MyBook_LO.xlsm.ods (9.4 KB)

1 Like
#9

but:

@sokol92 Your “Excel-compatible-Basic-Msgbox” will be of little use to him!

#10

@karolus, we regularly use documents intended for viewing and editing in Microsoft Excel and LibreOffice Calc.
Here are some recommendations (far from a complete list):

  1. Use the .xlsx, .xlsm, .xltx, .xltm document formats. The .ods and .ots formats are not suitable due to their poor support in Excel.
  2. Use only functions in formulas that are supported by both Excel and Calc. It is undesirable to use “new” functions (LET, etc.), since they are not supported in previous versions of Excel and Calc.
  3. To call macro on demand by the user, we can use Form Control button or graphic object. ActiveX controls should not be used.
  4. Macros (if any) should be created and edited in Excel. The author of the macros should take into account the specifics of how macros work under Excel and under Calc.
    Additionally (and this is unrelated to Calc), the macro author must be aware that VBA macros have numerous limitations related to Unicode support.
#11

What were your steps to get that to work?

#12

I followed your instructions.
Perhaps you inserted (in Excel) an ActiveX button in your example (see section 3 of my previous message).

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.