Calc List Box - populated with horizontal cell data problem

English
#1

For the following sheet Year (column) Week (row)
Example List Box problem sheet.ods (16.4 KB)

I make 2 list boxes. First list box for the year, second list box for the week (W). I can get Calc to populate the first list box by editing the properties of the list box… In Form Controls design mode, right click on the already created List Box, choose Control Properties, select Data tab, In “Source cell range” I wrote A2:A10

Everything is fine.

I try to do the same for the second list box which is supposed to read horizontal data, W1, W2, W3, i.e. the range B2:BA2 but the data in the list box only shows W1.
Why? And how to get it to contain all 52 weeks?

#2

The quick answer might be to use TRANSPOSE() to transpose the data, then use that result to populate your list box. However, if you could edit your question and upload the spreadsheet (edited as needed) you are working on, it would be much easier for the community to lend a hand.

#3

Thanks. I can’t see a way to upload an example sheet.
And I don’t see any transpose option in the list box features.

#4

Upload is the up-pointing arrow in the list of icons just above where you type. I thought anyone had upload privileges.

You would use the TRANSPOSE function to create a column somewhere else from the row of data you want to enter, then point the list box to that column instead of the row. You enter it with Ctrl+Shift+Enter (as an array formula).

#5

Thanks again. I’ve uploaded an example sheet.
I don’t really want to have unnecessary columns of “partial processing number crunching” data appearing on the sheet. All number crunching should take place in a non-visible way.
I’m really looking for the week selection to work the same way as the like the Year selection.

#6

The classic model is often to have a data sheet, a processing sheet, and a report sheet. There is no sin in having processing exposed. Of course, you can always just hide columns, but that’s not something I recommend in general. I’m sure the example will get some better possibilities coming in.

#7

Thanks for providing the transpose option, which I will use as a last resort.

There cannot be any reason other than oversight as to why List Boxes can’t be populated
by horizontal data as well vertical data.

#8

I looked at your example ODS. Thanks. Is there a particular reason you must use a list box control? You can easily use a horizontal data range for Data Validation. Data>Validity>Allow: Cell Range>Source: $Sheet1.$B$2:$H$2. The drop arrow will “float,” but otherwise things seem pretty similar.

Another option might be (sigh) to see if you can use the spreadsheet itself as a data source…but I think I’d transpose before that.

#9

Here is a macro solution to load list box options. It can work with any single-region range.

Option Explicit

Sub LLBTest()
	LoadListBox("Sheet1","Sheet1","$B$2:$H$2",1)
End Sub

Rem Load list box form control from range.
Rem	Range may be any single n x m region
Sub LoadListBox(ControlSheetName, DataSheetName, DataAddressName, ControlNumber As Integer)
	Dim ControlSheet As Object
	Dim SourceSheet As Object
	Dim DrawPage As Object
	Dim Control As Object
	Dim Range As Object
	Dim RangeData As Variant
	Dim Row As Long
	Dim Column As Long
	
	ControlSheet = ThisComponent.getSheets().getByName(ControlSheetName)
	SourceSheet = ThisComponent.getSheets().getByName(DataSheetName)
	DrawPage = ControlSheet.DrawPage
	Range = SourceSheet.getCellRangeByName(DataAddressName)
	RangeData = Range.getDataArray
	Control = DrawPage.getForms().getByIndex(0).ControlModels(ControlNumber)

	Control.removeAllItems
	For Row =  Range.Rows.Count - 1 To 0 Step -1
		For Column = Range.Columns.Count - 1 To 0 Step -1
			Control.insertItemText(0, CStr(RangeData(Row)(Column)))
		Next Column
	Next Row

End Sub

If you don’t want to have to press “Refresh” (see example) then you can, of course, just have the macro run from a load event, for example, so that the control is updated upon loading the ODS. (Using it on load is not tested…)

LoadListBoxFromRange.ods (25.7 KB)

1 Like
#11

Thank you joshua4.
That is certainly a nice solution and the code is easily altered to
allow me to go to W52. I wounder if the code triggered by clicking ‘refresh’
tied to opening of the List Box ?? That would be even better ! :smiley:

Much appreciated for it.

My coding ability is still very shallow and it would
have taken a LONG time (if possible) to drum it up.
I do hope the LO devs realise this deficiency and repair it.

#13

Coding LO/UNO is a very specific art, but it isn’t as “magic” as the results sometimes make it seem. If you do want to do more, search “MRI” and “resources” on this site. In the end, you kind of tap out the result like a tinker taps a flat sheet of metal into a shape.

I looked into using an event handler to reload your list boxes, and as far as I could tell the list boxes just don’t have an event for simple interaction like that. Ironically, they do have an event for when you change items, but that’s what we want to do, not what we want to have trigger what we do!

If I get some time, I might see if XScriptListener fires at the form level when a person clicks the down arrow on a list box. It would be a long shot.

#14

joshua4
Hi joshua4.
After a lot of experimenting. I have managed to add a few List Boxes with your fantastic code (tq so much!!) for various vertical and horizontally laid out data.
It seems the way to access a specific list box is via an automatically assigned yet hidden number corresponding to the sequence/count of components placed on the sheet.was placed on the sheet.
I can’t find any way to ‘discover’ this property/number/index. Do you know hot to do it by ordinary means or can it only be seen by MRI?

#15

I’m under the weather right now, and I am where I only have pure MS products…no LO. I’m happy to see about sample code, but it will be a few days. Others on the forum may respond, too, of course.

Typically when you want an object by a “hidden” handle you iterate through all the objects using that handle member as your indexing value and reading another handle and comparing it with a known value. Then you pass back the iterator indexing value to use to retrieve that object. It seems “wasteful,” but whether you do it or the API does it, it’s got to get done no matter. Keep reading…but first…

Can you post an example of where you are…an ODS that builds a list box?

I assume you are wanting to know how to find the ControlNumber to pass to the list box loader routine. Well, there must be some specific value that each list box has. What is that for you? Is it a Name value, etc.? What you’ll do is iterate over all list boxes (even all drawing objects) by control number then read and compare with the Name, or whatever value you use to distinguish your list boxes. Since you are iterating over the control number, you have that number as your iterating index, so once you get a match comparing the Name or whatever, you pass back that index. Just remember to begin with that you have to use an explicit iterating index variable, since you’ll need it to pass back–you can’t use a FOR EACH or the like. Also, since you may not be able to know the range of control numbers in use, you’ll probably need error catching so that as you iterate i from 1 to 100 (as it were) the code will just move on if a control with number i doesn’t exist.

I know this is vague, sorry. Post an example, and I’d think it will get resolved pretty quickly.

#16

Here it is…there could likely be more error control, but this is reasonable:

'Load list box form control from range.
'	Range may be any single n x m region
Sub LoadListBoxByName(ControlSheetName, DataSheetName, DataAddressName, ListBoxName)
	Dim ControlSheet As Object
	Dim SourceSheet As Object
	Dim DrawPage As Object
	Dim Control As Object
	Dim Range As Object
	Dim RangeData As Variant
	Dim Index As Long
	Dim Found As Boolean
	Dim Row As Long
	Dim Column As Long
	
	ControlSheet = ThisComponent.getSheets().getByName(ControlSheetName)
	SourceSheet = ThisComponent.getSheets().getByName(DataSheetName)
	DrawPage = ControlSheet.DrawPage
	Range = SourceSheet.getCellRangeByName(DataAddressName)
	RangeData = Range.getDataArray
	
	Found = False
	For Index = 0 To DrawPage.Count
		Control = DrawPage.getForms().getByIndex(0).ControlModels(Index)
		If Control.Name = ListBoxName Then
			Found = Control.supportsService("com.sun.star.form.component.ListBox")
			Exit For
		EndIf
	Next Index
	
	If Not Found Then Exit Sub

	Control = DrawPage.getForms().getByIndex(0).ControlModels(Index)

	Control.removeAllItems
	For Row =  Range.Rows.Count - 1 To 0 Step -1
		For Column = Range.Columns.Count - 1 To 0 Step -1
			Control.insertItemText(0, CStr(RangeData(Row)(Column)))
		Next Column
	Next Row

End Sub

LoadListBoxFromRangeViaNames.ods (18.6 KB)

#10

Yes, it is better to use cell validation directly instead of a macro.

but… if you like them as much as I do, an alternative…

	doc = ThisComponent
	sheet = doc.CurrentController.ActiveSheet
	rango = sheet.getCellRangeByName("B2:H2")
	form = sheet.DrawPage.Forms.getByIndex(0)

	lst_week = form.getByName("lst_week")
	lst_week.removeAllItems()
	For Each value In rango.DataArray(0)
		lst_week.insertItemText(lst_week.ItemCount, value(0))
	Next
#12

Thanks elmau.
To get this code to run it needs to be tied to a command button, or as I said to joshua4 could it
be tied to the event : Opening of the List Box?

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