Prompt for Comments

[Cubical]

Hi.

I was wondering if there was a method of prompting for someone to add a comment, when a cell is changed, and copying that comment into another sheet. Of course in that process saving a history of all the comments.

Thanks =)!

Edit: Just wanted to note when I say a cell is change, I mean changes anywhere. Thanks again!

[Cubical]

Well I discovered these two codes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
Static AncAdress As String, AncCell As Variant 
    If AncAdress <> "" Then 'for first initialization. 
        If AncCell <> Range(AncAdress) Then 
            'The cell that you just left has been changed.  
            'Put action to be taken. 
            Stop 
        End If 
    End If 
    AncAdress = Target.Address 
    AncCell = Target.Value2 
End Sub

    Dim commrange As Range
    Dim mycell As Range
    Dim curwks As Worksheet
    Dim newwks As Worksheet
    Dim i As Long

    Set curwks = ActiveSheet

    On Error Resume Next
    Set commrange = curwks.Cells _
        .SpecialCells(xlCellTypeComments)
    On Error GoTo 0

    If commrange Is Nothing Then
       MsgBox "no comments found"
       Exit Sub
    End If

    Set newwks = Worksheets.Add

     newwks.Range("A1:D1").Value = _
         Array("Address", "Name", "Value", "Comment")

    i = 1
    For Each mycell In commrange
       With newwks
         i = i + 1
         On Error Resume Next
         .Cells(i, 1).Value = mycell.Address
         .Cells(i, 2).Value = mycell.Name.Name
         .Cells(i, 3).Value = mycell.Value
         .Cells(i, 4).Value = mycell.Comment.Text
       End With
    Next mycell

    Application.ScreenUpdating = True

End Sub

One detecting any changes to cells and one copying the comments into a sheet. But I still need to figure out how to prompt the user to add a comment. Any ideas? Thanks.

[nimrod]

DEMO : How to get input from user while running macro...

Public Sub GetInfoFromUserDemo()
   Dim PromptMsg As String
   Dim response As Variant
   
   PromptMsg = "Please enter comment here "
   
   response = Application.InputBox(msg, "Add Comment", "")
   
   If Not response = False Then
      msg = "your comment was ... " & vbNewLine & response
      pt = MsgBox(msg, vbInformation, "Demo")
   Else
      pt = MsgBox("User clicked Cancel", vbCritical, "Process Aborted")
   End If
   
End Sub