Track Changes via Macro

[ManSim]

Hi all,
I am brandnew to VBA but learn a bit more every day. Now I have the following problem that needs your help:
I have about 15 different excel inventories and need to check every Monday if changes were made in the last week. All files are shared hence "tracking changes" is basically possible.
Now instead of clicking the mouse so many times I attempted to start recording a macro to ease my work. The result was the following code:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sub TrackChanges()
'
' TrackChanges Macro
'
<o> </o>
'
With ActiveWorkbook
.HighlightChangesOptions When:="15.03.2010"
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = True
End With
End Sub
<o> </o>
<o> </o>
Unfortunately this macro does not really work - nothing happens! What is wrong with this?
<o> </o>
PS. At a later stage I want to replace the hard coded date with an input msgbox...

Much appreciate any comment.
Thanks in advance

 

[sarbo]

Hey Markus!

first of all thanks heaps for the macro! works perfectly!!!
Since all the data changes are stored, do you have an idea of how to create an Undo button...?!

If you have any idea or point me in the right direction, that would be great!

thanks again!

 

[arkusM]

Ha! I have thought about doing that but have not pursued it. It would theoretically be possible, but would likely require some changes to the original code to better track the source. But from there you should be able to do a look up on the current target cell and look it up in the tracking sheet and build a set of previous values in a userform... hmm. Interesting. Not something I have time to pursue. But it you do and figure it out I would love to see your result.

Cheers.

Hey Markus!

first of all thanks heaps for the macro! works perfectly!!!
Since all the data changes are stored, do you have an idea of how to create an Undo button...?!

If you have any idea or point me in the right direction, that would be great!

thanks again!

 

[graudales]

Good morning ArkusM, just wanted to thank you for this code, works like a charm, I just have a question I am new on VBA and is amazing all the things that can be done, have a sheet named "schedule" and I added the tracket sheet, and everything works perfect, my only problem is that i am updating quantities and dates in the "schedule" tab, so when I go to tracker it shows 1/3/1900 instead of 3, is there a way to add to this code the copy special option so its copy the format of the cell is taking the change from? thank you in advance, this code fix the problem I had , thank you again ArkusM you are fantastic.

 

[arkusM]

There is a way, unfortunately I do not have the time these days to change the code.
you quickest solution is to change the format of the cells on the "Tracked Changes" sheet. so where you are getting the 1/3/1900 Change the columns format to "General". This should solve your problem.
Excel has this annoying habit of inheriting date formats from cell above.

I am glad you find the code useful.
Cheers.

Mark

Good morning ArkusM, just wanted to thank you for this code, works like a charm, I just have a question I am new on VBA and is amazing all the things that can be done, have a sheet named "schedule" and I added the tracket sheet, and everything works perfect, my only problem is that i am updating quantities and dates in the "schedule" tab, so when I go to tracker it shows 1/3/1900 instead of 3, is there a way to add to this code the copy special option so its copy the format of the cell is taking the change from? thank you in advance, this code fix the problem I had , thank you again ArkusM you are fantastic.

 

[graudales]

Thank you so much, I will try the formatting, your code is just the best thing ever...Thank you really for sharing your knowledge.

 

[Omkar Balgobin]

Re: Track Changes via Macro - Log Creation

Good day arkusM,

I have been searching the internet for something like this for a while, thus far this is the closest that I have found that can serve my purpose.

Disclaimer: I an very new/ green to VBA but with the internet and help from others I have been successful with my project thus far.
Project: I have created a quotation template for my business that auto generates and saves a quotation number for that quote (saves a copy of the new quote with the new quote number and save ONLY the quote number in the template).
Request:In my template document I a trying to create a log file to log specific information (similar to a report) the information that I would like to log are Quote No., Date, Expiration, Customer, Transaction, Model, Total.

The Quotation Tempalte is Sheet1 in my workbook renamed "Quotation" and the Cells for that captures the requested data are as follow;
Quote No.: Cell:L9
Date: Cell:L7
Expiration: Cell:L8
Customer: Cell:A8
Transaction: Cell: A14
Model: Cell:A18
Total: Cell:L52

I have created a sample of what I hope my log file/worksheet can look like;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Quote (L9)[/TD]
[TD]Date (L7) [/TD]
[TD]Experation (L8)[/TD]
[TD]Customer (A8) [/TD]
[TD]Transaction (A14) [/TD]
[TD]Model (A18) [/TD]
[TD]Subtot (L52) [/TD]
[/TR]
[TR]
[TD]Quote0001[/TD]
[TD]26-Sep-17[/TD]
[TD]26-Oct-17[/TD]
[TD]Joe Enterprise Ltd[/TD]
[TD]Lease[/TD]
[TD]GHT345A[/TD]
[TD]$ 10,000[/TD]
[/TR]
[TR]
[TD]Quote0001[/TD]
[TD]27-Sep-17[/TD]
[TD]27-Oct-17[/TD]
[TD]ABC Trading[/TD]
[TD]Sale[/TD]
[TD]RX829-1[/TD]
[TD]$ 250,010[/TD]
[/TR]
</tbody>[/TABLE]

Question and Humble Request;
1. Is there a way to incorporates your code to track changes and get my desired results?
2. If so can you assist me with putting together such a code.


I do look forward to your assistance and would appreciate the same.

Thanking you in advance,.

Regards,
OB

 

[Dman333]

Hi arkusM
This post and code really helps me out a lot so Thanks!!

I have two additional needs and I was wondering if you might be able to help?

First, I need to get the sheet name and cell reference into two different columns. I' prefer not to have the filename.

Second, I need to grab input from other cells in the row so I can turn the tracker into a table for greater utility. I can name the 2 columns I want to grab the same range name for each sheet in the workbook. But don't know how to get the info to propagate into the tracker.

Any help would be greatly appreciated and thanks in advance for taking a look at this.

Cheers,
Dman333

 

[arkusM]

Re: Track Changes via Macro - Log Creation

Omkar,

My apologies I didn't see this message until now April 30, 2018), Unfortunately I don't have the time anymore to further develop this code.

Best of luck,

Mark

Good day arkusM,

I have been searching the internet for something like this for a while, thus far this is the closest that I have found that can serve my purpose.

Disclaimer: I an very new/ green to VBA but with the internet and help from others I have been successful with my project thus far.
Project: I have created a quotation template for my business that auto generates and saves a quotation number for that quote (saves a copy of the new quote with the new quote number and save ONLY the quote number in the template).
Request:In my template document I a trying to create a log file to log specific information (similar to a report) the information that I would like to log are Quote No., Date, Expiration, Customer, Transaction, Model, Total.

The Quotation Tempalte is Sheet1 in my workbook renamed "Quotation" and the Cells for that captures the requested data are as follow;
Quote No.: Cell:L9
Date: Cell:L7
Expiration: Cell:L8
Customer: Cell:A8
Transaction: Cell: A14
Model: Cell:A18
Total: Cell:L52

I have created a sample of what I hope my log file/worksheet can look like;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Quote (L9)[/TD]
[TD]Date (L7) [/TD]
[TD]Experation (L8)[/TD]
[TD]Customer (A8) [/TD]
[TD]Transaction (A14) [/TD]
[TD]Model (A18) [/TD]
[TD]Subtot (L52) [/TD]
[/TR]
[TR]
[TD]Quote0001[/TD]
[TD]26-Sep-17[/TD]
[TD]26-Oct-17[/TD]
[TD]Joe Enterprise Ltd[/TD]
[TD]Lease[/TD]
[TD]GHT345A[/TD]
[TD]$ 10,000[/TD]
[/TR]
[TR]
[TD]Quote0001[/TD]
[TD]27-Sep-17[/TD]
[TD]27-Oct-17[/TD]
[TD]ABC Trading[/TD]
[TD]Sale[/TD]
[TD]RX829-1[/TD]
[TD]$ 250,010[/TD]
[/TR]
</tbody>[/TABLE]

Question and Humble Request;
1. Is there a way to incorporates your code to track changes and get my desired results?
2. If so can you assist me with putting together such a code.


I do look forward to your assistance and would appreciate the same.

Thanking you in advance,.

Regards,
OB

 

[arkusM]

Dman333,

Unfortunately I don't have the time these days to help work through these changes.
What you are looking to do shouldn't be too difficult.
The values tracked are written into the tracking sheet with .offsets so those can be added to or modified as you need
Research Target.Offset(x,y) for extracting more info from the source sheet, put it in a variable and write into your tracking sheet with offsets (above line)

I know this answer is vague and I apologize, very busy at work these days...

Good luck

Mark

Hi arkusM
This post and code really helps me out a lot so Thanks!!

I have two additional needs and I was wondering if you might be able to help?

First, I need to get the sheet name and cell reference into two different columns. I' prefer not to have the filename.

Second, I need to grab input from other cells in the row so I can turn the tracker into a table for greater utility. I can name the 2 columns I want to grab the same range name for each sheet in the workbook. But don't know how to get the info to propagate into the tracker.

Any help would be greatly appreciated and thanks in advance for taking a look at this.

Cheers,
Dman333

 

[Dman333]

Dman333,

Unfortunately I don't have the time these days to help work through these changes.
What you are looking to do shouldn't be too difficult.
The values tracked are written into the tracking sheet with .offsets so those can be added to or modified as you need
Research Target.Offset(x,y) for extracting more info from the source sheet, put it in a variable and write into your tracking sheet with offsets (above line)

I know this answer is vague and I apologize, very busy at work these days...

Good luck

Mark

No worries - Thanks for responding.
I did some research on using Target and was able to do what I needed. It just took me a while on Google to really understand what you were doing in the code,

One thing though. The only struggle I'm still having - and it may not be possible - is I'd like to grab the value of a cell that is a formula based on the one being changed (ie not the target) and can't seem to find the correct command to do that (if there is one). do you know if that's possible? It seems like short of forcing a manual calc, then grabbing the cell, and then forcing a calc might be the only solution (if that's even possible). I want to show the total budget before and after the cell change. The after part I got down. Any direction on the before part?

Thanks,
Dan