Como Crear LISTA de VALIDACION DEPENDE Valor CELDA con MACRO Excel VBA #533

Crear Validación que depende el valor ingresado en otra celda

En esta presentación se mostrará como crear una lista de validación con macro dependiente del dato ingresado en otra celda, es decir la macro creara una lista de datos válidos que se puede ingresar en una celda, pero dicha lista dependerá de los datos ingresados en otra celda, es decir los datos de validación variaran dependiendo del valor o dato ingresado en una celda adyacente. 

Necesitas aprender a manejar Excel, click acá, si quieres aprender sobre Excel en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet, te lo recomiendo no te arrepentirás.

  

Una vez descargado el ejemplo con el cual se trabaja, que lo puedes hacer desde el final del post, al abrir el libro de Excel, se puedo observar en la celda J1 existe una celda de color naranja que cuya lista de validación en la celda de Excel, contiene tres datos: fecha, descripción y marca.

Depende del valor seleccionado en la celda J1, se creará en la celda J2 un listado de validación, es decir si se selecciona fecha, se creará un listado de validación permitiendo en la celda el ingresos de datos que coincidan con el listado, si se elige descripción creará otra lista de validación distinta y si se elige marca creará otra lista de validación que no será igual a las dos anteriores.

Los datos de donde obtiene la macro los datos para crear la lista de validación, provienen de la Hoja3 columnas B, C y D.

Explicación del Código para validación dependiendo de datos ingresados en otra celda

La macro de Excel que permite crear una validación de datos en cascada en una celda de Excel, en primer lugar crea un objeto con las hojas 1 y 3 que es donde están las celdas con validación y donde están los registros para crear las listas de validación, respectivamente, se usa el código:

Set a = Sheets(«Hoja1»)
Set b = Sheets(«Hoja3»)

Luego se realiza un bucle entre la columna 2 y la 4, buscando la columna que coincida con el dato ingresado o seleccionado en J1, una vez encontrado el titulo de la columna en la hoja3 que coincida con lo ingresado en la celda J1 de la hoja1, procede a crear un rango con los datos de dicha columna.

Es decir si seleccionó la palabra «marca», con intención de filtrar por marcas  en la celda J1 de la hoja1, la macro buscará en la hoja 3 en las columnas 2 a 4 dicha palabra «marca» una vez encontrada la palabra, que es la cabecera o titulo de la columna en la hoja3, creará un rango con los datos de dicha columna, seleccionando cuales son los registros que se deben mostrar en el listado de validación de la celda J2 de la hoja1.

El bucle para realizar lo mencionado es el siguiente:

Hace un bucle entre la columna 2 y 4

For x = 2 To 4

‘Determina en cada recorrido del bucle si el titulo de la columna que es esta en al fila 1 de cada columna es igual a lo ingresado en J1, que es el dato por el que se requiere hacer un filtro.

If b.Cells(1, x) = a.Range(«J1») Then

Se determina cual es la dirección donde el titulo de la columna coincida con lo ingresado en J1 de la hoja 1, a su vez determina la letra de la columna para luego crear rango donde están los datos que serán validos ingresar en la celda J2 de la hoja1, se usan los siguientes códigos:

direc = b.Cells(1, x).Address
wc = Mid(direc, InStr(direc, «$») + 1, InStr(2, direc, «$») – 2)
uf = b.Range(wc & Rows.Count).End(xlUp).Row
valida = «=Hoja3!$» & wc & «$2:$» & wc & «$» & uf

Luego se crea en la hoja1 columna J2 el listado de validación que dependerá del valor o dato seleccionado en la columna J1, la codificación para crear una validación con macro en dicha celda es la siguiente:

With a.Range(«J2»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Luego se seleccionar el dato por el que se requiere filtrar, aplica un filtro automático mostrando los datos coincidentes con lo seleccionado en la celda J2, el código es el siguiente:

If Target.Row = 2 And Target.Column = 10 Then Call Filtrar

En este post no se explica como se crea el código para que aplique un filtro avanzado y muestre los datos que se requieren, si necesita saber como se realiza el filtro avanzado vea el post como realizar un filtro avanzado con macros de Excel.

Código para Crear Validación de Datos que dependan del dato ingresado

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 1 And Target.Column = 10 Then
Application.ScreenUpdating = False
Set a = Sheets(«Hoja1»)
Set b = Sheets(«Hoja3»)

For x = 2 To 4
If b.Cells(1, x) = a.Range(«J1») Then
direc = b.Cells(1, x).Address
wc = Mid(direc, InStr(direc, «$») + 1, InStr(2, direc, «$») – 2)
uf = b.Range(wc & Rows.Count).End(xlUp).Row
valida = «=Hoja3!$» & wc & «$2:$» & wc & «$» & uf
Exit For
End If
Next x

With a.Range(«J2»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

If Target.Row = 2 And Target.Column = 10 Then Call Filtrar
End Sub

Descarga el Libro de Excel Como Crear Validación de Datos Dependientes

La descarga del archivo de Excel usado como ejemplo puedes hacerlos desde el final del post, el mismo es totalmente gratuito y libre su uso, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407

Summary

Author Rating

Aggregate Rating

5 based on 1 votes