Lista desplegable con combobox dinámico

En el pasado mostramos que una de las limitaciones de crear listas desplegables con Validación de Datos es la falta de la propiedad "autocompletar". En una nota del año 2008 mostramos como usar un cuadro combinado (combobox) de la colección de controles ActiveX para superar esta limitación.

Desde entonces he recibido muchas consultas sobre el tema. La más común es cómo hacer que el control aparezca en la celda activa y desaparezca después de haber elegido el valor.

Es decir, queremos mimetizar el comportamiento de las listas creadas con validación de datos, pero usando el cuadro combinado para disfrutar de la propiedad "autocompletar".

La técnica para hacerlo incluye, obviamente programar eventos (Vba). Mostraré aquí un ejemplo sencillo, que puede aplicarse a todo modelo. En nuestro ejemplo definimos un rango que contiene los nombres de los días de la semana, que será la fuente de los valores del combobox, y un rango en la hoja donde queremos utilizar el control.

Los pasos son los siguientes:

1 – Creamos un nombre definido que se refiere al rango que contiene los días de la semana

En este ejemplo usamos el cuadro de nombres para crear el nombre con facilidad. El rango está en la hoja "valores".

2 – En la hoja "lista" incrustamos un control cuadro combinado (combobox) de la colección de controles ActiveX

Ubicamos el control en el lugar deseado (en nuestro caso sobre la celda B2) asegurándonos que ocupe toda la celda.
En la propiedad ListFillRange ponemos el nombre definido que se refiere a la lista de valores (dia_semana).

De ser necesario cambiamos la definición de la fuente a un tamaño adecuado cambiando la definición de la propiedad Font.

Al finalizar el proceso desactivamos el botón "Modo Diseño" (lo controles no funcionan cuando Excel se encuentra en modo de diseño).

3 – Definimos el rango de la hoja "Lista"", donde queremos que aparezcan los controles cuando seleccionamos alguna celda del rango. En nuestro ejemplo el rango es B2:B20 ("rngDia")

4 – Programamos el evento Worksheet_SelectionChange en la hoja "lista" de manera que cuando el usuario seleccione alguna celda del rango "rngDia", el cuadro combinado aparezca sobre esa celda. El código, que va en el módulo de la hoja, es el siguiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

Lo que hace este código lo siguientes:
- Vuelve visible el control (cuando la celda activa no está en el rango deseado, el control es invisible, lo que hacemos definiendo la propiedad Visible como False)
- Definimos la propiedad Top del control de manera que coincida con el ángulo superior izquierdo de la celda activa
- Definimos que celda debe recibir el valor elegido (la celda activa).
La última línea del código oculta el control si la celda activa no pertenece al rango donde queremos que aparezca la lista desplegable.

El modelo funciona así

Podemos mejorar este código de manera que al alto y ancho del control se adapten dinámicamente el alto y ancho de la celda, definiendo las propiedades Height y Wide en el código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

De la misma manera podemos definer dinámicamente otras propiedades como el contenido de la lista (ListFillRange), el número de valores a mostrar en el cuadro (ListRows), etc.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Intercambiar rangos en Excel

En todo proceso de diseño de una hoja en Excel es prácticamente inevitable tener que reordenar rangos (filas, columna o celdas).
Por lo general nos basta con arrastrar el rango a la posición deseada. Pero en no pocos casos debemos intercambiar posiciones, poner un rango en lugar del otro.
La técnica más común es usar Copiar (o Cortar) y Pegar, usando una ubicación transitoria, o arrastrando una de las columnas (o filas o celdas) a un área "libre" de la hoja y moviendo luego los objetos a la posición deseada

Una forma más eficiente es seleccionar uno de los rangos y manteniendo apretada la tecla Mayúsculas (Shift), apuntar con el mouse al borde indicado y arrastrar el rango a la posición del rango a intercambiar

Lo mismo puede hacer con celdas o filas.

En ciertas situaciones estas técnicas pueden ser engorrosas, en particular cuando queremos realizar el intercambio entre celdas que se encuentran en distintas columnas. Por ejemplo, si tenemos una lista de turnos de trabajo, donde los operarios piden con cierta frecuencia cambiar su turno:

Con un poco de código Vba (macros) podemos crear una herramienta útil para intercambiar celdas con facilidad:

Sub InterCambiarCeldas()
    Dim rngCell2 As Range
    Dim cellTemp As String
   
    'verificar que se haya elegido solo una celda
    If Selection.Count > 1 Then
        MsgBox "Debe elegirse solo una celda", vbCritical
        Exit Sub
    End If
   
    'verificar que la celda contenga un valor
    If Len(ActiveCell) = 0 Then
        MsgBox "Celda vacia", vbExclamation
        Exit Sub
    End If
     
    On Error GoTo errCancel
   
    Set rngCell2 = Application.InputBox(prompt:="Elija la celda a intercambiar", _
                        Title:="Celda a intercambiar", Type:=8)
    If rngCell2.Count <> 1 Then
        MsgBox "Debe elegirse solo una celda", vbCritical
        Exit Sub
    End If
    If Len(rngCell2) = 0 Then
        MsgBox "La celda elegida esta vacia", vbExclamation
        Exit Sub
    End If

   
    cellTemp = ActiveCell.Value
   
    ActiveCell.Value = rngCell2.Value
    rngCell2 = cellTemp
   
    Exit Sub
   
errCancel:
If Err.Number = 424 Then
MsgBox "Operacion cancelada", vbExclamation
Exit Sub
End If


End Sub

El código va en un módulo común del editor de Vba, y para que podamos usarlo en todo cuaderno abierto lo guardamos en un módulo del cuaderno Personal.xls(m). Par apoder usar la macro con facilidad le asignamos un atajo de teclado

o un ícono en la barra de acceso rápido

Con este código todo lo que tenemos que hacer es seleccionar la primera celda a intercambiar, apretar Ctrl-Mayúsculas-Enter para accionar la macro, elegir la segunda celda y apretar Ok. Si elegimos más de una celda o una celda vacía, aparece un aviso y el código termina; lo mismo si el usuario aprieta el botón Cancelar del al forma

Remediar el problema de las celdas combinadas en Excel

Empiezo esta nota por declarar abiertamente y sin remordimientos: detesto la funcionalidad "Combinar y centrar" de Excel. Y no es una cuestión de gustos personales. Ya hemos expuesto parte, y sólo una pequeña parte, de los problemas que genera el uso de Combinar y Centrar (y también mostré las ventajas de usar "centrar en la selección" en el menú Formato de Celdas – Alineación).

Sin embargo y a pesar de ser una de las peores prácticas en Excel, cada tanto recibo archivos plagados de celdas combinadas. Por lo general se trata de archivos de colegas de trabajo que, después de haber invertido horas en crear la hoja, no entienden por qué se dan comportamientos extraños o imprevistos.

Últimamente tuve que rediseñar un cuaderno, con varias hojas, donde el uso de Combinar y Centrar generaba todo tipo de problemas. Dada la cantidad de correcciones a hacer, decidí programar una macro que haga la tarea: encontrar las celdas combinadas y "descombinarlas" (tal vez debería decir deshacer o descomponer).

El código es el siguiente

Sub unmerge_Cells()
    Dim rngCell As Range
   
    'comprobar numero de celdas en la seleccion
    On Error GoTo errOverFlow
    If Selection.Count = 1 Then
        MsgBox "Por favor, seleccionar un area de la hoja", vbExclamation
        Exit Sub
    End If
    If Selection.Count > 1000000 Then
        MsgBox "Demasiadas celdas en la seleccion", vbCritical
        Exit Sub
    End If
   
    'descombinar celdas combinadas
    For Each rngCell In Selection
        If rngCell.MergeCells = True Then
            rngCell.UnMerge
        End If
    Next rngCell
      
    Exit Sub
   
errOverFlow:
If Err.Number = 6 Then
MsgBox "Se han seleccionado demasiadas celdas", vbCritical
Exit Sub
End If
   
End Sub


El código revisa todas las celdas en el área seleccionada y deshace las combinaciones. Como puede verse, hemos limitado la cantidad de celdas que puede contener el área, para evitar problemas de "overflow".

El mejor lugar para guardar el código es en un módulo del libro Personal.xls(m), de manera de poder usarlo en todos los cuadernos abiertos.

Como decía más arriba, la alternativa a Combinar y Centrar es usar Formato de Celdas-Alineación-Centrar en la Selección. Este código, como el anterior, deshace las combinaciones de celdas y luego aplica el centrado en la selección, manteniendo de esta manera el diseño de la hoja (la excepción es cuando la combinación de celdas incluye más de una fila)

Sub unmerge_and_center()
    Dim rngCell As Range
    Dim rngMergedRange As Range
    Dim strMergeAreaAddress As String
   
    'comprobar numero de celdas en la seleccion
    On Error GoTo errOverFlow
    If Selection.Count = 1 Then
        MsgBox "Por favor, seleccionar un area de la hoja", vbExclamation
        Exit Sub
    End If
    If Selection.Count > 1000000 Then
        MsgBox "Demasiadas celdas en la seleccion", vbCritical
        Exit Sub
    End If
   
    'descombinar celdas combinadas
    For Each rngCell In Selection
        If rngCell.MergeCells = True Then
            Set rngMergedRange = rngCell.MergeArea
            strMergeAreaAddress = rngMergedRange.Address
            Range(strMergeAreaAddress).Select
            rngCell.UnMerge
            Range(strMergeAreaAddress).HorizontalAlignment = xlCenterAcrossSelection
        End If
    Next rngCell
   
    Exit Sub
   
errOverFlow:
If Err.Number = 6 Then
MsgBox "Se han seleccionado demasiadas celdas", vbCritical
Exit Sub
End If

End Sub

El archivo con los códigos se puede descargar aquí.