How to rewrite VBA combo box code in LO Basic?

[ocorlett]

I imported a VBA macro from Excel, but it won’t run properly in Calc. Here’s a section of the imported code:

Private Sub UserForm_Initialize()
Dim RngTags As Range, RngNames As Range, i As Long

Set Rng1 = Sheets(“options”).Range(“options”)

With ComboBox1
.ColumnCount = 1
.Style = fmStyleDropDownList
.TextAlign = fmTextAlignLeft
.BoundColumn = 1

For i = 1 To Rng1.Count
.AddItem Rng1(i).Value
.List(.ListCount - 1, 1) = Rng1(i).Value
Next i

End With
End Sub

In Excel, this creates a dropdown list for the combo box by referencing a range [“options”] in a worksheet [also called “options”].

Running this code stepwise, as soon as it hits ColumnCount=1, it generates:

BASIC runtime error.
‘423’
Property or method not found: ColumnCount.

I can’t find anywhere hints on how I might write a routine to do the comparable operation in a Calc macro.

Can anyone point me in the right direction?

[Wanderer]

@ocorlett

can’t find anywhere hints

lmgtfy…

LibreOffice Basic - Getting all items listed in a ComboBox

Ladies and Gents, I am having the most difficult time attempting to find any explanation on how I can access each item within a ComboBox. I have created my own custom form (named “NewTransaction”) using LibreOffice Basic (v5.0.3.2). On it, I have a combobox named “cmbxCategory”. In my code module, I have the following code: Function ShowDialog_NewTransaction Dim dialogBox_NewTrans As Object BasicLibraries.LoadLibrary("Tools") Set dialogBox_NewTrans = LoadDialog("Standard", "NewTransaction"…

Or

Filling combobox in userform with range in spreadsheet

Hello I have a userform with a combobox I would like to fill this combobox with data from range of spreadshet, is it possible? Thank you LO: 5.2 Windows: 7 e 10

And
https://forum.openoffice.org/en/forum/viewtopic.php?t=48183

[elmau]

Sub Main()

	DialogLibraries.LoadLibrary("Standard")
	dialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
	combo = dialog.getControl("ComboBox1")
	
	sheet = ThisComponent.Sheets("options")
	range = sheet.getCellRangeByName("options")
	
	For Each row In range.DataArray
		combo.addItem(row(0), combo.ItemCount)
	Next
	
	dialog.Execute()
	
End Sub

[ocorlett]

I tried this. At line 2, I get:

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

I suppose somehow the standard library isn’t loaded into the current spreadsheet.
My apologies, I am a dabbler in this and not very familiar.

[joshua4]

Have you created the dialog box using the BASIC IDE? I’ve attached a very basic example that seems to work, although it won’t answer some pretty obvious questions about using the user selection from the dialog box…those are different questions than this, I think.

There are 5 parts:

1. The list of options on the spreadsheet
2. A command button on the spread sheet to open the dialog
3. The macro in the BASIC IDE that opens the dialog
4. The macro in the BASIC IDE that takes some action
5. The dialog itself, with the combo box and two buttons: action button and OK button

ComboDialogStarter.ods (14.9 KB)

[ocorlett]

Thank you for the example. It works when I run it in your spreadsheet, but if I try to run it in a new spreadsheet on spreadsheet in place of my existing VBA-derived code I get a runtime error (doesn’t recognise Dialog1).

I suspect the problem is that my spreadsheet isn’t accessing the standard LO macros? I don’t know how to fix this.

Thank you, in any case, for your response.

[joshua4]

Please post a copy of your attempt so we can see what is going on. It still sounds to me like you do not have the dialog itself in your BASIC IDE.

[ocorlett]

Thanks for taking the time. Here is my .ods with your code in Module 1. This is the message I get when I run ShowDialog():

combotest.ods (9.5 KB)

I’m not sure what you mean when you say I do not have the dialog in my BASIC IDE. Do I have to upload a standard library or something?

[joshua4]

I think it is as I suspected. You have to create the dialog itself. In the BASIC editor use the Dialog menu, then Organize Dialogs. Select Standard under your ODS in the tree pane then click the New… button to the right. You will also need to add the Combobox1 and any buttons before you can use them in code, which you also do using the Dialog menu. It can be pretty quirky.

See also: Creating a Basic Dialog