I'll try and walk you through an example. I want to record changes to the "Data" worksheet in the "Log" worksheet. Here are my two worksheets:
First - right click the "Data" worksheet and click "View Code". This will open the VBA window for that particular sheet. Notice the
blue cross, this is code specific to this sheet:
By clicking the dropdown boxes (see the
red crosses) I selected "Worksheet" in the first box and then several options are available in the second box. These options trigger code to run depending on what you've selected. In the picture above I selected "Change". If any cells change in the "Data" worksheet then where it says "Your code goes here" will run.
So, you make a change to the "Data" worksheet and the macro is triggered. The "Target" variable (
pink underline) tells you which cell or cells have changed. Because more than one cell may have changed we'll need to loop through each of them like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR="Yellow"] Dim Rng As Range
For Each Rng In Target[/COLOR]
'Your code goes here
[COLOR="Yellow"] Next[/COLOR]
End Sub
Next I want to record some information to put into my log:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
[COLOR="Yellow"] Dim TimeAndDate As String
Dim CellAddress As String
Dim CellValue As Variant[/COLOR]
For Each Rng In Target
[COLOR="Yellow"] TimeAndDate = Format(Now, "hh:mm:ss dd/mm/yyyy")
CellAddress = Rng.Address
CellValue = Rng.Value[/COLOR]
Next
End Sub
Now we need to start adding things to the log. First I want to find the next available row in the log. I'm using a different method to my previous post, it's a little less efficient but hopefully a little more intuitive. Using "Do" I'm looping through each cell in the first column until I come across a blank cell and then stopping:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim TimeAndDate As String
Dim CellAddress As String
Dim CellValue As Variant
[COLOR="Yellow"] Dim LogRow As Long[/COLOR]
For Each Rng In Target
TimeAndDate = Format(Now, "hh:mm:ss dd/mm/yyyy")
CellAddress = Rng.Address
CellValue = Rng.Value
[COLOR="Yellow"] LogRow = 0
Do
LogRow = LogRow + 1
Loop Until Sheets("Log").Cells(LogRow, 1) = ""[/COLOR]
Next
End Sub
Now I know which row in the log is free I can start adding my data to it:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim TimeAndDate As String
Dim CellAddress As String
Dim CellValue As Variant
Dim LogRow As Long
For Each Rng In Target
TimeAndDate = Format(Now, "hh:mm:ss dd/mm/yyyy")
CellAddress = Rng.Address
CellValue = Rng.Value
LogRow = 0
Do
LogRow = LogRow + 1
Loop Until Sheets("Log").Cells(LogRow, 1) = ""
[COLOR="Yellow"] Sheets("Log").Cells(LogRow, 1).Value = TimeAndDate
Sheets("Log").Cells(LogRow, 2).Value = CellAddress
Sheets("Log").Cells(LogRow, 3).Value = CellValue[/COLOR]
Next
End Sub
Notice how I used
.value = to set the values. This is much more robust that using copy & paste. Suppose you have a macro running and you copy some text in another program, that could cause the macro to fall over. Similarly, defining specific cells (e.g.
Sheets("Log").Cells(3, 2)) is much more robust than using
.Select and
Selection.
If you click in the margin immediately to the left of the code the line will turn red and the code will pause at that point. (Typing the word
Stop in the code will do the same thing). You can then use F8 to step through each line of the code in turn. That will help a lot with understanding what's happening at each step.
I hope that helps. You can download the workbook
here
5.