[Calc] References of multiple choices.

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

[Calc] References of multiple choices.

Postby Sébastien C » Wed Jan 11, 2017 12:39 am

Hello everyone,

When setting up tabular databases, it may be useful to use Calc even if it is not necessarily a purpose.
We naturally know the possibility offered by the menu "Data" / "Validity" which allows to limit certain choices.
But in some fields it is sometimes necessary to enter SEVERAL values, separated by a separator, knowing that their number is variable. To make it as concise as possible, it is sometimes necessary to use references, the shortest possible.
The example given here makes a list of materials in the "system" sheet with their references.
The macro makes it possible to call a dialog box that reads all the items of the sheet to have them in a dialog box that allows multiple selection (with mouse and [Ctrl] or [Shift] keys).
It can also be noted that the dialog box is able to read the references written in the call cell.
I opted for a dash for separating references because it is very clear. But this is a choice that can be changed in the code (and it is possible to put a separator of several characters).
The order of references does not matter and one can very well grasp the references "by the hand". But if we validate the dialog with the "OK" button, then it will be put back in the one of "system.B3:C21".

Enjoy !
The code :
Code: Select all   Expand viewCollapse view

' ╔══════════════════════════════════════════════════════════════════════════════╗
' ║ References of multiple choices.                                              ║█
' ╚══════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub multipleChoices(myAddressTable As String, myTitle As String) As String
Dim   mySheet As Object, myTableRange As Object,  myDialog As Object
Dim myLinesNb As Integer,     myLines As Integer, myLines1 As Integer
Dim  myString As String,  mySeparator As String

  mySeparator = "-"
myTableRange = thisComponent.sheets.getByName("system").getCellRangeByName(myAddressTable)
    myLinesNb = myTableRange.rows.count

Dim myListBox(myLinesNb) As String, myRefs(myLinesNb) As String, mySelections(myLinesNb) As String
Dim myChoices(myLinesNb) As Integer

With myTableRange.rangeAddress
  mySheet = thisComponent.sheets.getByIndex(.sheet)

  For myLines = .startRow To .endRow
   myLines1 = myLines - .startRow
   myListBox(myLines1) = mySheet.getCellByPosition(.startColumn, myLines).String
      myRefs(myLines1) = mySheet.getCellByPosition(  .endColumn, myLines).String

   If (myListBox(myLines1) = "") Or (myRefs(myLines1) = "") Then
    myLines1 = myLines1 - 1
    reDim Preserve    myListBox(myLines1) As String
    reDim Preserve       myRefs(myLines1) As String
    reDim Preserve mySelections(myLines1) As String
    reDim Preserve    myChoices(myLines1) As Integer
    myLinesNb = myLines1
    Exit For
   End If
  Next myLines
End With

myDialog = createUnoDialog(dialogLibraries.Standard.Dialog1)
myDialog.title = myDialog.title & myTitle
myDialog.getControl("ListBox1").addItems(myListBox(), 0)

' Sélection of the right choices if the active cell contain knowing references.
mySelections = split(thisComponent.currentSelection.String, mySeparator)
For myLines = 0 To myLinesNb
  For myLines1 = 0 to ubound(mySelections)
   If myRefs(myLines) = mySelections(myLines1) Then
    myDialog.getControl("ListBox1").selectItemPos(myLines, true)
    Exit For
   End If
  Next myLines1
Next myLines

If myDialog.execute() Then
    myString = ""
  myChoices() = myDialog.getControl("ListBox1").getSelectedItemsPos

  For myLines = 0 To ubound(myChoices())
   myString = myString & myRefs(myChoices(myLines)) & mySeparator
  Next myLines

  If len(myString) > 0 Then thisComponent.currentSelection.String = left(myString, len(myString) - len(mySeparator))
End If

End Sub

' ╔══════════════════════════════════════════════════════════════════════════════╗
' ║ Call the differents multiple choices.                                        ║█
' ╚══════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub multipleChoicesMaterial() : multipleChoices("$B$3:$C$100", "MATERIAL") : End Sub

(13.26 KiB) Downloaded 139 times
LibreOffice under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
Posts: 81
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest