martes, diciembre 22, 2009

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

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


53 comentarios:

  1. Buenos días Jorge,

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

    Mil gracias por toda tu ayuda.

    ResponderBorrar
  2. 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.

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

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. 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.

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

    ResponderBorrar
  7. 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!

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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.

    ResponderBorrar
  10. 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.

    ResponderBorrar
  11. 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

    ResponderBorrar
  12. 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.

    ResponderBorrar
  13. 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.

    ResponderBorrar
  14. 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.

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

    ResponderBorrar
  16. 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".

    ResponderBorrar
  17. 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...

    ResponderBorrar
  18. 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).

    ResponderBorrar
  19. 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.

    ResponderBorrar
  20. 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.

    ResponderBorrar
  21. Tito

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

    Set rngValid = Range("rngValidado")

    ResponderBorrar
  22. 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.

    ResponderBorrar
  23. 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.

    ResponderBorrar
  24. 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.

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

      Borrar
  25. 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,

    ResponderBorrar
  26. 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.

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

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

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

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

    ResponderBorrar
  31. 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

    ResponderBorrar
  32. 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.

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

    ResponderBorrar
  34. Buenas, es la primera vez que veo algo de las macros, así que puede ser que haya hecho algo mal...
    He hecho clic derecho en la pestaña de la hoja, ver código y he pegado el codigo que has expuesto. Ahora cada vez que intento pegar algo, en vez de salirme el mensaje de error "normal" me salta un error de Microsoft Visual Basic (Se ha producido el error '1004' en tiempo de ejecucion: Error en el metodo 'Range' de objeto '_Worksheet') con las opciones de Finalizar, Depurar y Ayuda.
    Alguna pista sobre lo que puedo hacer, o si he pasado algo por alto? Gracias!

    ResponderBorrar
  35. Hola, por lo visto hay un problema en la definición del rango con al validación.

    Te sugiero que descargues el archivo con el ejemplo y lo uses para ubicar donde pueda estar el problema en tu código.

    ResponderBorrar
  36. Estoy probando cosas, y el error salta en esta linea
    -> Set rngValid = Range("rngValidado")
    Cada vez que intento cambiar el rango de alfanum y rngValidado (el =Hoja1!$F$3:$F$6) escribiendo las celdas de mi excel también me salta el mismo error y se queda como #¡VALOR! (estoy utilizando palabras para la validación, siendo alfanumérico esto no debería dar problemas no?)
    Gracias!

    ResponderBorrar
  37. Frikilangelo, no logro entender lo que estás haciendo, pero de lo que escribes puedo acotar:

    # - para definir un rango explícitamente en Vba tienes que usar rngValid=Sheets("Hoja1").Range("$F$3:$F$6)

    # - si en la hoja recibes el resultado #VALOR, quiere decir que estás realizando una operación con un rango que incluye referencias a celdas que no contienen valores numéricos (por ejemplo A1 +A2 cuando A2 contiene texto)

    ResponderBorrar
  38. Buenas!
    Como ya dije, e sla primera vez que veo esto. Examinando el ejemplo vi que habia que crear los nombres para el rango y los valores en Formulas>Administrador de nombres. Ahora me va perfecto, gracias!

    ResponderBorrar
  39. Hola Jorge, Viendo la macro encontré que la misma valida sólo el primer valor pegado y no todos si se pegan varios. Ej: en el file de ejemplo si se escribe a1 en la celda D4 y luego se copian las celdas D4 y D5 y se pegan en la celda B5, B6, la macro solo valida el primer valor que es correcto (valor a1), y no tira ningún mensaje de error por el valor pegado en la celda B6 (valor aaabb). Hay alguna forma de solucionar esto? Muchas gracias! Saludos

    ResponderBorrar
  40. La macro contempla la posibilidad de que se pegue un úniico valor.Por supuesto hay formas de soolucionarlo; espero poder encontrar un poco de tiempo para publicar una solución.

    ResponderBorrar
  41. hola, existe la posibilidad que esto aplique a más de una celda? te explico, necesito que me deje ingresar datos solo si es que de 5 celdas hacia el costado una de ellas no sea igual a la validacion anterior, ejemplo, si las 5 celdas son iguales a 5 celdas validadas anteriormente no puede ingresar los datos, consegui validar concatenando y luego haciendo un contar.si.conjunto, con el contar si conjunto me arroja el numero 2 (datos ingresados 2 veces) me da el error que no puedo ingresar, independiente el orden en el que ingrese los datos a la celda, el punto es que trate de ocupar tu codigo y queda pegado luego excel se cierra automaticamente, tal vez es porque no puede hacerlo con mas de una celda a la vez o yo estoy haciendo algo mal, favor tu ayuda, muchas gracias.

    ResponderBorrar
  42. Hola, no veo como el código de la nota puede ayudarte. Se trata de casos distintos, si es que entiendo correctamente tu planteo.
    Te sugiero que me envíes el archivo con el ejemplo, siguiendo estas instrucciones.

    ResponderBorrar
  43. Buenas tardes Jorge... ante todo muchísimas gracias por esta colaboracion. Te cuento que he podido adaptarla a mi archivos muy facilmente. Simplemente hay algo que me llamo la atencion, ya que la macro evita que se peguen algunos valores (por ejemplo copiar y pegar texto de una web o un mail), pero sin embargo permite pegar texto proveniente de otro excel (copiando pegando una celda), lo cual anula la validacion. Se te ocurre algun comando para evitarlo?

    Desde ya muchas gracias

    ResponderBorrar
  44. Hola Pablo, he trata de recrear la situación, copiando celdas de otro libro, pero veo que la macro funciona. Parece ser que se trata de otro tipo de problema. ¿Podrías describir más detalladamente lo que hacés o mandarme el archivo donde pusiste la macro?

    ResponderBorrar
  45. Buenas Jorge,

    Al final has hecho alguna publicación donde se solucione el problema para varias columnas con diferentes validaciones de datos?

    Gracias y enhorabuena por las aportaciones me han sido de una gran utilidad.

    ResponderBorrar
  46. Todavía está anotado en la columna del "debe". Veremos más adelante, cuando termine de reponerme de las vacaciones :)

    ResponderBorrar
  47. Hola Jorge, ante todo agradecerte este blog y toda la ayuda que prestas.
    Estoy intentando resolver el tema de evitar borrar la validación de celdas y he descargado tu fichero. Te comento que en mi Mac funciona bien excepto por que sí que permite el copiar y pegar y así se pierde la validación. Me imagino que será un tema de versiones: tengo mac OS X Capitan 10.11.2 y Excel 2011 para mac version 14.1.0.
    Se te ocurre que pueda hacer algo?

    Gracias adelantadas

    ResponderBorrar
  48. Hola David,
    no tengo experiencia con la versión para Mac. Te sugiero que consultes en alguno de los foros de Excel para Mac.

    ResponderBorrar
  49. Hola Jorge gracias por tu aporte
    Creo que a lo que se refiere Pablo es que si tu copias información con mas de una celda al pegarlos como valores esto salta la marco y permite pegar texto que no este dentro de la lista desplegable sin embargo si copias información de una celda y la pegas en una celda la macro funciona bien. ahí esta el bug

    ResponderBorrar
  50. Exacto Gabriel Maldonado, ese es problema. Al pegar texto se anula la validacion de la lista desplegable. Por lo que he leido es un problema de excel en general. Saludos!

    ResponderBorrar
  51. Buenos dias Jorge, muy bueno tu aporte, te hago una consulta, como adaptarías el codigo si la hoja donde tienes los datos está en formato tabla? al estar en formato tabla no puedes definir todo un rango en una columna ya que la tabla se va expandiendo o creciendo sola a medida que vas incluyendo datos

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.