VALIDACIÓN CON LISTA EN EXCEL


La validación con lista desplegable en la celda (o celdas), es un tema que da mucho de sí.
No voy a explicar aquí la filosofía de funcionamiento de la Validación (consultar la ayuda de Excel), sino la reconstrucción de algunos casos concretos que tengo en mi página web.

Los ejemplos tratados aquí son:

  1. Validación múltiple dependiente. . .(Ejemplo 003)
  2. Validación excluyente . . . . . . . . . .(Ejemplo 004)
  3. Validación con lista creciente. . . . .(Ejemplo 006)



1.- VALIDACIÓN MÚLTIPLE DEPENDIENTE

En esta ocasión me baso en las Islas Canarias (sólo las 7 islas mayores) para construir el presente ejemplo, pero puede ser cualquier bloque de datos similares o no, pero con una relación directa entre ellos.
En la celda C6 podemos elegir una provincia, según la provincia elegida la lista desplegable de la celda C9 mostrará los nombres de unas islas u otras y cuando elijamos una isla en C9, la lista desplegable de la celda C13 mostrará los municipios que pertenecen a dicha isla.
Mediante código VBA (ver los eventos más abajo), logramos que al elegir un nombre, se borren las categorías inferiores, para evitar por ejemplo tener en un momento dado una isla correspondiente a una provincia diferente. También el código evita seleccionar celdas diferentes de C6:C13.


Nota: En este ejemplo trabajo con otros valores, como extensión o altitud, que no comentaré en esta reconstrucción ya que no son necesarios para el correcto funcionamiento de la validación que nos ocupa.

Detalles adicionales se pueden ver en el propio ejemplo descargable.



El libro consta de dos hojas: Principal y Tablas.

Proceso.

En la hoja Tablas tenemos los siguientes rangos con datos: Nota: La estructura de los nombres definidos (4 primeros caracteres del nombre de la provincia o del nombre de la isla (sin espacios)), es para referenciar en las fórmulas, a partir de los nombres completos.

En la hoja Principal tenemos mediante: Datos > Herramientas de datos > Validación de datos… El código VBA es el siguiente:

Private Sub Workbook_Open()
Sheets("Principal").ScrollArea = "$C$6:$C$13"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$6" Then Range("C9").ClearContents
If Target.Address = "$C$9" Then Range("C13").ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.ScrollArea = "$C$6:$C$13" Then
If Target.Offset(, -1) = "" Then Target.Offset(-1).Select
End If
End Sub


Volver arriba

2.- VALIDACIÓN EXCLUYENTE


En este otro ejemplo muestro como en un rango de celdas validadas, la lista desplegable de cualquiera de ellas muestra una lista de nombres reducida, de tal manera que sólo muestra aquellos nombres que no hayan sido elegidos ya en cualquiera de las otras celdas del rango.
Las imágenes de la derecha, muestran la lista desplegable antes y después de elegir algunos nombres.
Observa que en la imagen de la derecha, no aparecen en la lista ninguno de los 5 nombres elegidos en las celdas anteriores.

Proceso.

1. Definir un nombre para la lista origen: 2. El rango con todos los nombres, está en J1:J13.
3. En el rango K1:K13 están en blanco los nombres ya elegidos, mediante la fórmula: =SI(CONTAR.SI($A$2:$A$14;J1)>0;"";J1)
4. En la columna L es donde escribe el código VBA los nombres restantes (sin celdas en blanco).
5. Validación de celdas:
La lista con los nombres no elegidos (miLista en columna L) se consigue con el siguiente código VBA (apoyado en las fórmulas existentes en la columna K):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celda As Range, fila As Integer
If Target.Column = 1 Then
fila = 1
For Each celda In Range("K1:K13")
If celda.Value > "" Then
Cells(fila, 12).Value = celda.Value
fila = fila + 1
End If
Next celda
Do Until fila > Cells(Rows.Count, 10).End(xlUp).Row
Cells(fila, 12).ClearContents
fila = fila + 1
Loop
End If
End Sub


Volver arriba

3.- VALIDACIÓN CON LISTA CRECIENTE

En este ejemplo en caso de escribir en la celda un nombre inexistente en la lista origen, el mensaje emergente propondrá incluirlo en la lista.
Aceptar lo incluye en la lista origen, y Cancelar no lo incluye y lo borra de la celda validada.

Proceso.

Validar las celdas:

El ejemplo se apoya en el evento CHANGE de la hoja, cuyo código VBA se encarga de agregar el nuevo nombre a la lista origen y ordenar la misma a continuación. También se encarga de convertir a mayúsculas (UCASE) el nuevo nombre entrado en la celda (esto no es imprescindible y puede quitarse).

El código VBA es el siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)
' No ejecuta el código; por ejemplo al borrar varias celdas.
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If Application.WorksheetFunction.CountIf(Sheets("Hoja2").Range("MiLista"), Target.Value) = 0 Then ' Si no existe el nombre escrito. Application.EnableEvents = False ' Desactiva los eventos.
Target.Value = UCase(Target.Value) ' Convierte a mayúsculas el nombre escrito.
' Escribe el nuevo nombre en la lista origen.
Sheets("Hoja2").Cells(1000, 10).End(xlUp).Offset(1).Value = UCase(Target.Value)
' Ordena la lista origen.
Sheets("Hoja2").Range("MiLista").Sort Key1:=Sheets("Hoja2").Range("J2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Application.EnableEvents = True ' Activa los eventos.
End If
End If
End Sub


Volver arriba