Do you like a bit of Big Brother?. Or do you just want to know if someone has changed something on your Excel worksheet? Either way, here is how to track and document changes to an Excel workbook with a small bit of Excel VBA Code. Let’s get our #macromonday fun on.
What Does The Macro Do?
This Macro will log an audit trail of any changes to your Excel worksheet and save these changes made including the username to a separate log sheet in the workbook.
How Does The Macro Work?
We first need to create a new module in the Excel workbook we are working on to store the VBA code. In this example, my workbook is called it is My Excel Log. You can insert a module by opening Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. You can now hit Insert Module.
After that, you also need to add a new worksheet which will display the log details. As you can see in my example I have called my log worksheet simply ‘Logged Changes’.
Step 1. We first need to declare a variable. In this macro, it is just the one, Dim PreVal. This simply means that Excel allocates some memory to enable it to store this information and represent this value. Note that we set this outside of the Subroutines, meaning it is available to use in both routines.
Step 2. The worksheet change event is triggered if we change the cell we have selected. If the value is not the same then logging begins.
Step 3. This is the stage that the logging of the details we want happens. We go down 65000 cells, to give plenty of room to log our changes, then go back up to the last occupied cell in the column, then offset this by one cell in our column. This finds the next free cell to begin the logging of details. We then pull in the details below:-
UserName
Insert the text “changed cell“,
Then insert the Target.Address
Next, insert the text “from”
After that, we insert the PreVal
Insert the text “to”
Insert the Target.Value (new value)
That’s it. Let’ take a look at an example below in action.
What’s Next? Do You Want To Copy The Code?
[stextbox id=”grey”]
Dim PreVal
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘MACRO BY HOW TO EXCEL AT EXCEL
‘1. This sets our previous value once we have selected the cell value to change
PreVal = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
‘2. If we change the cell we selected then the worksheet change event is triggered
If Target.Value <> PreVal Then
‘3. If the new value of the cell is not the same a previous value then logging of details begins
Sheets(“Logged Changes”).Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
Application.UserName & ” changed cell ” & Target.Address _
& ” from ” & PreVal & ” to ” & Target.Value
End If
End Sub
[/stextbox]
You can check out my full list of Macro Mondays blog posts, and the code is always free for you to copy at the end of the blog post.
If you want more tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
