Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como CREAR VALIDACION de Celdas LISTADO de Validacion CREADO en TIEMPO REAL – Excel VBA #536

Validacion datos en tiempo real

VALIDAR DATOS SIN LISTA DE VALIDACIÓN

En este ejemplo se muestra como validar con macro de Excel datos en una celda sin tener una lista de validación previa, la lista de validación se crea en tiempo real al insertar un dato.

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

  
 

https://www.youtube.com/watch?v=9hoibKP15Zg&list=PLdK9H5dMIfQgEv075oRnP3SKbF1zZaYHq

Mira la macro en acción, una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestro canal de You Tube, mira el playlist con  vídeos relacionados donde podrás ver la macros relacionadas en acción con una explicación en forma visual que ayudará a entender el ejemplo en forma más fácil.

VALIDAR DATOS EN EXCEL CON LISTA VALIDACIÓN CREADA CON MACRO EN TIEMPO REAL

Primero se debe descargar el libro, una vez abierto el libro de Excel, se debe insertar un dato en cualquier celda de la columna A, excepto la fila 1 que está reservada para la cabecera de las columnas.

Cuando se inserta el dato, en forma automática la macro crea una lista de validación en la columna D y F.

Como se podrá observar no existe ninguna lista de validación en las restantes hojas, normalmente cuando se validan datos se cuenta con un lista de datos válidos ya sea en la misma hoja o en otra hoja, pero en este caso no hay ninguna lista de validación, la lista de validación de Excel se crea en tiempo real, es decir una vez ingresado el dato en cualquier celda de la columna A, se crea la lista de validación.

En la columna D la lista de validación la confecciona con los datos ingresados previamente en dicha columna, los datos de la columna F son obtenidos de un bucle  que inserta un array en la lista de validación de las celdas de la columna F.

Explicación del código para crear listados para validar en tiempo real con macro de Excel VBA

La macro solo se ejecuta si la celda modificada pertenece a cualquier fila de la columna A, para ellos se usa el siguiente código:

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

Se declaran las variables a usar, así:

Dim Mycolec As New Collection, celda As Object, MyArray() As Variant
Dim Mycolec1 As New Collection, MyArray1() As Variant

Se hace un objeto con la Hoja1 y se determina el rango de datos ingresados en la columna D, con el siguiente código:

Set a = Sheets(«Hoja1»)
uf = a.Range(«D» & Rows.Count).End(xlUp).Row
r1 = «D2» & «:D» & uf

Luego se recorre cada unas de las celdas del rango agregándolas a una colección con la particularidad que solo se agregan los datos únicos no repetidos, se usa el código:

For Each celda In Range(r1)
Mycolec.Add celda.Value, CStr(celda.Value)
Next celda

Luego se redimenciona la array o matriz creado el cual va de 1 hasta la cantidad de datos que hay en la colección de datos obtenidas con el código anterior, así:

ReDim MyArray(1 To Mycolec.Count)

Posteriormente se hace un bucle for… next que va desde 1 hasta el último valor de la colección agregando en el array o matriz cada dato de la colección, así:

For i = 1 To Mycolec.Count
MyArray(i) = Mycolec(i)
Next i

Luego se agrega el listado creado en la celda para establecer cuales son los datos válidos que se pueden agregar en la celda, se usa el siguiente código:

With a.Cells(Target.Row, «D»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=Join(MyArray, «,»)
.IgnoreBlank = True
.InCellDropdown = True
End With

Posteriormente se cargan en otra colección los datos de un bucle que va de 1 a 5, es decir la colección de datos tendrá los números del 1 al cinco, el código que se usa es:

For x = 1 To 5
Mycolec1.Add x
Next x

Se redimenciona la array o matriz y se pasan los datos de la colección de datos al array con el siguiente código:

ReDim MyArray1(1 To Mycolec1.Count)
For i = 1 To Mycolec1.Count
MyArray1(i) = Mycolec1(i)
Next i

Por último se agrega la validación con macros de Excel a la celda de la columna F, dicha validación solo permite ingresar números del 1  al 5, se usa el siguiente código:

With a.Cells(Target.Row, «F»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=Join(MyArray1, «,»)
.IgnoreBlank = True
.InCellDropdown = True
End With

Código VBA para validar datos ingresados en celdas de Excel en Tiempo Real

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row > 1 And Target.Column = 1 Then
Dim Mycolec As New Collection, celda As Object, MyArray() As Variant
Dim Mycolec1 As New Collection, MyArray1() As Variant

Set a = Sheets(«Hoja1»)
uf = a.Range(«D» & Rows.Count).End(xlUp).Row
r1 = «D2» & «:D» & uf
For Each celda In Range(r1)
Mycolec.Add celda.Value, CStr(celda.Value)
Next celda

ReDim MyArray(1 To Mycolec.Count)
For i = 1 To Mycolec.Count
MyArray(i) = Mycolec(i)
Next i

With a.Cells(Target.Row, «D»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=Join(MyArray, «,»)
.IgnoreBlank = True
.InCellDropdown = True
End With

For x = 1 To 5
Mycolec1.Add x
Next x

ReDim MyArray1(1 To Mycolec1.Count)
For i = 1 To Mycolec1.Count
MyArray1(i) = Mycolec1(i)
Next i

With a.Cells(Target.Row, «F»).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, Formula1:=Join(MyArray1, «,»)
.IgnoreBlank = True
.InCellDropdown = True
End With

End If
End Sub



Descarga el Libro Como Crear Validación de Datos en Tiempo Real con Excel VBA

Puedes descargar desde el final el archivo usado como ejemplo, es gratis y su uso libre, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

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

Summary
Author Rating
1star1star1star1star1star
Aggregate Rating
no rating based on 0 votes