Control de cambios personalizado en Excel

lunes, julio 19, 2010

Hay situaciones en las cuales queremos guardar una historia de los cambios que realizamos en una celda o rango determinado. Por ejemplo, en una lista de precios queremos guardar la historia de los precios anteriores de cada artículo.

Una posibilidad es usar la funcionalidad Control de Cambios que nos ofrece Excel



EL problema con esta funcionalidad es que convierte el cuaderno en compartido, con la consiguiente pérdida de funcionalidades.

Un cliente me pidió que encontrara una solución más práctica. Mi solución fue utilizar los comentarios para guardar la historia de los cambios, asignándole a cada valor en el comentario la fecha en que fue introducido en la celda.

Mi solución utiliza una macro para crear y agregar información a los comentarios y un evento para disparar la macro cada vez que se cambia el valor de una celda en el rango relevante.

Al ingresar por primera vez un precio en la lista, se genera un comentario como este



Después de realizar un cambio en la celda, el comentario refleja los dos valores "históricos" de la celda



El código da la macro es


Sub track_change()
    Dim strPrevious As String
   
    With ActiveCell
        If .Comment Is Nothing Then
            .AddComment Text:=Format(Now, "dd/mm/yy hh:mm") & " - " & .Value
       
        Else
            strPrevious = .Comment.Text
            .Comment.Text Text:=strPrevious & Chr(10) & _
                        Format(Now, "dd/mm/yy hh:mm") & " - " & .Value
        End If
   
    End With
   
   
End Sub


Este código hay que ponerlo en un módulo corriente del editor de Vb.

El evento hay que ponerlo en el módulo de la hoja relevante


Private Sub Worksheet_Change(ByVal Target As Range)

    If Union(Target, Range("rngPrecio")).Address = Range("rngPrecio").Address _
            Then Call track_change
   
End Sub


Como pueden apreciar, estoy utilizando un rango dinámico dentro de un nombre (rngPrecio) para controlar el rango donde aplicamos el control de cambios

=DESREF(Hoja1!$C$2,1,,CONTARA(Hoja1!$B:$B),1)



En nuestro ejemplo, el rango se aplica dinámicamente a la columna C que contiene los precios. Un detalle importante en la definición del nombre es "anclar" el rango en la celda C2. Esto es necesario para el caso que el usuario elimine todas las líneas de la lista de precios (estamos suponiendo que no borrará los encabezamientos).

Y un cambio más antes de publicar la nota



Un inconveniente con esta solución es que el tamaño del cuadro de texto de los comentarios no se adapta dinámicamente a la cantidad de texto contenido. Para visualizar todos los cambios, en algún momento deberemos cambiar el tamaño del cuadro manualmente.

21 comments:

María Luisa,  20 julio, 2010 18:25  

Gracias por este artículo, como todos los demás, muy interesante.

Mi duda es: es posible colocar la información del comentario en una hoja aparte automáticamente, de manera que pueda generar un reporte sobre los cambios?

de nuevo muchas gracias

Anónimo,  20 julio, 2010 18:29  

CONSULTA: Se podrían poner los cambios en el comentario en el orden inverso?
De forma que queden los últimos cambios en la parte de arriba del comentario.

Saludos.

Jorge L. Dunkelman 20 julio, 2010 20:10  

Si, se puede. Tal vez publique una nota sobre el tema.

Jorge L. Dunkelman 20 julio, 2010 20:16  

Para que el último comentario aparezca siempre en primer lugar hay que cambiar el orden de .Comment.Text de esta maner

.Comment.Text Text:=Format(Now, "dd/mm/yy hh:mm") & " - " & .Value & Chr(10) & strPrevious

Es decir, strPrevious aparece al final en lugar de al principio.

lic.ppsoto 21 julio, 2010 06:57  

Ejem Jorge... aplica para todas las versiones de excel..?? Yo aun trabajo con la 2003... sé que 2007 y 2010 son mejores, pero ya ve... no es x falta de ganas...

My best wishes..!!

Jorge L. Dunkelman 21 julio, 2010 07:07  

No lo he probado en Excel 2003, pero supongo que corre sin problemas.

Oscar Efrain 24 julio, 2010 09:37  

Hola Jorge.
Puedes graficar estas dos aserveraciones que haces?
"Este código hay que ponerlo en un módulo corriente del editor de Vb.

El evento hay que ponerlo en el módulo de la hoja relevante"

Un abrazo y gracias por ser grande

Jorge L. Dunkelman 24 julio, 2010 10:41  

Oscar,
los códigos de las macros se ponen en módulos comunes, esos que se pueden agregar en el editor de Vb con el icono Insertar Módulo o con el menú Insertar-Módulo.
Las hojas, como también otros objetos, tienen sus propios módulos y en ellos hay que poner el código de los eventos (evento= acción relacionada al objeto que genera una respuesta, como hacer correr una macro).
Para acceder al módulo de una hoja, podés hacer un doble-clic sobre el icono de la hoja en el editor, o a través del menú contectual que se abre con el botón derecho del maouse aputando a la pestaña de la hoja.

Anónimo,  16 septiembre, 2010 10:08  

Buenos días Jorge,

Soy Jose. Tengo un problemilla con el control de cambios. Cuando realizo un cambio en una celda (C3 por ejemplo) me genera un comentario en la celda siguiente (siguiendo el ejemplo, en C4). Y en el comentario vemos la fecha y hora de la modificación y, lo más curioso, el último valor de C4.

He seguido tus instrucciones y he repetido el ejercicio varias veces en distintos cuadernos. Pero nada. No sé sí influirá el hecho de que estoy trabajando con el Excel 2003.

Oyes, ya por curiosidad. No sé sí sería rizar el rizo, pero sería posible que en el comentario se dejara el rastro del "usuario" que ha realizado la modificación. ¿Habría alguna forma?

Y nuevamente, muchas gracias por tu ayuda.


Un saludo!

Anónimo,  16 septiembre, 2010 10:16  

Buenas Jorge,

Soy Jose otra vez. Se me acaba de ocurrir, ¿podríamos generar el comentario en lugar de la celda que hemos modificado en la siguiente a la derecha? Por ejemplo, podifico C4 y me crea el comentario en D5.

Te hago esta consulta porque he visto que podemos eleminar los comentarios y por un error perderíamos la trazabilidad. Y una alternativa que se me ha ocurrido es generar los comentarios en otra celda que podría estar oculta o protegida.

Muchas gracias!

Jorge L. Dunkelman 16 septiembre, 2010 11:23  

Hola José,
en la segunda nota sobre el tema creo que encontrarás respuestas a parte de tus consultas.
En cuanto a la ubicación del comnetario haré algunas pruebas en Excel 2003, para ver si hay algún problema con el código.

Mostrar nombre 08 febrero, 2011 22:33  

Buenos dias,

Llevo mucho tiempo intentando esto pero no soy capaz, me gustaria saber si se puede hacer y si sois tan amables de darme un empujon.

Tengo 6 hojas de excel dentro de un libro , 1 de ellas la tengo vinculada con otra hoja en otro libro a traves de xlm, y las otras 5 hojas son para cuando tu introduzcas valores se sumen y se resten entre ellas. Yo utilizo el excel 2007 y tiene una pestaña de control de cambios pero no la puedo utilizar porque tengo vinculada una de las hojas por xlm y no me deja.

Mi pregunta es la siguente, se puede hacer una macro la cual revise las 5 hojas que yo quiero y me de en una de las hojas un resumen de los cambios que se han hecho (quien lo cambio, hora del cambio, celda que cambio).

Muchas gracias.

Un salud

Jorge L. Dunkelman 09 febrero, 2011 16:40  

Primero fijate en esta nota.Tienes que crear un evento para cada una de las cinco hojas y modificar la macro "track_change" para que registre también el nombre de la hoja donde se generó el cambio.

jaimeadz 07 abril, 2012 01:55  

Buenas tardes, Como hago para que los cambios se registren cuando realmente modifico un precio y no cuando me paro sobre la celda. mil gracias

Jorge L. Dunkelman 09 abril, 2012 19:28  

Los cambios sólo re registran cuando se efectúa una modificación en la hoja. Al apuntar a la celda se hace visible el comentario que describe el cambio.

Felipe Cardenas 12 octubre, 2012 18:01  

Hola Jorge,

Mi problema es que no se pone la nota en las celdas C3 y C4 se ponen en otras celdas y solo muestra las fechas pero no el cambio

Jorge L. Dunkelman 15 octubre, 2012 21:11  

Felipe,
tendrías que enviarme el archivo para que haga una idea del problema.

AgustinBit,  23 noviembre, 2012 19:36  

Hola Jorge, llevo tiempo intentando añadir a la macro, el nombre de la hoja en la que se produce el cambio, pero como soy total inesperto en macros pues no doy con el argumento que deberia poner, para que funcione, hasta ahora todo lo que he intentado me da error, agradeceria que pudieseis echarme una mano. Muchas gracias.

Jorge L. Dunkelman 24 noviembre, 2012 11:25  

En este ejemplo usamos el cambio se registra el la misma celda donde se produjo por lo que registrar el nombre de la hoja es innecesario.
¿Te refieres a la posibilidad de aplicar el control a todas las hojas y registrarlos en una hoja aparte, como mostré en esta nota?

Anónimo,  04 septiembre, 2013 15:59  

Hola Jorge,

Esta nota esta increíble muchas gracias.
La intenté aplicar sin embargo al ejecutarla me sale el error de "Error de compilación" No se ha definido Sub o function" en la parte del módulo de la hoja y pone en amarillo la parte del código del evento "Call track_change"

Espero me puedas ayudar con este error

Jorge Dunkelman 04 septiembre, 2013 17:15  

Hola, el modelo tiene dos códigos. EL "track_change" (que es el que te falta) va en un módulo común del editor Vb; el código del evento va en el módulo de la hoja relevante. Aségurate que ambos códigos estén, cada uno en su lugar.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP