Multiple drop-down selection is a useful feature in Microsoft Excel and requires Visual Basic to function properly, which has led to some challenges.
Challenges with multiple drop-down select
Multiple drop-down selections require Visual Basic to work. The web-based version of Excel CANNOT do multiple select drop-downs as it doesn't support Visual Basic.
Because multiple drop-down selections require Visual Basic to work, you must first save the Excel file as a macro-enabled workbook (.xlsm) instead of the traditional Workbook format (.xlsx).
Because the web-based version of Excel doesn't support this, you must use co-authoring (https://support.microsoft.com/en-us/office/collaborate-on-excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104).
Most of the Visual Basic examples are wrong, e.g. they allow multiple drop-down select but do not allow you to unselect for example. The Visual Basic below supports this.
Creating or modifying an Excel file to support multiple drop-down select
Save the file as a macro-enabled Excel workbook
First save the file as a Macro enabled workbook, .xlsm, and then get rid of the previous one.
Enable Developer in the Excel ribbon interface.
You will need to enable Developer in the ribbon interface to add the Visual Basic
Add the needed Visual Basic to the file, once for each sheet that needs it.
Add the Visual Basic to the appropriate sheet(s)
Click "Developer" and then "Visual Basic"
Then cut and paste the script into the needed sheet(s):
Then, save the file and upload it to the appropriate location.
The Visual Basic needed is:
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
Dim multiSelectColumns As Range
' Define the delimiter
DelimiterType = ", "
' Exit if more than one cell is changed
If Destination.Count > 1 Then Exit Sub
' Define the explicit list of columns (V, W, X, Y, Z, AA)
Set multiSelectColumns = Union(Me.Range("V:V"), Me.Range("W:W"), Me.Range("X:X"), Me.Range("Y:Y"), Me.Range("Z:Z"), Me.Range("AA:AA"))
' Check if the changed cell is in the multi-select columns
If Intersect(Destination, multiSelectColumns) Is Nothing Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
' Exit if the changed cell is not a drop-down cell
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then Exit Sub
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
' If oldValue is not empty
If oldValue <> "" Then
If newValue <> "" Then
' Check if the value already exists
If InStr(1, oldValue, newValue) > 0 Then
' If it exists, remove it
oldValue = Replace(oldValue, DelimiterType & newValue, "")
oldValue = Replace(oldValue, newValue & DelimiterType, "")
oldValue = Replace(oldValue, newValue, "")
Destination.Value = oldValue
Else
' If it doesn't exist, add it
Destination.Value = oldValue & DelimiterType & newValue
End If
End If
End If
exitError:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' This can remain empty or used for other purposes
End Sub
Upload the file to Sharepoint or OneDrive
Opening the File to edit with multiple select
In order to use multiple drop-downs select in Excel, Visual Basic is required; this means you CANNOT use the web-based version of Excel. You must use the desktop application version. In order for co-authoring, multiple people working on the file, to work you will need to enable auto-save.
Ensure autosave is on
Go to File > Options > Save. Check that the AutoSave box is ticked.
Editing the file using the application version (desktop and mobile) not the web version
Ensure you use the application version (desktop and mobile), not the web interface version as it doesn't support Visual Basic.
People will then need to edit it using the desktop version of Excel, not the web version, to do this right click on the file, choose Open, Open in App.
I would NOT suggest clicking the checkbox "Do not ask me again for network files", just hit yes so it applies to this one document.
Just a quick note: you can add and remove things from the drop-down lists, but because adding things triggers the Visual Basic you can't hit Ctrl-Z (undo) to remove it, you'll need to select the drop-down item and deselect the item you want to remove.
What to do if Macros are disabled
Macros may be disabled (a red bar with a warning), if this is the case you may need to enable the document as a trusted source, or enable Macros. If you are using a corporate version of Office they may have Macros disabled entirely
Comments
0 comments
Please sign in to leave a comment.