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