A Multiple Selection Dropdown List to add Multiple Tags within multiple cell ranges in different sheets and columns with ease for LibreCalc. Much appreciated if there is a way to achieve this without VBA Codes. But if not it’s acceptable.
1 Like
Hello and welcome! I will translate this example into English and post it here. But if you need it urgently and you cannot wait, then see the original solution.
Well I dont have any experience in vba codes or macros so if possible can you provide the completed version of the code that supports librecalc and also show where do i need to make changes or how so that i can set the my own required cell for the dropdown list like in which column or cell range.
Okay, now you have a few options, and they are all good. You can use the hints received to do it yourself. In this case, you will learn something and next time you will be able to solve problems without outside help. And that’s good - you will have the solution and the knowledge. Or you can wait (perhaps long) for some altruist to prepare a complete solution to the problem for you and publish it here. This is also good - you have a solution and the same solution is available to everyone. Or you can hire a freelancer on an exchange (not an exchange here!). You will receive solutions, and the freelancer will use the money you pay him to drink beer or buy a bouquet of flowers for his girlfriend. And that’s good too.
Finally, you can agree that computers and programs are not what you should be doing, and leave that to others - you will leave to make furniture or grow strawberries. And this is also good - the world will receive beautiful furniture or delicious berries and will be spared a dozen bad programs. Which option you choose depends only on you.
Perhaps you have not quite correctly understood the purpose of this resource. Here users help other users, and not do some work for free.
(And yes, I know how this problem is solved. But I will not do this. Your online store will perform better if you have a multi-select to list the characteristics of your products, but I’m bored with that)
Closing an answer for being accepted doesn’t do the accepting automatically.
You are expected to do that (in addition) explicitlly.
(You may omit the closing. It’s not of much use for visitors. Accept is.)
Okay. I want to warn you right away that this is not the best solution to the problem. A good solution requires a lot more code, it would be difficult for a beginner to understand what’s going on. Therefore, I’ll show you what needs to be done so that one and only one cell in your spreadsheet behaves like a drop-down list and at the same time contains a list of tags.
Start by creating a dropdown list. For this, Data - Validity is usually used (there are other possibilities - for example, a control ComboBox).
Select the cell in which your drop-down list will be and select the desired menu item. Set the required parameters and do not forget to uncheck Show error message when invalid values are entered in the Error Alert tab
A simple and fairly reliable way to save the previous value of a cell is a global variable in a macro: it will be available for reading and writing throughout the entire session with the spreadsheet.
Option Explicit
Global oldValue As Variant ' Store previous value of dropdown cell
Const DROPDOWN_ADDRESS = "$'Multiple Dropdown Demo'.$E$4" ' Address of dropdown cell
Private Sub on_ChangeSelection(oEvent As Variant)
Rem Each time when user do something on this sheet - store string from dropdown cell to variable
If oEvent.supportsService("com.sun.star.sheet.SheetCell") Then _
oldValue = oEvent.getSpreadsheet().getCellRangeByName(DROPDOWN_ADDRESS).getString()
End Sub
A separate procedure will handle the event of a value change in a cell:
Private Sub on_Change(oEvent As Variant) ' Call when any content of sheet changed
Dim newVal As String, oldVal As String
On Error Resume Next
If oEvent.supportsService("com.sun.star.sheet.SheetCell") Then ' If changed single cell
If (oEvent.AbsoluteName = DROPDOWN_ADDRESS) Then ' Is it our dropdown cell?
newVal = oEvent.getString() ' Save selected value
Rem If stored value isn't "empty string" then append new selected value to previous string with comma-delimiter
oldVal = Trim(oldValue)
oEvent.setString(addOrRemove(newVal, oldVal))
on_ChangeSelection(oEvent) ' Store new value to global variable
End If
End If
End Sub
This routine uses the addOrRemove(newVal, oldVal) helper function, which handles various combinations of the old cell value and the one that was just selected from the dropdown list:
Private Function addOrRemove(sNew As String, sOld As String) As String
Rem Combine old value and new
Dim i As Long, j As Long, aTemp As Variant, sTemp As String
addOrRemove = ""
If sNew = "" Then Exit Function
If sOld = sNew Then Exit Function
addOrRemove = sNew
If sOld = "" Then Exit Function
aTemp = Split(sOld,",")
For i = LBound(aTemp) To UBound(aTemp)
If aTemp(i) = sNew Then
sTemp = ""
For j = LBound(aTemp) To UBound(aTemp)
If Trim(aTemp(j)) <> "" Then
If aTemp(j) <> sNew Then
If Len(sTemp) > 0 Then sTemp = sTemp & ","
sTemp = sTemp & aTemp(j)
EndIf
EndIf
Next j
addOrRemove = sTemp
Exit Function
EndIf
Next i
addOrRemove = sOld & "," & sNew
End Function
This is all the code you need to solve the problem. Paste it into your spreadsheet:
To make this code run at the right time, assign the launch of these procedures to the events of the sheet where your multi-cell is located.
If you followed these instructions carefully, it should look something like this - Simple Multiple Dropdown.ods
I wanted to use this multi select dropdown in more that one cell like 1 or 2 columns dedicated to dropdown’s. Also when tried the marco on the document it didnt work even in a single cell.
I wanted to use this multi select dropdown in more that one cell
Study the macro in the demo at the link from the first comment - a range of cells is processed there.
it didnt work even in a single cell
Does the macro work for you in the demo attached to the answer?
Yes it work on your demo only. When I used it for my doc it didn’t work even after altering referenced cell. Well now i am just using the marco from the original link of the article you gave me from the your 1st comment. Well it only works for cell range of only 1 Column though.
Demo File: https://www.udrop.com/5Bia/MultiSelect_Dropdown.ods