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
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