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.
Gracias por este artículo, como todos los demás, muy interesante.
ResponderBorrarMi 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
CONSULTA: Se podrían poner los cambios en el comentario en el orden inverso?
ResponderBorrarDe forma que queden los últimos cambios en la parte de arriba del comentario.
Saludos.
Si, se puede. Tal vez publique una nota sobre el tema.
ResponderBorrarPara que el último comentario aparezca siempre en primer lugar hay que cambiar el orden de .Comment.Text de esta maner
ResponderBorrar.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.
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...
ResponderBorrarMy best wishes..!!
No lo he probado en Excel 2003, pero supongo que corre sin problemas.
ResponderBorrarHola Jorge.
ResponderBorrarPuedes 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
Oscar,
ResponderBorrarlos 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.
Buenos días Jorge,
ResponderBorrarSoy 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!
Buenas Jorge,
ResponderBorrarSoy 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!
Hola José,
ResponderBorraren 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.
Buenos dias,
ResponderBorrarLlevo 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
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.
ResponderBorrarBuenas tardes, Como hago para que los cambios se registren cuando realmente modifico un precio y no cuando me paro sobre la celda. mil gracias
ResponderBorrarLos 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.
ResponderBorrarHola Jorge,
ResponderBorrarMi 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
Felipe,
ResponderBorrartendrías que enviarme el archivo para que haga una idea del problema.
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.
ResponderBorrarEn 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.
ResponderBorrar¿Te refieres a la posibilidad de aplicar el control a todas las hojas y registrarlos en una hoja aparte, como mostré en esta nota?
Hola Jorge,
ResponderBorrarEsta 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
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.
ResponderBorrar