[Solved] Cell Text into ComboBox

[Tobin]

Howdy,

I'm trying to use a For Next loop to get some cell text into a ComboBox. I seem to be missing something. Attached is my CombpBox.ods example.

Can anybody help?

[FJCC]

I don't think you can do this with a For Next loop. I found some code that works and I reference the source at the top of my code. See if this makes sense and post any questions you have.

Code: Select all

' Lifted from http://www.oooforum.org/forum/viewtopic.phtml?t=21265
Dim vForms, Form
Dim Sheet as object
Dim CellRange as object
Dim initParam(0) as new com.sun.star.beans.NamedValue 
Dim Box as Object
Dim CellRangeListSource as Object
Sheet = ThisComponent.Sheets(0)
CellRange = Sheet.getCellRangeByPosition(0,0,0,5)
vForms = ThisComponent.CurrentController.ActiveSheet.DrawPage.Forms
Form = vForms.getByName("Standard")
Box = Form.getByIndex(0)
initParam(0).Name = "CellRange"
initParam(0).value = CellRange.RangeAddress
   
   'create CellRangeListSource object that points to new data for ComboBox
oCellRangeListSource = _
ThisComponent.createInstanceWithArguments("com.sun.star.table.CellRangeListSource", initParam())
   
   'Update ComboBox with new data
Box.setListEntrySource(oCellRangeListSource) 

[Tobin]

WOW! That worked flawlessly.
That's way more complicated than I thought it might be. I would have never guessed that on my own.
I really appreciate your help.
I have a totally unrelated question. How do you create the "code" box, with the "Select All" and "Expand View" options in your posts?
I'd like to be able to use that too.

Thanks again FJCC for your valuable help

[FJCC]

I admit the solution was much different than I expected. I'm surprised the ComboBox doesn't have a more straightforward get-the-list-from-this-cell-range method.

To get the code box in your reply, you type your code into the usual text box for the reply, then highlight it and click the Code button that is in the list of buttons between the Subject box and the text box. It will not look like a code box until you submit it.

[Tobin]

Thanks Again FJCC!
After reviewing your post and the reference you noted this is the code I will be using: (and probably any other ComboBox I need)

Code: Select all

Sub LoadComboBox

Dim oForms as object, Sheet as object, CellRange as object
Dim initParam(0) as new com.sun.star.beans.NamedValue
Dim cbo_PrtNum as Object, oCellRangeListSource as Object

' Point to collection of form controls on current worksheet
oForms = ThisComponent.CurrentController.ActiveSheet.DrawPage.Forms.getByIndex(0)

' get cell range for new data to be inserted into ComboBox
Sheet = ThisComponent.Sheets(0)
CellRange = Sheet.getCellRangeByPosition(0,0,0,5) ' A1:A6

' prepare to create a CellRangeListSource object
initParam(0).Name = "CellRange"
initParam(0).value = CellRange.RangeAddress

' create CellRangeListSource object that points to new data for ComboBox
oCellRangeListSource = _
ThisComponent.createInstanceWithArguments("com.sun.star.table.CellRangeListSource", initParam())
   
' Update ComboBox with new data
oForms.getByName("cbo_PrtNum").setListEntrySource(oCellRangeListSource)

End Sub

Thanks you've been very helpful