Ask Your Question
0

Filling combobox in userform with range in spreadsheet

asked 2016-11-17 10:13:53 +0200

Iniziato gravatar image

updated 2016-11-17 10:17:25 +0200

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2016-11-17 20:05:35 +0200

mark_t gravatar image

If the combo box is in a Dialog or Userform then you need to populate the list for the combobox in the macro that initialises the Dialog.

Simple example below expects the range to initialise the combo box list to be in a vertical column range of cells.

Sub StartDialog

    Dim oList As Object
    Dim n As Integer
    Dim i As Integer
    Dim oDialog As Variant
    Dim oData As Variant

    ' Code for initiating and showing the dialog    '   
    DialogLibraries.LoadLibrary("Standard")
    oDialog = CreateUnoDialog( DialogLibraries.Standard.ImportTextFormat )

    oList = oDialog.getControl("FormatList")

    oList.Text = "Select text input format from the list."

    ' Read the data list from cell range into a variant array '
    oData = ThisComponent.Sheets(0).getCellRangeByName("C7:C9").DataArray

    n = ubound(oData)

    For i = 0 to n
        oList.addItem(oData(i)(0) ,i)
    Next i

    oDialog.Execute()
End Sub
edit flag offensive delete link more

Comments

Thank you Mark :D

Iniziato gravatar imageIniziato ( 2016-11-18 08:11:24 +0200 )edit
0

answered 2016-11-17 11:28:39 +0200

pierre-yves samyn gravatar image

updated 2016-11-17 15:24:01 +0200

Hi

Yes, with Combo Box and ListBox you can define the Source cell range in the Data tab (design mode):

image description

Note: as you can see in the screenshot linked cell (B1 in this example) is a text, even if the source data range contains numbers.

[EDIT]

I join the Combo.ods example so you can check the form.

Regards

edit flag offensive delete link more

Comments

I'm sorry Pierre, but in your example the combobox is not in a userform

The "Data" Tab is not in useform combobox

Iniziato gravatar imageIniziato ( 2016-11-17 14:47:52 +0200 )edit

@Iniziato - When you create a combo box or a drop-down list, or whatever the type of control, a form is always created to which these controls are attached. To verify this, display the Form Design toolbar and use Form Navigator tool. That said, I may not have understood your question, but in this case you should give more precision on your form.

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-11-17 15:22:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-17 10:13:53 +0200

Seen: 1,019 times

Last updated: Nov 17 '16