Private Sub Worksheet_SelectionChange/Worksheet_Change

Question

Good day, I need to create 2 private macros in one workbook - one which stores content of cell after clicking on it and second one which will store the new value of cell and send and email with body of old text in cell and new text in cell.

Truth to be told, I'm not sure if this is the right way to do it (or if it's even possible) but I don't work with private macros often so I will appreciate any help. Thanks a lot!

That is what i got so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
OldCellValue = ActiveCell.text
old_value = OldCellValue
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Area As Range
Dim OutlApp As Object
Dim IsCreated As Boolean
Dim cell As String
Dim old_value As String
Dim new_value As String

Set Area = Range("A1:E20")

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Area) Is Nothing Then

cell = ActiveCell.Address
new_value = ActiveCell.text

On Error Resume Next
 Set OutlApp = GetObject(, "Outlook.Application")
 If Err Then
   Set OutlApp = CreateObject("Outlook.Application")
   IsCreated = True
 End If
 OutlApp.Visible = True
 On Error GoTo 0

 With OutlApp.CreateItem(0)

   .Subject = "Change in table"
   .to = "someones email"
   .HTMLBody = "Change in cell " & "<B>" & cell & "</B><br>" _
                & "Old value: " & old_value & "New value: " & new_value

   On Error Resume Next
   .Send
   Application.Visible = True
   On Error GoTo 0

 End With

 If IsCreated Then OutlApp.Quit

 Set OutlApp = Nothing

End With

End If

End Sub

What's your problem - does the code not work, not do what you want....? — SJR, Sep 27 '17 at 16:48
This code doesn't work. It will load the text from cell when I click but when I make a change and press Enter, it will move to next row which will change variable in on old_value. Sometimes it even showed empty cell in old_value even if there was text in cell and in cell beneath it. — Zdeněk Bužga, Sep 27 '17 at 16:53

score 0 · Accepted Answer · answered Sep 27 '17 at 16:57

0

Not re-writing all your code, but in essence you have to do this to store the value when the cell is selected and then after it is changed. You don't need the newcellvalue variable as Target captures that.

Dim OldCellValue

Private Sub Worksheet_Change(ByVal Target As Range)

Dim newcellvalue

newcellvalue = Target.Value

MsgBox "Old " & OldCellValue & ", New " & newcellvalue

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

OldCellValue = Target.Value

End Sub

answered Sep 27 '17 at 16:57

SJR

22,986
6
18
26

1

It worked! Propably global variable and Targets made it work because otherwise mine code was the same. Thanks a lot for help. Marking your post as answer for anyone who would need help in this matter. – Zdeněk Bužga Sep 27 '17 at 18:04
Thanks. Yes, I should have mentioned that making it a global variable is the main thing. – SJR Sep 27 '17 at 18:05

score 0 · Answer 2 · answered Sep 27 '17 at 17:07

I think you're on the right track. I would use a global variable to track the current/old value so you can compare it in the Worksheet_Change event.

Something like this:

Private old_value As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    old_value = Target.Text

    'Debug to check the old_value
    'Debug.Print "old_value = " + old_value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Area As Range
    Dim OutlApp As Object
    Dim IsCreated As Boolean
    Dim cell As String
    Dim new_value As String

    Set Area = Range("A1:E20")

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Area) Is Nothing Then
        new_value = Target.Text

        'Debug to compare values
        'Debug.Print "new_value = " + new_value
        'Debug.Print "old_value = " + old_value

        If new_value <> old_value Then
            'Debug to compare
            'Debug.Print "new_value and old_value are different"
        End If
    End If
End Sub

Keep in mind that the Worksheet_SelectionChange event is going to fire every time you navigate between cells. So, if you change the value of a cell and press the Enter key, the value of old_value is going to change because you are resetting the value in the Worksheet_SelectionChange event. You need to perform the comparison and send the email before the selection changes.

Also, you'll probably want to use .Value for the cell instead of .Text. See this post for the differences: What is the difference between .text, .value, and .value2?

Thanks a lot for help. Using global variable was the key to make it work. — Zdeněk Bužga, Sep 27 '17 at 18:05

Private Sub Worksheet_SelectionChange/Worksheet_Change

2 Answers2