Excel/VBA - An Event that will prompt for comments

Ask a question



Issue


I am trying to create an event within multiple cells that would ask for a comment if certain conditions are met.
For instance:
  • If c1 < 3.2 then [run event]
  • I want the event to take the forms a comment popup that would save to a comment on the cell.

Solution


Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyMessage As String
If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
If Range("C1") < 3.2 Then
MyMessage = InputBox("Please input your comment")
With Range("C1")
    .ClearComments
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:=MyMessage
End With
End If
End Sub


Thanks to TrowaD for this tip.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team