Validación de datos en múltiples hojas de un cuaderno Excel

sábado, febrero 18, 2012

Allá por el 2007 publiqué dos notas sobre cómo aplicar validación de datos entre valores que se encuentran en varias hojas de un mismo cuaderno. La idea era impedir la entrada de valores duplicados en el cuaderno, sin importar en que hoja.

Después de una búsqueda en la Internet, encontré un pequeño comentario de Bob Umlas (Excel MVP):

Data validation across sheets isn't supported. You may need an event macro with code to do the checking for you as the worksheet changes (no se puede aplicar validación de datos a través de varias hojas. Habría que usar una macro de tipo evento para hacer la comprobación a medida que se van introduciendo cambios en la hoja).

En las notas mostré dos métodos con fórmulas en contradicción con el comentario de Umlas. El problema con los métodos que expuse en mis notas es que no funcionan. Así que como gesto de contrición y arrepentimiento (que viene a ser lo mismo), aquí va mi nota sobre como validar datos a través de las hojas de un cuaderno utilizando Vba (macros).

Siguiendo con el ejemplo que expuse en esas notas, tenemos un cuaderno con tres hojas; en cada hoja hay una lista de nombres. La idea es que no podamos ingresar en cualquiera de las listas un nombre que ya existe en cualquiera de las otras listas.



Para cada lista hemos creado un nombre que se refiere al rango en forma dinámica (con la función DESREF)



=DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A)-1;1)

=DESREF(Hoja2!$A$2;0;0;CONTARA(Hoja2!$A:$A)-1;1)

=DESREF(Hoja3!$A$2;0;0;CONTARA(Hoja3!$A:$A)-1;1)

Lo que hacemos ahora es crear un evento de manera que cuando se produzca un cambio en alguna de las hojas, el evento dispare una macro que controle si el nuevo valor introducido en alguna de las tres listas ya existe.

El código de la macro que hace esta tarea es:

Sub valid_accross_sheets(valValue)
   
    Dim iValCalc As Integer
   
    iValCalc = WorksheetFunction.CountIf(Range("Lista1"), valValue) + _
                WorksheetFunction.CountIf(Range("Lista2"), valValue) + _
                 WorksheetFunction.CountIf(Range("Lista3"), valValue)
   
    If iValCalc > 1 Then
        ActiveCell.ClearContents
        MsgBox "El valor " & ActiveCell.Value & " ya existe"
    End If
 
End Sub


Esta macro usa la función CONTAR.SI de Excel para comprobar cuantas veces aparece el valor de la celda activa (en la que hemos introducido el valor). Si el valor aparece más de una vez, el contenido de la celda activa es borrado y aparece un mensaje diciendo que el valor ya existe.

El valor de la celda activa es pasado a la macro por evento Workbook_SheetChange del objeto ThisWorkbook. Usamos este evento para evitar tener que programar un evento para cada hoja



El código es muy sencillo: hace una llamada a la macro valid_accross_sheets(valValue) pasando el valor de Target (la celda en la que hemos ingresado el nuevo valor).

Un detalle a tener en cuenta es que los rangos de los nombres deben ser continuos. Si dejamos una fila en blanco, el rango dinámico no incluirá el nuevo valor y la validación fallará.

El cuaderno con el ejemplo puede descargarse aquí.

5 comments:

José Manuel Agundis 20 febrero, 2012 14:26  

La humildad y honestidad hacen a la gente aún más valiosa, bueno por usted Jorge.
Gracias por sus aportes.

Juan Munevar 20 febrero, 2012 16:50  

Jorge, solo una corrección a la macro valid_accross_sheets(valValue), incluir después de If iValCalc > 1 Then:
ActiveCell.Offset(-1, 0).Select
MsgBox "El valor " & ActiveCell.Value & " ya existe"
ActiveCell.ClearContents
End If
End Sub

Jorge L. Dunkelman 20 febrero, 2012 17:05  

Hola Juan,

tu observación es correcta si el cuaderno tiene definido mover la selección hacia abajo al apretar Entrar.
Dado que no podemos saber que definición hay en cuaderno (en mis cuadernos siempre quito la opción de mover después de apretar Entrar), tendremos que adaptar el código de acuerdo a las definiciones del usuario (puede ser que haya elegido mover la selección hacia arriba, o hacia la derecha o hacia la izquierda).
Creo que agregaré algo en la nota.
Gracias por la observación.

Juan Munevar 20 febrero, 2012 18:35  

Oye si, olvidaba que también se puede configurar excel para que se desplace hacia otro lado, o usar la combinación de teclas para forzar su desplazamiento en otra dirección diferente a hacia abajo. Ok tienes razón, entonces se debe tener en cuenta según como lo vayamos a utilizar en nuestros documentos.

Jorge L. Dunkelman 21 febrero, 2012 01:32  

Mañana estaré publicando una nota con el código ampliado para contemplar todas las posibilidades.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP