Insert Comment in cell based on cell value

[sharley]

Have a 3 shift production spread sheet, need a comment box to pop up if the cell value is less than shift goal.

So cell A1 has the shift goal and it should be 500, in cell d1 I enter 400, need a comment box to open for the reason the production was low, not that good with VBA is there another way to do this?

 

[Hui]

Sharley

Firstly, Welcome to the Chandoo.org Forums

You can use this small piece of VBA to assist you

Open you file
Goto VBA (Alt+F11)
Find the worksheet you want and double click on it
Copy and paste this code into the right hand code pane
Return to Excel (Alt+F11)
Save the file as a *.xlsm file type

Adjust the target and Range inside the code to suit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myComment As String
Dim myUserName As String
Dim myValue As Double

myUserName = "My name: "
myValue = 500 'Set Target here

If Intersect(Target, Range("A1:D10")) Is Nothing Then Exit Sub 'Change range to suit
If Target.Value < myValue Then
  myComment = InputBox("Please enter production shortfall reason", "Production shortfall")
  Target.AddComment.Text Text:=myUserName & Chr(10) & myComment & Chr(10) & ""
Else
  Target.ClearComments
End If
Target.Select

End Sub