Como Crear Lista de Validacion que Depende Dato Insertado en Celda #535

CREAR LISTA DE VALIDACIÓN EN CASCADA DEPENDIENDO DE DATO INSERTADO

En este ejemplo se muestra como se puede crear con macro una lista de validación que dependa del dato ingresado en una celda, es decir en base al registro que se ingrese en una celda, se creará un listado de validación cuyos datos dependerán del dato ingresado. 

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

  

Este ejemplo funciona así: al intentar ingresar un dato o registro nuevo en una celda de la columna A, se crea en la columna B un listado de validación que solo permite ingresar los números de los distintos departamentos que existen en una tienda.

Una vez seleccionado el dato del número del departamento, se creará en la columna D, en este ejemplo está destinado a ingresar la marca del producto, pero dicho listado va a depender de lo seleccionado en la columna B, es decir que dependiendo seleccionado o ingresado en la columna A será el dato que se muestre en la columna D.

En el link encuentra muchos vídeos relacionados con el ingreso o creación de listados de validación en Ecel VBA.

Explicación del código para Crear Distintas Listas de Validación Dependiendo del Dato ingresado en Celda

Primero se debe determinar si las celdas modificadas pertenecen a la columna A, sea cualquier celda de dicha columna mayor a 1, así:

If Target.Row >= 1 And Target.Column = 1 Then

Se crea objetos con la hoja1 y hoja3, de la siguiente manera:

Set a = Sheets(“Hoja1”)
Set b = Sheets(“Hoja3”)

Se determina el rango donde está los datos para crear el listado de validación y se carga en la variable “valida”:

uf = b.Range(“B” & Rows.Count).End(xlUp).Row
valida = “=Hoja3!$B$2:$B$” & uf

Luego dicho rango se usa para hacer que la lista de validación se muestre en la celda, insertando la validación con los siguientes códigos:

With a.Cells(Target.Row, “B”).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

Lo anterior es para crear la validación en la columna B, en dicha validación se establecen cuales son los departamentos a los que pertenecen los distintos elementos o productos de la base de datos.

Ahora viene la creación del código, que permite mostrar un listado de validación distinto, el cual variará dependiendo de lo seleccionado o escrit en la columna B.

Solo se crea el listado si el cambio en la celdas de Excel se produce en la columna B fila mayor  a 2, para ello se usa el siguiente código:

If Target.Row >= 1 And Target.Column = 2 Then

Se crea un bucle For… Next que permite recorrer las columnas 3 a 7, el objetivo es encontrar en que columna se encuentra el departamento seleccionado en base a ello tomará el listado correspondiente que están en las columnas 3 a 7 (Columnas C a G), el código es el siguiente:

For x = 3 To 7
If b.Cells(1, x) = a.Cells(Target.Row, “B”) 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

Luego se crea la validación agregando a la celda correspondiente de la Columa D el listado de datos válidos para ingresar en dicha celda, se usan los códigos:

With a.Cells(Target.Row, “D”).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida
.IgnoreBlank = True
.InCellDropdown = True
End With
Exit For
End If
Next x

Código VBA  Inserta en Modulo para crear una validación en celda al insertar un dato

Sub BORRA()
Sheets(“Hoja1”).Range(“B:D,D:D”).Validation.Delete
End Sub

Código VBA  Inserta en Worksheet  para crear listado de validación dependiendo del dato ingresado

Sub BORRA()

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

uf = b.Range(“B” & Rows.Count).End(xlUp).Row
valida = “=Hoja3!$B$2:$B$” & uf
With a.Cells(Target.Row, “B”).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida
.IgnoreBlank = True
.InCellDropdown = True
End With
End If

If Target.Row >= 1 And Target.Column = 2 Then

Application.ScreenUpdating = False
Set a = Sheets(“Hoja1”)
Set b = Sheets(“Hoja3”)
For x = 3 To 7
If b.Cells(1, x) = a.Cells(Target.Row, “B”) 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

With a.Cells(Target.Row, “D”).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=valida
.IgnoreBlank = True
.InCellDropdown = True
End With
Exit For
End If
Next x
End If

Application.ScreenUpdating = True
End Sub

Descarga el Libro Como Crear Validación al Ingresar Datos en Hoja de Excel con Macros VBA

Desde el final del post se puede descargar el libro de Excel usado como ejemplo, 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

no rating based on 0 votes