Evitar borrar Validación de datos en Excel al pegar datos copiados

martes, diciembre 22, 2009

Empiezo con una aclaración: por primera vez he eliminado una entrada luego de haberla publicado. Se trata de la entrada donde mostraba un código para evitar el problema de la destrucción de la validación de datos al pegar un valor copiado de otra celda. Había demasiadas circunstancias que provocaban que el código no funcionara. Mea culpa!

Volviendo sobre el tema, una de las debilidades de Validación de Datos es que si un usuario pega un valor en una celda del rango validado en lugar de ingresarlo manualmente, las definiciones de la validación quedan eliminadas.

La única forma de evitar estas situaciones es usar macros, más precisamente eventos.



Buscando en la Internet encontré, entre otras, una solución propuesta por John Walkenbach. El problema con esta solución es que si el usuario en lugar de pegar el valor usa la opción Pegado Especial-Valores (o Fórmulas), el evento no responde y el valor es aceptado.

El código que propongo usa una parte del código de Walkenbach, para el caso que el usuario use la opción de pegado común, y parte de mi código para el caso que use pegar-valores, pegar-fórmulas, Insertar o arrastre el valor a la celda.

En el módulo de Vba de la hoja pegamos estos códigos

Private Sub Worksheet_Change(ByVal Target As Range)
'   rutina desarrollada por Jorge Dunkelman - JLD Excel Blog
'   parte del codigo tomado de la nota de John Walkenbach http://www.j-walk.com/ss/excel/tips/tip98.htm

    Dim rngValid As Range, cell As Range
    Dim Msg As String
    Dim codeValid As Variant

    Set rngValid = Range("rngValidado")

    On Error Resume Next

    If Not HasValidation(rngValid) Then

         Application.Undo
         MsgBox "Valor no válido", vbCritical
         Application.EnableEvents = False
         Target.ClearContents
         Application.EnableEvents = True
     End If

    For Each cell In Target
        If Union(cell, rngValid).Address = rngValid.Address Then
            codeValid = ActiveCell.Validation.Value
            If codeValid = True Then
                Exit Sub
            Else
                MsgBox "Valor no válido", vbCritical
                Application.EnableEvents = False
                cell.ClearContents
                cell.Activate
                Application.EnableEvents = True
            End If
        End If
    
    Next cell

    

End Sub

Private Function HasValidation(r) As Boolean
    Dim x

    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function


Nótese que estamos usando un evento Worksheet_Change y una función UDF.

Al rango B3:B12 de la hoja hemos aplicado validación de datos con la opción Lista. Los valores permitidos son a1, a2, a3 y a4.




Este video muestra cómo funciona la macro




El archivo con el ejemplo y el código puede descargarse aquí.


35 comments:

Jorge Alberto,  23 diciembre, 2009 17:19  

Buenos días Jorge,

Esta macro aplica tanto a valores númericos como alfanúmericos?

Mil gracias por toda tu ayuda.

Jorge L. Dunkelman 23 diciembre, 2009 17:44  

Jorge Alberto,
si. La macro de hecho comprueba primero si se ha quitado la validación de la celda que se está evaluando (cosa que sucede si se aplica copiar/pegar). Si se ha quitado la validación, la macro corre la orden UNDO y cancela la acción del usuario. Como ves, este no está ligado al tipo de valor o regla que hayamos puesto en la validación.
La segunda parte de la rutina evalúa si el valor en la celda coincide con alguno de los valores permitidos. Esto es necesario en caso que el usuario use Pegar Especial-Valores o Fórmulas. Tampoco en este caso importa si los valores de la validación son numéricos o alfa-numéricos.

Antonio8688,  23 diciembre, 2009 19:06  

Felicitaciones Julio por tu sapiencia. También por la nueva forma de descargar tus ejemplos, brinda mayor facilidad para las descargas. Feliz Navidad 2009.

Javier,  23 diciembre, 2009 20:12  

Hola, Jorge.
La solución funciona bastante bien, aunque se echa en falta algo más genérico que no hubiera que programar para cada rango en que queramos tener una validación, porque si tenemos varios se complica mucho la cosa.
Haciéndole perrerías he encontrado un comportamiento anómalo, que te lo cuento sólo a modo de curiosidad, porque reconozco que es un tanto rebuscado: si se intenta cambiar algo de la validación de alguna de las celdas del rango parece que si el cambio no afecta a todas por igual se vuelve un poco loca la macro y ya no acepta ningún valor y se queda en bucle señalando el error.

Y por último, me gustaría saber con qué programa haces tus videos de demo. hace tiempo contestaste en otra nota que hacías unas demo en flash con Wink, pero estos videso quedan bastante mejor y se ven directamente, es mucho mejor.

Saludos y gracias por tu tiempo.

Jorge L. Dunkelman 23 diciembre, 2009 21:11  

Gracias, pero...¿quien es Julio?

Anónimo,  23 diciembre, 2009 21:41  

gracias maestro, es usted un genio en esto.
y sobre todo lo mas valioso que le he conocido.
la capacidad y humildad para compartir sus conocimientos.

Que tenga usted feliz navidad y un prospero año nuevo.

Jorge L. Dunkelman 23 diciembre, 2009 22:05  

Gracias, pero lo de genio me queda un poco grande.
Que tengas un excelente año!

Jorge L. Dunkelman 25 diciembre, 2009 14:40  

Javier,
tenés razón, habría que programar algomás general. En cuanto al "bug" que encontraste me olvidé de aclarar que la macro funciona bien sólo si todas las celdas del rango contienen validación de datos. Tasl vez más adelante publique algo más general.
Feliz año nuevo!

Jorge L. Dunkelman 25 diciembre, 2009 14:43  

Ah, me olvidaba. Las capturas de pantalla en video las hago con el Windows Media Encoder de Microsoft (se puede descargar gratuitamente).
La ventaja del Wink es que podes editar y agregar comentarios.

fcocam 29 diciembre, 2009 19:14  

Muchisimas gracias por su humildad y generosidad en compartir sus valiosisimos conocimientos don Jorge. Exitos y Bendiciones para este 2010. Que la pase bien. Saludos desde Guatemala.

Javier C,  22 enero, 2010 04:26  

Hola Jorge. Me han gustado mucho tus notas, gracias por tu esfuerzo, aunque no he tenido tiempo para probarlas todas. Tengo una consulta, me gustaría saber como valido datos según un orden asignado. Por ejemplo: Si en la celda A1 tengo "Documento A" y la celda B1 "Revisión 1", y en la celda A2 tengo el mismo texto "Documento A" entonces la celda B2 solo me deberia permitir digitar "Revisión 2", no me deberia dejar digitar "Revisión 3" o "Revisión 0" porque no es el consecutivo correspondiente. Te enviare un archivo adjunto a tu correo para que sea mas clara la pregunta.

Muchas gracias de antemano y sigue con el buen trabajo.

Jorge L. Dunkelman 22 enero, 2010 07:21  

Se puede hacer, aunque la explicación es un poco larga para ponerla en un comentario.
No me queda claro por qué se necesita validación de datos. Si en las celdas de la columna B tiene que aparecer siempre el valor correspondiente al de la columna A, podrías usar una fóemula sencilla como ="Revisión "&A1

Anónimo,  09 marzo, 2010 11:29  

Buenos días, me ha venido genial tu código pero me surge un problema, tengo que validar los datos de toda la hoja y aquí solo me valida de la B3:B12, como podría cambiar esto? Gracias y un saludo.

Jorge L. Dunkelman 09 marzo, 2010 15:43  

Esimado,
supongo que habrás descargado el archivo del ejemplo. En este archivo el rango que contiene la validación de datos está incluido en un nombre, rngValidado. Tienes que adaptar el rango a tus necesidades.

Robert Blanco 19 marzo, 2010 21:00  

Jorge, estoy probando este código en una planilla, pero como hago si necesito usar el código para otros rangos de celdas no adyacentes.

Jorge L. Dunkelman 20 marzo, 2010 09:24  

Habría que definir todos los rangos que contengan validación de datos. No veo otra manera-

Robert Blanco 22 marzo, 2010 01:03  

Al respecto le comento Sr. Jorge que a 3 celdas no contiguas les nombre "rngValidado", pero la macro entra un bucle sin fin y me muestra la ventana de error "Valor no valido".

Angel Hernandez 30 enero, 2013 05:46  

el codigo se ve chevere... soy nuevo en las macros pero me encantan un monton... ¿donde pego ese codigo? tengo algo en el siguiente libro: la "hoja3" celda "C10" y la "hoja4" celda "C10" tienen listas desplegables, ¿como hago para que el codigo funcione en esas celdas? gracias por su ayuda...

Jorge L. Dunkelman 30 enero, 2013 17:27  

Angel,
como pongo en la nota, en el módulo de la hoja. Te sugiero que descargues el ejemplo y lo investigues. Para ver los códigos debes abrir el editor de Vb (Alt+F11).

Tito Ramírez 11 septiembre, 2013 08:59  

Hombre! Jorge muy bueno tu archivo, me servirá mucho para la entrada de datos que tienen que hacer los ejecutivos del trabajo en el archivo de excel que diseñé, en la que siempre le dan copiar de internet y pegar en las celdas y dañan el formato y pasan por alto la validación creada y yo que tengo que copiar de los archivos de todos para unificarlos hago trabajo demás corrigiendo lo que hace cada uno que son como 26 ejecutivos del call center. Tengo que hacer las adaptaciones.


Agradecido. Un abrazo.

Tito Ramírez 24 septiembre, 2013 05:34  

no se que paso con mi comentario desde el movil. Es que probé de todo con el código pero no he podido validar con mas de una columna en la que cada una tenga diferente validación, como se podrá hacer. gracias.

Jorge Dunkelman 27 septiembre, 2013 17:29  

Tito

tendría que ver tu código. Fijate en la definición del rango a considerar:

Set rngValid = Range("rngValidado")

Tito Ramírez 28 septiembre, 2013 07:32  

Claro sobre todo esa línea que se refiere al rango o celdas a aplicar la acción al haber un cambio. La cual he puesto


Set rngValid = Range("a2:b10")


Que es lo mismo que asignar nombres a una serie de celdas. La cual también he probado y es lo mismo funciona siempre y cuando la validación sea la misma, es decir, si se aplica la validación de que el largo de texto ingresado sea de 4 caracteres para todas las columnas, pero en el caso que sean diferentes la validaciones , es decir, la columna A que permita solo 4 caracteres y la columna B incluida en el rango permita solo 5 caracteres, alli es donde el código no funciona.

Tito Ramírez 28 septiembre, 2013 07:35  

alguna ayududilla al respecto. Puede probarlo como le mencioné, de crear solo 2 columnas con 5 filas y seleccionar como rango en el código, es decir, de b2:c6 con fila de encabezado y validen todo con una identica validación, y funciona de maravilla, pero luego solo a la columna B o a la A, cualquiera... cambien la validación o regla para poder ingresar datos y queda en bucle cerrando Excel.

Jorge Dunkelman 29 septiembre, 2013 18:59  

Tito,

he hecho algunas pruebas y veo que realmente existe un problema cuando la validación se extiende a más de una columna. Espero poder publicar una ampliación del método en los próximos días.

Tito Ramírez 12 octubre, 2013 19:11  

Si son varias columnas no hay problema siempre y cuando tenga la misma validación de datos. Gracias Jorge.

Anónimo,  03 marzo, 2014 20:17  

Hola, tengo un problema con validación de datos. Tengo un archivo en el cual tengo que concatenar un par de celdas para que me den un formato especifico, es decir, necesito tener la nomenclatura correcta de algunos documentos los cuales ya están en una base de datos. Por ejemplo: en hoja 2 tengo la base de datos, del rango A1 al A100, ahora bien, en mi hoja 1 tengo mi celda validación de datos la cual voy a vincular con mi hoja 2 para poder seleccionar los datos y en la hoja 1 tengo el apartado que voy a concatenar. hoja 1 celda A2 tengo validación de datos a hoja 2, en la hoja 1 también tengo en la celda A5: sitio 1 Celda: B5 documento 1, en la celda B7 es donde voy a concatenar A5&B5, hasta ahí todo esta bien, pero el problema es en la celda A2 de la hoja 1 ya que yo puedo sin problema seleccionar el numero de sitios que están seleccionados en la hoja 2, pero no quiero que se puedan pegar datos en la hoja 1 A2, ya que me descompone el formato. que puedo hacer? Gracias por tu atención y quedo al pendiente,

Jorge Dunkelman 04 marzo, 2014 07:41  

Con una mano en el corazón, ¿realmente crees que puedo entender el planteo sin ver el archivo? Por favor, fijate en el elnace Ayuda, en la parte superior de la plantilla, cuáles son las condiciones para las consultas como la tuya.
Un detalle más: los comentarios tienen que estar relacionados con el tema de la nota; para consultas te sugiero recurrir a alguno de los muchos foros que hay o proceder como describo en el enlace Ayuda.

puntopenal 13 abril, 2014 19:51  

Hola, ¿es posible que solo devuelva una advertencia en ves de borrar el dato validado? Muchas gracias

Jorge Dunkelman 14 abril, 2014 07:37  

Si. Hay que modificar el código quitando esta parte
Application.EnableEvents = False
cell.ClearContents
cell.Activate
Application.EnableEvents = True

Tito Ramírez 15 abril, 2014 03:50  

Se puede aplicar en la misma hoja otra validación en otra columna? probé y no deja.

Jorge Dunkelman 15 abril, 2014 09:29  

Así es, el código está limitado a un tipo de validación. ¿Buscaste en alguno de los foros de Excel/Vba?

JOSE LOPEZ 24 abril, 2014 18:33  

Hola buenas a todos, después de buscar por todos lados ustedes han colocado la macro que necesito para un formato que estoy utilizando. Pero como no soy experto en el tema me gustaría saber como utilizo esta macro para un mismo archivo para que me valide diferentes campos con sus diferentes opciones; es decir en una columna debe responder solo SI o NO, en otra celda Tipo de Documento, Sexo, etc... La verdad les agradecería mucho me ayuden aclarar mi duda

Jorge Dunkelman 24 abril, 2014 21:58  

Hola José, cómo se usa el código está explicado en la nota. En cuanto a las validaciones, como puedes ver en los últimos comentarioss, el código se puede usar sólo con un tipo de validación por hoja.

JOSE LOPEZ 25 abril, 2014 18:30  

Gracias Jorge te agradezco la aclaración a mi duda feliz día amigo.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP