Uno de mis compañeros me preguntó si es posible hacer varias selecciones en una lista desplegable de Excel.
Al crear una lista desplegable, sólo puede hacer una selección. Si selecciona otro elemento, el primer se sustituirá por la nueva selección.
Quería hacer varias selecciones desde el mismo menú desplegable de modo que las selecciones se añadieran al valor ya presente en la celda.
Algo como se muestra a continuación en la imagen:
No hay ninguna manera de hacerlo con las funciones integradas de Excel.
La única manera es utilizar un código VBA, que se ejecuta siempre que haga una selección y añada el valor seleccionado al valor existente.
Cómo hacer varias selecciones en una lista desplegable
En este tutorial, te mostraré cómo hacer varias selecciones en una lista desplegable de Excel (con repetición y sin repetición).
Este ha sido uno de los tutoriales de Excel más populares de este lugar. Como tengo muchas preguntas similares, he decidido crear una sección de preguntas frecuentes al final de este tutorial. Por lo tanto, si tiene alguna pregunta después de leerlo, consulte primero la sección de preguntas frecuentes.
La creación de una lista desplegable permite dos selecciones:
- Creación de la lista desplegable.
- Añadir el código VBA al back-end.
Creación de la lista desplegable en Excel
Estos son los pasos para crear una lista desplegable a Excel:
- Seleccione la celda o el intervalo de celdas donde desee que aparezca la lista desplegable (C2 en este ejemplo).
- Vaya a Datos -> Herramientas de datos -> Validación de datos.
- En el cuadro de diálogo Validación de datos, en la pestaña Configuración, seleccione “Lista” como criterios de validación.
- En el campo Fuente, seleccione las celdas que tengan los elementos que desee en el menú desplegable.
- Haga clic en Aceptar.
Ahora, la celda C2 tiene una lista desplegable que muestra los nombres de los elementos a A2: A6.
A partir de ahora, tenemos una lista desplegable donde puede seleccionar un elemento a la vez (como se muestra a continuación).
Para habilitar este menú desplegable que nos permita hacer varias selecciones, tenemos que añadir el código VBA en la parte posterior.
Las dos secciones siguientes de este tutorial te proporcionarán el código VBA para permitir varias selecciones en la lista desplegable (con y sin repetición).
Código VBA para permitir varias selecciones en una lista desplegable (con repetición)
A continuación se muestra el código VBA de Excel que nos permitirá seleccionar más de un elemento de la lista desplegable (permitiendo repeticiones a la selección):
Private Sub Worksheet_Change(ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com ' To make mutliple selections in a Drop Down List in Excel Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$C$2" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & ", " & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Ahora deberá colocar este código en un módulo del Editor VB (como se muestra a continuación en la sección “Dónde poner el código VBA”).
Cuando haya colocado este código en dorsal (se tratará más adelante en este tutorial), le permitirá hacer varias selecciones en el menú desplegable (como se muestra a continuación).
Tenga en cuenta que si selecciona un elemento más de una vez, se introducirá de nuevo (se permite la repetición).
Código VBA para permitir varias selecciones en una lista desplegable (sin repetición)
Mucha gente ha estado preguntando sobre el código para seleccionar varios elementos de una lista desplegable sin que se repita.
He aquí el código que le asegurará que un elemento sólo se pueda seleccionar una vez que no haya repeticiones:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Ahora deberá colocar este código en un módulo de VB Editor (como se muestra en la siguiente sección de este tutorial).
Este código le permite seleccionar varios elementos de la lista desplegable. Sin embargo, sólo podrá seleccionar un elemento sólo una vez. Si intenta volver a seleccionarlo, no pasaría nada (como se muestra a continuación).
Dónde poner el código VBA
Antes de comenzar a utilizar este código en Excel, debe ponerlo en el fondo, de manera que se active siempre que haya algún cambio en la selección desplegable.
Siga los pasos siguientes para poner el código VBA al fondo de Excel:
- Ve a la pestaña Desarrollador y haga clic en Visual Basic (también puede utilizar el atajo de teclado – Alt + F11). Esto abrirá el Editor de Visual Basic.
- Debería haber un panel de Project Explorer a la izquierda (si no está, utilice Control + R para hacerlo visible).
- Haga doble clic en Nombre de la hoja de trabajo (en el panel izquierdo) donde reside la lista desplegable. Esto abre la ventana de código para la hoja de trabajo.
- En la ventana del código, copia y pega el código anterior.
- Cierre el editor VB.
Ahora, cuando vuelve al menú desplegable y haga selecciones, le permitirá hacer varias selecciones (como se muestra a continuación):
Nota: Como usamos un código VBA para hacerlo, debe guardar el libro con una extensión .xls o .xlsm.
Preguntas más frecuentes (FAQs)
He creado esta sección para responder a algunas de las preguntas más frecuentes sobre este tutorial y el código VBA. Si tiene alguna pregunta, te pido que primero haga un seguimiento de esta lista de consultas.
Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells? Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then
Q: I need to create multiple drop-downs in entire column 'C'. How do I get this for all the cells in the columns with multi-select functionality? Ans: To enable multiple selections in drop-downs in an entire column, replace the following line in the code: If Target.Address = "$C$2" Then with this line: If Target.Column = 3 Then On similar lines, if you want this functionality in column C and D, use the below line: If Target.Column = 3 or Target.Column = 4 Then
Q: I need to create multiple drop-downs in a row. How can I do this? Ans: If you need to create drop-down lists with multiple selections in a row (let's say the second row), you need to replace the below line of code: If Target.Address = "$C$2" Then with this line: If Target.Row = 2 Then Similarly, if you want this to work for multiple rows (let's say second and third row), use the below line of code instead: If Target.Row = 2 or Target.Row = 3 Then
Q: As of now, the multiple selections are separated by a comma. How can I change this to separate these with space (or any other separator). Ans: To separate these with a separator other than a comma, you need to replace the following line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of VBA code: Target.Value = Oldvalue & " " & Newvalue Similarly, if you want to change comma with other character, such as |, you can use the following line of code: Target.Value = Oldvalue & "| " & Newvalue
Q: Can I get each selection in a separate line in the same cell? Ans: Yes you can. To get this, you need to replace the below line of VBA code: Target.Value = Oldvalue & ", " & Newvalue with this line of code: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine inserts a new line in the same cell. So whenever you make a selection from the drop-down, it will be inserted in a new line.
Q: Can I make the multiple selection functionality work in a protected sheet? Ans: Yes you can. To get this done, you need to do two things: Add the following line in the code (right after the DIM statement): Me.Protect UserInterfaceOnly:=True Second, you need to make sure the cells - that have the drop-down with multiple selection functionality - are not locked when you protect the entire sheet. Here is a tutorial on how to do this: Lock Cells in Excel