Seleccionar varios elementos de una lista desplegable en Excel

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:

seleccione varios elementos de una lista desplegable a Excel: selecciones múltiples

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:

  1. Seleccione la celda o el intervalo de celdas donde desee que aparezca la lista desplegable (C2 en este ejemplo).Datos para las que desea crear el menú desplegable
  2. Vaya a Datos -> Herramientas de datos -> Validación de datos.hacer varias selecciones en una lista desplegable a Excel: validación de datos
  3. En el cuadro de diálogo Validación de datos, en la pestaña Configuración, seleccione “Lista” como criterios de validación.seleccione varios elementos de una lista desplegable a Excel - Lista
  4. En el campo Fuente, seleccione las celdas que tengan los elementos que desee en el menú desplegable.selección de varios elementos de una lista desplegable de Excel: datos de origen
  5. 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).

El menú desplegable a C2 permite selecciones individuales

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).

seleccione varios elementos de una lista desplegable a Excel: selecciones múltiples

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).

Seleccione varios elementos de una lista desplegable a Excel, sin repetició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:

  1. 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.Selección de Visual basic en la pestaña Desarrollador
  2. Debería haber un panel de Project Explorer a la izquierda (si no está, utilice Control + R para hacerlo visible). seleccione varios elementos de una lista desplegable a Excel: Project Explorer
  3. 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.seleccione varios elementos de una lista desplegable a Excel - Ventana de código
  4. En la ventana del código, copia y pega el código anterior.Pegue el código para permitir varias selecciones los desplegables de Excel
  5. Cierre el editor VB.

Ahora, cuando vuelve al menú desplegable y haga selecciones, le permitirá hacer varias selecciones (como se muestra a continuación):

Menú desplegable resultante en que puede elegir más de un elemento

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 

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *