Force comment based on cell's value

[Ricktaxx]

I have a grid of numbers that is used for tracking, and it is being looked
at in many ways.

One last function I have been trying to add is a way to force a comment onto
a cell who's value is less then a set value. For our sake lets just say less
then 1.

Is there a way to have the cell value tested and start a macro if less then
one?

Thanks

 

[davesexcel]

something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range("A1") Then
If Target.Value < 1 Then
MsgBox ("Less then one") 'your code goes here
End If
End If

End Sub
this code goes in a worksheet module

 

[Brian Hanlin]

Yes, that did help in starting me on my way.. but not being a true expert
yet, I'm a bit lost as to how this is working.
If I enter a 0, no reaction. -1 gives the pop up. But if I enter text, Debug
error.

Is there a way to only watch or confirm changes in an area of the worksheet?
Can I avoid the error when entering text?

Again, Thanks

 

[Dave Peterson]

Do you really mean you want to see a comment (insert|Comment) in that cell?

Maybe something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myComment As String

With Target
If .Cells.Count > 1 Then
Exit Sub
End If
If Intersect(.Cells, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

If IsNumeric(.Value) Then
If .Value < 1 Then

If .Comment Is Nothing Then
'nothing to delete
Else
.Comment.Delete
End If

'if it's a standard comment
'.AddComment Text:="Standard comment here"

'if it's up to the user
myComment = InputBox(Prompt:="What's the comment")
If Trim(myComment) = "" Then
myComment = "No Response by:"
End If
.AddComment Text:=myComment & vbLf & Application.UserName _
& vbLf & Now

End If
End If
End With
End Sub

 

[Brian Hanlin]

Ok, I have come up with this Test code that worked...

Is there any way to tighten it?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ChgT As String, myComment As String, _
failMsg As String, Response, Msg, Style, Title

With Target
If .Cells.Count > 1 Or .Row < 3 Or .Row > 10 _
Or .Column < 4 Or .Column > 12 Then Exit Sub
ChgT = .Cells
If Len(ChgT) < 1 Or Len(ChgT) > 3 Then Exit Sub
If IsNumeric(.Value) Then
If .Comment Is Nothing Then
Exit Sub
Else
Msg = "Do you wish to remove" & vbLf & "the old failure
comment?" ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton1 ' Define
buttons.
Title = "Changing Scores" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then .Comment.Delete
Exit Sub
End If
End If
failMsg = "None"
If Right(ChgT, 1) = "c" Or Right(ChgT, 1) = "C" Then failMsg =
"Critical"
If Right(ChgT, 1) = "f" Or Right(ChgT, 1) = "F" Then failMsg = "Fatal"
If failMsg = "None" Then Exit Sub
myComment = InputBox(Prompt:="What was the " & failMsg & " failure?" _
& vbLf & "Please enter only one.", Title:="Enter Failure")
If Trim(myComment) = "" Then
myComment = "No failure recorded"
End If
Target.AddComment Text:=myComment

End With
End Sub