none
VBA TO TRACK CHANGES RRS feed

  • Question

  • Hi experts!

    I have a worksheet with a simple userform. Once a form is submitted (and added to the top of the list), the whole worksheet EXCEPT for columns <I> and <J> gets protected. And only by clicking <Edit>, a user can make changes.

    The reason why columns <I> and <J> are not protected is because when a user double-click them, their name (system username) gets entered. 

    Now what i would like is a way to track changes made to the sheet. I've found this code (can't remember where) and tweaked a little bit to make it applicable to my sheet. But each time i submit a new form, i'm getting an error with a yellow debug highlighted on Application.Undo. I'm just not sure what I should do to make it work for my userform. Although I quite like this macro, I dont have to stick with this particular style of tracking. As long as i could see what changes were made by whom, that's good enough for me. Can someone help please~~

    Thank you

    Jay

    Below is a change tracking code i've found over the net. 

    --------

    Private Sub Worksheet_Change(ByVal Target As Range)

        Const xRg As String = "A3:J1000"
        Dim strOld As String
        Dim strNew As String
        Dim strCmt As String
        Dim xLen As Long
        
        With Target(1)
            If Intersect(.Cells, Range(xRg)) Is Nothing Then Exit Sub
            strNew = .Text
            Application.EnableEvents = False
           Application.Undo
            strOld = .Text
            .Value = strNew
            Application.EnableEvents = True
            strCmt = "Last Modified: " & Format$(Now, "dd/mm/yyyy, hh:mm:ss") & " by " & Application.UserName & Chr(10) & "Modified From: " & strOld
            If Target(1).Comment Is Nothing Then
                .AddComment
            Else
                xLen = Len(.Comment.Shape.TextFrame.Characters.Text)
            End If
            With .Comment.Shape.TextFrame
                .AutoSize = True
                .Characters(Start:=xLen + 1).Insert IIf(xLen, vbLf, "") & strCmt
            End With
        End With

        ActiveSheet.Protect

    End Sub

    Below is my userform code

    Private Sub cmdAdd_Click()

        If tbMediaID.Value = "" Then
            MsgBox "Please enter Media ID", vbExclamation
            Exit Sub
        End If
        
           
        Dim foundcell As Range
        Dim search As String
        Dim erow As Long
        erow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        search = tbMediaID.Text
        Set foundcell = Worksheets("Sheet1").Columns(2).Find(search, LookIn:=xlValues, Lookat:=xlWhole)
                
        If foundcell Is Nothing Then
            If tbEndDate.Value = "DD/MM/YYYY" Then
            MsgBox "Please enter a valid end date", vbExclamation
            Exit Sub
            End If
                If cmbListItem.Value = "select" Then
                MsgBox "Please select the slide type", vbExclamation

                            
                Else
                Rows(3).Insert
                Rows(3).RowHeight = 60
                            
                tbTimestampDate.Value = Now
                tbTimestampDate = Format(tbTimestampDate.Value, "dd mmmm yyyy")
                tbTimestampTime.Value = Now
                tbTimestampTime.Value = Format(tbTimestampTime.Value, "hh:mm:ss")
                tbUsername.Value = Application.UserName
                
                Range("A3").Value = cmbListItem.Text
                Range("B3").Value = tbMediaID.Text
                Range("C3").Value = tbStartDate.Text
                Range("D3").Value = tbEndDate.Text
                Range("E3").Value = tbTitle.Text
                Range("F3").Value = tbBody.Text
                Range("G3").Value = tbNote.Text
                Range("K3").Value = tbTimestampDate.Text
                Range("L3").Value = tbTimestampTime.Text
                Range("H3").Value = tbUsername.Text
                
                Range("A3:L3").Interior.ColorIndex = 0
                Range("A3:L3").Font.ColorIndex = 1
                Range("A3:D3").Font.Name = "Gadugi"
                Range("A3:D3").Font.Size = 10
                Range("E3:G3").Font.Size = 9
                

            
                MsgBox "New slide added to the list"
            End If
            
        Else
        MsgBox "This Media ID is already in use." & Chr(10) & "Please try a different ID.", vbExclamation
        End If
            
    End Sub
    ---------
    Private Sub cmdClose_Click()
    Unload Me
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True

    End Sub

    Sunday, July 1, 2018 5:35 AM

Answers

  • Application.Undo only works with changes made in the Interactive Mode. It does not work with changes made with VBA code.

    You will need to save the values in the worksheet immediately prior to writing new values. This can be done on a hidden worksheet like the following example and then the values on the hidden worksheet can be used for your strOld variable.

    Save the previous values to the hidden worksheet to cells with matching Id for ease of use.

        Dim wsPrevVal As Worksheet
       
        Set wsPrevVal = Worksheets("PrevVals")     'This is the Hidden worksheet.
       
        wsPrevVal.Range("A3").Value = ActiveSheet.Range("A3").Value        'Save prev value
        Range("A3").Value = cmbListItem.Text        'Write new value
        'Worksheet change will now be called.  Use the Target address to locate the address in the hidden worksheet.
       
        wsPrevVal.Range("B3").Value = ActiveSheet.Range("B3").Value        'Save prev value
        Range("B3").Value = tbMediaID.Text          'Write new value
        'Worksheet change will now be called. Use the Target address to locate the address in the hidden worksheet.


    Regards, OssieMac

    • Proposed as answer by Terry Xu - MSFT Monday, July 2, 2018 5:15 AM
    • Marked as answer by jay.nz Tuesday, July 3, 2018 8:52 AM
    Sunday, July 1, 2018 6:27 AM

All replies

  • Application.Undo only works with changes made in the Interactive Mode. It does not work with changes made with VBA code.

    You will need to save the values in the worksheet immediately prior to writing new values. This can be done on a hidden worksheet like the following example and then the values on the hidden worksheet can be used for your strOld variable.

    Save the previous values to the hidden worksheet to cells with matching Id for ease of use.

        Dim wsPrevVal As Worksheet
       
        Set wsPrevVal = Worksheets("PrevVals")     'This is the Hidden worksheet.
       
        wsPrevVal.Range("A3").Value = ActiveSheet.Range("A3").Value        'Save prev value
        Range("A3").Value = cmbListItem.Text        'Write new value
        'Worksheet change will now be called.  Use the Target address to locate the address in the hidden worksheet.
       
        wsPrevVal.Range("B3").Value = ActiveSheet.Range("B3").Value        'Save prev value
        Range("B3").Value = tbMediaID.Text          'Write new value
        'Worksheet change will now be called. Use the Target address to locate the address in the hidden worksheet.


    Regards, OssieMac

    • Proposed as answer by Terry Xu - MSFT Monday, July 2, 2018 5:15 AM
    • Marked as answer by jay.nz Tuesday, July 3, 2018 8:52 AM
    Sunday, July 1, 2018 6:27 AM
  • Thanks OssieMac.

    I'm not very familiar with vba as you can probably tell.

    1. What do i do with Application.EnableEvents = False and Application.Undo? Do i just delete them?

    2. Where should your code go in to?

    Monday, July 2, 2018 8:07 AM
  • I did not realize that you are not sufficiently proficient with VBA to integrate the code into your project. To help you further I really require a copy of your workbook.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click in the field displaying the link and Ctrl and A should highlight the entire link and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Monday, July 2, 2018 8:30 AM
  • Sorry and thanks OssieMac. Here is my onedrive link to the workbook.

    My code may look quite fiddly and is all over the place. Please don't laugh. lol

    https://1drv.ms/u/s!AiYZvMO8U7EVkU-hE2zNA1VEDM9m

    Thank you!



    • Edited by jay.nz Monday, July 2, 2018 8:34 PM
    Monday, July 2, 2018 8:49 AM
  • I have the file but as the code is currently written it is really not practical to save the previous values in corresponding cells on a separate worksheet because every new record is inserted as row 3 and therefore the matching cells for all records when they are inserted will be row 3 in the Prev Values worksheet.

    What is the reason for inserting the row 3?

    Why can't the new records go on the bottom of the list so that all records retain a previous value at the same cell address on the separate worksheet?

    Are records likely to be deleted? If they are then it will be necessary to delete the corresponding record from the Prev Value worksheet.

    Unfortunately Excel doesn't really provide for data base audit trailing and it can get quite complex to write the code to ensure that the information is correct.

    You have indicated that that you are "not very familiar with vba" so how important is the audit trail data? Is it worth the effort?

    However, if you are prepared to accept a change and add new records to the bottom rather than insert them at the top then I will try to resolve the problem for you.

    As a point of interest, every data base should have an exclusive Id number that is system generated. Id numbers should NOT be reused. This ensures that the data connected to the Id is not mixed up with other data and provides a method of positive find for the data.


    Regards, OssieMac

    Tuesday, July 3, 2018 3:58 AM
  • Yeah it is messy. To answer your questions

    1. The actual list I am currently using does add a new record at the bottom. Users (including myself) don't like that though. Would like to have the latest at the top.

    2. Records will not be deleted. Well, should not.

    3. I manage the list and assign things around. Users are supposed to inform me of any changes they make, but they don't always. So, being able to see who changed what is only for me. Auditing per se is not required by other users. If I could somehow tell what's changed, that'd be good enough for me.

    4. I have suggested SharePoint (which would be perfect for things like this), but people find it too sluggish. Doh.

    If only macros were allowed with shared workbook...

    Suggestions?

    Tuesday, July 3, 2018 4:22 AM
  • On your point 3, "Audit Trail" is simply the name given to the process of recording changes etc to data bases. While it can be extensive for auditors to use it, it doesn't necessarily mean that the processes are being extensively audited. What you are using the information for is a form of auditing even if only for you own purposes. Actually a full audit trail keeps a record of everything including if someone only looked at the record and I fully understand that is not what you require.

    I can do what you want by using a different method but it will require using unique record Id's. That way the records can just be copied to the next vacant row on the Previous Vals Sheet and then look them up by the record Id. Any other method is fraught with danger if anything gets out of kilter due to a multitude of things that can go wrong.

    My suggestion is to insert a new column for column A and move everything else across by one column. The system can generate the new numbers. We use the worksheet function Max of the column +1 and never reuse numbers.

    Let me know if you want to go ahead with the record numbers.


    Regards, OssieMac

    Tuesday, July 3, 2018 7:15 AM
  • Oh I see what you mean. 

    Love the idea of using unique IDs! Go for it!

    And you are right. I certainly dont want to risk playing with any complicated method that i wouldnt know how to fix. 

    I'd truly appreciate your time and help. 

    Jay

    Tuesday, July 3, 2018 7:31 AM
  • Hello jay,

    It seems that you original issue has been resolved. I would suggest you mark the helpful reply to close the thread.

    If you have any other issue, please feel free to post thread to let us know.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 3, 2018 8:45 AM
  • Oh I see what you mean. 

    Love the idea of using unique IDs! Go for it!

    And you are right. I certainly dont want to risk playing with any complicated method that i wouldnt know how to fix. 

    I'd truly appreciate your time and help. 

    Jay

    How would you like to provide me with an email address and then we can communicate privately. There will be quite a lot of information that I will require so that we can plan the project correctly in the first place instead of a piecemeal attack on it that finishes up with code like a patchwork quilt.

    Can't include a proper email address here because Microsoft deletes anything that appears to be an email address. However, you can provide one similar to the following. When I have it you can come back and delete the post so it does not remain there indefinitely. I can't provide mine for a very good reason but if you provide yours I will explain why I can't when I reply. If you are concerned about spam etc then create a new email address and you can delete it when we are finished with it.

    name at domainname dot com dot xx             (Where dot xx is for country if included in your email address)


    Regards, OssieMac

    Tuesday, July 3, 2018 10:52 PM
  • Hi OssieMac,

    Thank you very much for looking into all this for me! It seems that this vba could become far too complicated than i could handle. :-0  I think i would go back and see what i can to make your initial suggestion, which i marked as answered, work for my sheet. 

    Again, thank you very much for your time and help!!!

    Jay

    Wednesday, July 4, 2018 7:41 AM
  • Hello again Jay,

    I think that maybe you are worrying over an issue that is unlikely to occur. I have been programming for over 40 years and if the code is written in a structured and modular way with comment documentation throughout then it is not too hard to follow.

    However, I also understand you wanting to accomplish the project yourself but please feel free to get back to me because I will help if I can.

    One question: Is the Media Id an exclusive number for every record? If it is then it could be used for the record Id. The record Id does not need to be consecutive numbers; just unique.


    Regards, OssieMac

    Wednesday, July 4, 2018 8:39 AM
  • :-)

    Yes although Media IDs are manually created, they are and must be unique for each record. That's why I included the code to look for duplicates in the existing table.

    Cheers!

    Wednesday, July 4, 2018 9:02 AM
  • :-)

    Yes although Media IDs are manually created, they are and must be unique for each record. That's why I included the code to look for duplicates in the existing table.

    Cheers!


    OK. I will have another look at and I believe that we can make this work by using the Media Id as the exclusive record Id. It will be tomorrow before I do any more on it but I will keep you updated on progress.

    Regards, OssieMac

    Wednesday, July 4, 2018 10:53 AM
  • Further question. If an existing record is to be updated how do you propose that this should be done? Currently you do not appear to have any method of reading an existing record into the Userform for updating.

    Do you intend to update directly on the worksheet or do you intend to update from the Userform. If you propose updating on the Useform then the usual method is when the Media Id is entered then the "AfterUpdate" event is used and the code first searches for an existing record and if not found then the user continues to enter the remaining data. If found then it populates the Userform with the found data and the user can update as required and then save.

    Note that the After Update event on the Userform is similar to the Change event on the worksheet. The change event on a userform fires with every character typed in the userform field.

    If it is your intention to only update directly on the worksheet then you can use the code you already have. Just insert Application.EnableEvents = False at the start of Private Sub cmdAdd_Click() and Application.EnableEvents = True at the end of the sub. Because it is only adding a new record and the comments are probably not required and with events disabled,  Worksheet Change event will not be called when the new record is added.

    Tip on VBA Programming: When Using Find in VBA all of the parameters (Except After:=ActiveCell) should be included in the code (See your Find in the code where I have edited it). There are good reasons for this because Find options previously set can be retained and cause problems if not reset in the VBA code. After:=ActiveCell should not be used unless you are positive that the active cell will always be within the search range.

    Very early in my programming days I was taught "always specify and don't use defaults" and therefore all of the Find parameters should be specified.

    See the following example workbook that I have updated so that changes can be made on the worksheet. If not what you want and you prefer an option to update via the userform then let me know.

    https://1drv.ms/u/s!ArAXPS2RpafCmh8pn9c01zgrrEc4


    Regards, OssieMac

    Thursday, July 5, 2018 2:48 AM
  • I have just realized that there are problems with the dates. In columns C and D the d/m/y dates are getting the day and month reversed as if they are m/d/y dates. The dates are actually the wrong dates when outputted to the sheet.

    In column K the actual number format of the date is set to m/d/y.

    Because I am confused with the difference with columns C and D with number format set to dd/mm/yyyy and column K with number format set to m/d/yyyy can you please let me know what format you want to use for each column and I will correct the VBA code.


    Regards, OssieMac

    Thursday, July 5, 2018 4:06 AM
  • What! No they should be the same format, dd/mm/yyyy. In fact, all dates on the form should be dd/mm/yyyy.

    "If an existing record is to be updated how do you propose that this should be done? Currently you do not appear to have any method of reading an existing record into the Userform for updating."
    - You read my mind. That's exactly what I am working on or googling and youtubing to be honest... If a user clicks on the form, it opens up with the values of the active cell/row. But if this gets too complicated, I'm happy to update on the worksheet.

    Tip on VBA Programming: When Using Find in VBA all of the parameters (Except After:=ActiveCell) should be included in the code (See your Find in the code where I have edited it).
    - Thank you for the tip!

    Thursday, July 5, 2018 4:22 AM
  • I have uploaded another example with the updating of the record on the worksheet to the following link. The problem with the dates was with your code of converting the strings to dates etc. See my edits in the code and following description.

    https://1drv.ms/u/s!ArAXPS2RpafCmiEKK4xTqugHhJwI

    I am sure you understand that all values in the userform controls are in string format.

    Because you had not used DateValue, your code was attempting to convert a string date to a specific format (d/m/y) by first coercing the string date to a date. Unfortunately, when this is done without using the DateValue function, Excel assumes the string is in m/d/y format. The DateValue function uses the regional date format of the computer setup to ensure that it interprets the string date correctly (in this case as d/m/y) and effectively it is converted to a real date before converting it back to a string format in the required date format.

    Also when a string date is written to the worksheet, it should be converted to a real date with DateValue before writing to the worksheet.

    Test with updating the record on the worksheet. If you decide that you would prefer editing on the Userform then it is more complex but let me know and I will provide some code to load the Userform with the selected record.


    Regards, OssieMac

    Thursday, July 5, 2018 10:45 AM
  • Thank you so much OssieMac!

    It works perfectly! And also thanks for all the tips and explanations in the code, which help me understand coding better. Nah, i'm more than happy with how it is. 

    Really appreciate your time and help!!! I can't wait to use what i've learned for my next vba project! 

    Jay

    Saturday, July 7, 2018 2:50 AM