Using userform in Libre calc

[Ecoex]

Hi, I’m new here, and I have no idea how to use LibreOffice Calc. I’m trying to adapt my .xlsm file to work on LibreOffice Calc. The thing is, I have a userform in my file, and I can’t find a way to make it work.

When I open the file for the first time, I can open/close my userform, and the codes work as they are supposed to. However, when I’m done working on my spreadsheet, I’m forced to choose between Excel 2007-365 (macro-enabled) or ODF formats when I try to save the file.

If I go with Excel, all my macros disappear (the file size goes from 45kb to 28kb), and if I choose ODF, I can’t use my userform (It shows an error).

Is there a way to fix this?"

For the error message part:

Basic runtime error.
‘423’
Property or method not found: Show.

Thanks in advance.

002.ods (22.9 KB)

[fpy]

what if in Excel you save as ODF and reopen ? then go to LO …

[Ecoex]

Doing that I lose all my macros and my Userform. As far as I understand, the userform is the problem.

Option VBASupport 1
Sub ShowForm()

    UserForm1.Show

End Sub

I’m not sure, but it seems that you can’t use excel’s userform in Calc, you need to create everything using codes. Not sure tho, if anyone could confirm I would appreciate it.

[jungcp00]

You should create a [dialog](=userform in VBA). And define macros that is different from VBA.

example

Sub ShowDialog()
      Dim oLibContainer As Object, oLib As Object
      Dim oInputStreamProvider As Object
      'Dim oDialog As Object
      Const sLibName = "Standard"
      Const sDialogName = "Dialog1"
      REM library container
      oLibContainer = DialogLibraries
      REM load the library
      oLibContainer.loadLibrary( sLibName )
      REM get library
      oLib = oLibContainer.getByName( sLibName )
      REM get input stream provider
      oInputStreamProvider = oLib.getByName( sDialogName )
      REM create dialog control
      oDialog = CreateUnoDialog( oInputStreamProvider )
      REM show the dialog
'      oDialog.execute()

  oSel = ThisComponent.getCurrentSelection 
  oSheet = ThisComponent.Sheets(oSel.RangeAddress.Sheet)
  oRange = ThisComponent.getCurrentSelection()
	' code sheet -----------------------------------
	rSheet = ThisComponent.Sheets.getbyname("code")
	'msgbox rSheet.getCellByPosition(0,0).string
	rCellCursor = rSheet.createCursor()
	rCellCursor.gotoEndOfUsedArea( False )
	nLastRow = rCellCursor.getRangeAddress().EndRow
	'msgbox nLastRow
	'code sheet ------------------------------------	
  aCellAddress = oRange.getCellAddress()
  nRow = aCellAddress.Row

	oTF = oDialog.GetControl("Text1")
	oTFa = oDialog.GetControl("tf_A")
	oTFb = oDialog.GetControl("tf_B")
	oTFc = oDialog.GetControl("tf_C")
	oTFd = oDialog.GetControl("tf_D")
	oTFe = oDialog.GetControl("tf_E")
	oTFf = oDialog.GetControl("tf_F")
	oTFg = oDialog.GetControl("tf_G")
	oTF.Text = nRow
	oTFa.Text = oSheet.getCellByPosition(0,nRow).string
	oTFb.Text = oSheet.getCellByPosition(1,nRow).string
	oTFc.Text = oSheet.getCellByPosition(2,nRow).string
	oTFd.Text = oSheet.getCellByPosition(3,nRow).string
	oTFe.Text = oSheet.getCellByPosition(4,nRow).string
	oTFf.Text = oSheet.getCellByPosition(5,nRow).string
	oTFg.Text = oSheet.getCellByPosition(6,nRow).string
	'oSB1 = oDialog.GetControl("SpinButton1")
	'oSB1.value = nRow
	oTimeF = oDialog.GetControl("TimeField1")
	oTimeF.text = format(Now(),"hh:mm")
	
	Call Combobox1_refresh

	'However, this is not sufficient to change the color 
	'because by default the LibreOffice dialogs adopts the Ubuntu/Linux desktop themes. 
	'The button colors remains same even if you execute above codes. 
	'To solves this, you have to turn off the NativeWidgetLook property of the entire dialog. 
	'Once you do that, you can change the look of the controls inside the dialog as per your needs. 
	'For our example, using below code you can turn it off.
	oDialog.getPeer().setProperty( "NativeWidgetLook", False )

      oDialog.execute()
	
	'oB1 = oDialog1.GetControl("cbClear")
	'with oB1.getModel
  	'	.BackgroundColor=RGB(255,0,0)
	'End With

End Sub

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]