+ Reply to Thread
Results 1 to 10 of 10

Log Files within an Workbook to track changes

  1. #1
    Registered User
    Join Date
    09-12-2017
    Location
    Trinidad and Tobago
    MS-Off Ver
    2010, 2016, 365
    Posts
    45

    Log Files within an Workbook to track changes

    Hi,
    I created a quotation template for a small business, I would like to create a log work sheet so that I can track or keep a count of all the quotes that was done.

    Eg.
    Lets say the Quote # is in Cell A1 and the Customer is stored in Cell B2 and the Value is Stored on C3. For every time a quote is Save/ SaveAs the information is stored in a worksheet, this process continues to log/ save the Quote #, Customer & Value in the log worksheet every time someone Save/SaveAs a quotation.

    The information in the worksheet will be stored as follows Column A,B,C Rows 1,2,3,4,5,6,7 etc.


    Any assistance would be greatly appreciated.


    Regards,
    OB

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Log Files within an Workbook to track changes

    Code in ThisWorkbook module :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    09-12-2017
    Location
    Trinidad and Tobago
    MS-Off Ver
    2010, 2016, 365
    Posts
    45

    Re: Log Files within an Workbook to track changes

    karedog,
    I used the code and it works great, I have one more request, I have a force "SaveAs" macro that forces the user to only "SaveAs" a new quote, this to maintain and keep my "Quotation Template" exactly that.

    You code works fine if I "Save" the document, however when I "SaveAs" a new quote the code does not log any information in the backup sheet.

    Any suggestions?


    Regards,
    OB

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Log Files within an Workbook to track changes

    Must be your "Force SaveAs" procedure that cancel the job.
    You must show all your code in ThisWorkbook (and code in Normal Module, if any procedure in this module is called from ThisWorkbook).
    Or, better upload your file here.

  5. #5
    Registered User
    Join Date
    09-12-2017
    Location
    Trinidad and Tobago
    MS-Off Ver
    2010, 2016, 365
    Posts
    45

    Re: Log Files within an Workbook to track changes

    Hi thank you for responding, see my codes below, I will try to remove the sensitive information from my template and upload if the codes are not a help.

    NB: FULL DISCLAIMER. I am a newbie at VBA & Macro, but with Google and you guys I got by.

    So far my template works great. I am 99% complete, I would only like to add the log/ tracking work sheet so I can generate a report at the end of the week.
    I know that Access would be the ideal solution for my purpose, but for not this will do.

    All the following codes are in "ThisWorkbook" all macros run and the end result are ok thus far (fingers crossed).

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Kind regards,
    OB

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Log Files within an Workbook to track changes

    Not too sure since I don't know which workbook is being opened and active, etc; but try this :

    Code on ThisWorkbook module :
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-12-2017
    Location
    Trinidad and Tobago
    MS-Off Ver
    2010, 2016, 365
    Posts
    45

    Re: Log Files within an Workbook to track changes

    Hello karedog,
    I added the code you provided, but I can get it to work, can you take a look at it.
    Also can you look at module 20, that is the macro for the save button on the quotation.

    Password: password1.


    Kind regards,
    OB
    Attached Files Attached Files

  8. #8
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Log Files within an Workbook to track changes

    Hi Omkar,

    The Sub RecordQuote() is worked, it's because the sequence of how you save two workbooks (the template and the other one) that disturbed.
    I has modified the code to what I think is most logical to me. Should this is not what you want, I'm sorry I cannot help you further, but I guarantee the Sub RecordQuote() is worked, but how the final behaviour of what you want is trivial (only you who know it).

    Regards
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-12-2017
    Location
    Trinidad and Tobago
    MS-Off Ver
    2010, 2016, 365
    Posts
    45

    Re: Log Files within an Workbook to track changes

    karedog,

    Your code works fine but my challenge is the changes are saved to my template for example:
    If I create a quotation for Mr. John Customer & the quote number is 123, when I save the new quote as MTBP-123 - Mr. John Customer this info is also saves in my template in the Quotation Sheet also all the information related the the quote done for Mr. John Customer.

    I will like only the log information to be saved to the template and nothing else. if you look back at the template i previously sent there are two behaviors;
    1. A new quote is saved
    2. My template closes and all the information in my template is as it was originally except for the quote number.

    Is there a way for only the log information to be save on the Backup sheet in a similar way the quote number is saved.


    I have tried numerous scenarios with the RecordQuote code but unsuccessful, if you can help me with this you will be my new hero.

    Thank you in advance.

    Kind regards,
    OB

  10. #10
    Registered User
    Join Date
    09-12-2017
    Location
    Trinidad and Tobago
    MS-Off Ver
    2010, 2016, 365
    Posts
    45

    Re: Log Files within an Workbook to track changes

    One more thing basically i want my template "Quotation" sheet to remain unedited except for the "Quote No" and the "Backup" sheet to save the log.

    Please see attached quote template with these 2 scenarios working;

    1. A new quote is saved (NB: The new quote remains open with the necessary quote details)
    2. My template closes and all the information in my template is as it was originally except for the quote number. (NB: This is where it gets tricky, the template closes, however the quote number chages, I would like to get the "Backup" sheet to save the log)

    Please create a quote by editing Cell A8 and use the save button on the quote and take a look at the behavior.


    Is this even possible?

    Kind regards,
    OB.
    Attached Files Attached Files
    Last edited by Omkar Balgobin; 10-14-2017 at 05:29 PM. Reason: Attached file and add details.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to Track Who is Viewing PowerPoint Files
    By SIintranet in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2017, 07:14 PM
  2. VBA Macro - Keep Track History - Shared Files
    By fataemorganae in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2016, 08:13 AM
  3. Ideas on how to track weekly trends from multiple files
    By Nurseryboy in forum Excel General
    Replies: 3
    Last Post: 08-03-2013, 04:03 AM
  4. Replies: 4
    Last Post: 06-24-2013, 11:16 AM
  5. updating multiple files, how do I track files that give errors?
    By jojotherider in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2008, 03:45 PM
  6. Replies: 1
    Last Post: 12-16-2006, 04:23 PM
  7. Using VBA to track workbook changes
    By Mark Ivey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-07-2006, 09:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1