Automatically run macro when cell value changes

[Rob161]

Hi,

With regarding the following macro, I need help adding the extra coding required if I wish to have the macro run automatically when the value of a particular cell changes. I'm new to using <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> and am always getting errors when I attempt this.

Thanks in advance
Rob


With theComment
.Visible = False
.Text Text:="My comment" & Chr(10) & ""
With .Shape
.Fill.Transparency = 0#
.Line.Weight = 0.75
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
.Line.Transparency = 0#
.Line.Visible = msoTrue
.Line.ForeColor.RGB = RGB(0, 0, 0)
.Line.BackColor.RGB = RGB(255, 255, 255)
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.BackColor.SchemeColor = 80
On Error Resume Next
.Fill.UserPicture MyPath
If Not Err.Number = 0 Then
MsgBox "You do not have a valid path to the picture file" & Chr(13) & _
"Please try again.", vbOKOnly, "Notification"

theComment.Delete 'remove the incomplete, pictureless comment ??
End If
On Error GoTo 0
End With
End With


End Sub

 

[Snakehips]

Rob161,

Assuming your original code is still Sub 'Add_Comment' and that the 'particular cell' is e.g. B2 then add the below to the sheet's code module.........

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
'Assumes cell B2 is the 'Particular cell'  edit to suit *******
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub '******
Application.EnableEvents = False
Application.ScreenUpdating = False


Call Add_Comment  'Original add_comment sub


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

Hope that helps.