[Solved] How to use several ranges for a listbox

[Ricky]

found a forum thread on how to use several ranges for a listbox
but don't understand how to make it work.

viewtopic.php?t=44156


here is the sub

Code: Select all

Sub Snippet(Optional oInitialTarget As Object)

  Dim oSpreadsheet As Object
  Dim aBottomBorder As New com.sun.star.table.BorderLine2
  Dim oDrawPage As Object
  Dim oForms As Object
  Dim oObj_1 As Object
  Dim oObj_2 As Object
  Dim oValueBinding As Object
  Dim oValidator As Object
  Dim oListEntrySource As Object
  Dim aCellRange As New com.sun.star.table.CellRangeAddress
  Dim nSheet As Integer
  Dim nStartRow As Long
  Dim nStartColumn As Long
  Dim nEndRow As Long
  Dim nEndColumn As Long

  oSpreadsheet = oInitialTarget.Spreadsheet
  aBottomBorder = oSpreadsheet.BottomBorder
  oDrawPage = oSpreadsheet.getDrawPage()
  
  oForms = oDrawPage.getForms()
  oObj_1 = oForms.getByIndex(0)
  oObj_2 = oObj_1.getByIndex(0)
  
  oValueBinding = oObj_2.getValueBinding()
  oValidator = oObj_2.getValidator()
  oListEntrySource = oObj_2.getListEntrySource()
  
  aCellRange = oListEntrySource.CellRange
  nSheet = aCellRange.Sheet
  nStartRow = aCellRange.StartRow
  
  nStartColumn = aCellRange.StartColumn
  nEndRow = aCellRange.EndRow
  nEndColumn = aCellRange.EndColumn
  
  
End Sub

questions

1- is this sub using a specific sheet name ?

2 - how or/and where do you select a listbox on a sheet ?

3 - i suppose there must be a button somewhere to call this sub but not shown
or run from the menu for macro may be ?

4 - is it possible to define this as a Function instead of a sub

5 - after running the sub will this automatically update the listbox
or need to add another command to updating the sheet ?

6 - with this style of program
how to read a cell text content / like the combo box selection?
and transform text to a range's name or address
to be use in the sub

i included a small sample file to save time

i added a combo box with the 3 range's name already defined in file

thanks for feedback
have a nice day

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[FJCC]

The attached file shows a method for making a data validity list whose content can be changed with another data validity list. You can pick your material type in E11 and specific material in D11. The list displayed in D11 is derived from the information in B15:C33. Would something like that work for you?

[Ricky]

interesting way of doing it

can you explain how to make these 2 datas validity list ?
might be useful for other things later on

it looks like you have made one giant list of material instead of several list!
that is why i ask if it was possible to make it work like in the other thread using listbox or combo box

problem is that i have like 12 to 20 tables to deal with
so might be easier to read data from a combo box and in macro select the proper table to
update a list box


thanks for feedback

[FJCC]

You can see how I set up the data validity in D11 and E11 by clicking on one of the cells and then selecting the menu item Data -> Validity. The set up of the two cells is different, so be sure to look at both. If you have a specific question about one of the cells, please ask.

Having separate lists makes it harder to work with the data. I would put the single list of materials on a dedicated sheet and insert rows as needed to add material within the list. You can use filters to choose a subset of the data if the list gets very long.

Combo Boxes and, especially, List Boxes are intended for working with databases. Sine you do not mention a database, I think those are the wrong tools.

Learning to write macros takes a lot of time. If you really want to do that, I can refer you to some helpful documentation, but I think you will get to a solution much faster using the method I have suggested.