[Calc] References of multiple choices.

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 section is not for asking questions about writing your own macros.
Post Reply
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

[Calc] References of multiple choices.

Post by Sébastien C »

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 !
:D
The code :

Code: Select all


' ╔══════════════════════════════════════════════════════════════════════════════╗
' ║ 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

 dialogLibraries.LoadLibrary("Standard")
 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

 myDialog.dispose
End Sub


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

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

Attachments
referencesMultipleChoices.ods
(13.26 KiB) Downloaded 286 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply