Excel VBA... Quick Add Note Macro

[IGPOD]

Problem: the note section of a flat database is located about 17 columns from the name of the item being looked up. The notes sometimes get placed in the wrong file and to mitigate this I need something that will be as close to fool proof as possible.

I was thinking something simple that would take the selected cell (the item being looked up)(any cell in column C in the picture below) and through the macro a box or form would appear asking you to add a note to the file... the trick is that I do not want to erase any of the notes currently in the notes cell for that particular record; also a date stamp of when the note was entered would be much appreciated as everybody seems to use their own formatting and it gets really annoying.

Example:


Thanks for your help.

[vbfbryce]

In the Worksheet_SelectionChange event I have this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then
        Call Module1.addNote(Target.Row)
    End If
End Sub

In Module1 I have this:

Public noteCurr As String
Public noteNew As String

Sub addNote(myRow As Long)
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    noteCurr = ws.Range("d" & myRow).Value
    ufNote.Show
    If Len(noteNew) > Len(noteCurr) Then
        ws.Range("d" & myRow).Value = noteNew
    End If
End Sub

In my user form (ufNote) I have this:

Private Sub btnAdd_Click()
    Module1.noteNew = Module1.noteCurr & vbCrLf _
        & Format(Now(), "yyyy-MM-dd") & vbCrLf & TextBox1.Text
    TextBox1.Text = ""
    Me.Hide
End Sub

Private Sub btnCancel_Click()
    Textbox1.Text=""
    Me.Hide
End Sub

My notes are in column D.

[IGPOD]

vbfbryce, thank you, I'll put this together in a few and get back to you to let you know if I was successful. Again, you're amazing and I'm learning a lot from you!