Create Comment With Input Box

[jski21]

Trying to get an input box to pop up so a user can enter abrief comment that will then appear in a comment attached to the cell. Theproblem I believe I’m running into is the user inputs data into a table on onesheet which feeds results over to another sheet; Sheet X. It is the results in SheetX’s range where I’d like the comments to reside. I think I need to specifySheet X in the range below to get this to work?


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'`````````````````````````````````````````````````````'
'Create pop up input box and add comment if"<=98.9%" result'
Dim sReason AsString


If NotIntersect(Target,Range("$B$26:$B$28,$B$30:$B$32,$B$34:$B$36,$B$38:$B$40,$E$26:$E$28,$E$30:$E$32,"& _
"$E$34:$E$36,$E$38:$E$40,$J$26:$J$28,$J$30:$J$32,$J$34:$J$36,$J$38:$J$40,"& _
"$M$26:$M$28,$M$30:$M$32,$M$34:$M$36,$M$38:$M$40")) Is NothingThen
IfTarget.Value > 0 <= 0.989 Then
sReason =Application.InputBox("Please provide a brief comment on why KPI wasmissed", Type:=2)
Target.AddComment
Target.Comment.Visible = False
Target.Comment.Text Text:=sReason
End If
End If

Application.ScreenUpdating = True

End Sub



Thanks


jski

 

[rlv01]

This will clean things up a bit and make the range more specific to the worksheet, but I'm still unclear where "sheetX' comes in to play.

Private Sub Worksheet_Change(ByVal Target As Range)

'`````````````````````````````````````````````````````'
'Create pop up input box and add comment if"<=98.9%" result'
    Dim sReason As String
    Dim C As Comment

    If Not Intersect(Target, Me.Range("$B$26:$B$28,$B$30:$B$32,$B$34:$B$36,$B$38:$B$40,$E$26:$E$28,$E$30:$E$32," & _
                                      "$E$34:$E$36,$E$38:$E$40,$J$26:$J$28,$J$30:$J$32,$J$34:$J$36,$J$38:$J$40," & _
                                      "$M$26:$M$28,$M$30:$M$32,$M$34:$M$36,$M$38:$M$40")) Is Nothing Then

        Set C = Target.Comment
        If Not C Is Nothing Then
            C.Delete                                  'delete any existing comment
        End If

        If Target.Value > 0 And Target.Value <= 0.989 Then
            Do
                sReason = Application.InputBox("Please provide a brief comment on why KPI was missed", _
                                               Title:="Comment for cell " & Target.Address & " (in " & Me.Name & ")", Type:=2)
            Loop Until sReason <> "False"
            Target.AddComment
            Target.Comment.Visible = False
            Target.Comment.Text Text:=sReason
        End If
    End If

    Application.ScreenUpdating = True
End Sub

 

[jski21]

Thanks rlv01. This is helpful. Sheet X is another worksheet that will not be active because the user will be entering data in a table in another sheet (Data X). I'm thinking ideally the user enters the data in Dat X which feed over to Sheet X. Then when Sheet X is active, if any of the cells in the range are less than 98.9% an input box will pop up while on Sheet X asking for user info that will then create a comment in the effected cell(s) in Sheet X.

Not even sure if that is possible. Maybe there is a better way?

 

[mikerickson]

So the sequence is:

User enters data in Data X
formulas calculate cells in Sheet X
If the result of that calculation (on Sheet X) is <.989 then get user input from inputbox and put that input as a comment in the cell in Sheet X.

Is that correct?

 

[mikerickson]

I think this will do what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oneCell As Range, twoCell As Range
    Dim i As Long
    Dim memRng As Range
    
    If not application.intersect(range("A1"),target) is nothing  Then :rem alter
        Set memRng = Selection
        Application.ScreenUpdating = False
        
        With Target.Cells(1, 1)
            .ShowDependents
            i = 1
            On Error Resume Next
            Do
                Set oneCell = .NavigateArrow(False, 1, i)
                If oneCell.Parent.Name = "SheetX" Then
                    Exit Do
                ElseIf oneCell.Parent.Name = .Parent.Name Then
                    Set oneCell = Nothing
                    Exit Do
                End If
                i = i + 1
            Loop Until Err
            On Error GoTo 0
            .Parent.ClearArrows
        End With
        If Not oneCell Is Nothing Then
            If oneCell.Value < 0.989 Then
                oneCell.NoteText Text:=Application.InputBox("explaination", Default:=oneCell.NoteText, Type:=2)
            End If
        End If
        Application.Goto memRng
    End If
End Sub

 

[rlv01]

Then when Sheet X is active, if any of the cells in the range are less than 98.9% an input box will pop up while on Sheet X asking for user info that will then create a comment in the effected cell(s) in Sheet

From a user standpoint, it is not a good idea to let the users add a bunch of data to sheet "Data X", and then when "Sheet X" becomes active force them to deal with 30 inputboxes popping up one after the other, each asking for a comment. You want to have the input box pop up as they enter data for each cell in sheet Data X.

For that, a relationship is needed between the cells in Data X and the cells in Sheet X. So for example, if $B$26 on sheet "Data X" is changed, then there needs to be some way to know which cell on "Sheet X" requires a comment to be added to it.

1. How is any cell on sheet "Data X" where the user enters data, matched up to a cell on sheet "Sheet X" where you want the comment to be created?

2. Where does the value for the value test ( if Value <= 0.989, etc.) come from? The user entered value on sheet "Data X"? Or the formula result on sheet "Sheet X"?

 

[jski21]

Yes, that is correct.

 

[jski21]

Yes, I get it. The KPIs seldom reach the .989 threshold but we still track. The cell on Sheet X simply refers to the result on Data X. The value result is on Data X. Sheet X refers to this result. Sheet X is essentially a mgmt. report. Data X is the raw data with calculated results that fee into Sheet X.

 

[rlv01]

Yes, that is correct.

I'm unclear what you mean by that. Still looking for these answers:

1. How is any cell on sheet "Data X" where the user enters data, matched up to a cell on sheet "Sheet X" where you want the comment to be created?

2. Where does the value for the value test ( if Value <= 0.989, etc.) come from? The user entered value on sheet "Data X"? Or the formula result on sheet "Sheet X"?

 

[jski21]

rlvo1,

1. The cells on Data X are matched up to a cell to Sheet X by a reference formula. For example, Cell A1 on Data X calculates the value. Cell A1 on Sheet X references Cell A1 on Data X by using ='Data X'!A1.

2. The value test occurs through conditional formatting on Sheet X using =AND(A1>0,A1<=0.989). If the value test is met the cell is highlighted red.

Does that help?