VBA Updating Comments to Active Cell

[gswann]

Hi Everyone,

I'm looking to try and solve a problem with a calendar that's been created by the business. It's a crude way of our board being able to know when meetings are taking place throughout the year, it's built in excel rather than just using outlook but it's what they've decided.

There's a tab per month with 5 rows per day within that month. Each row has a validated list of meeting names, what i'm trying to do is get VBA add a comment to the cell once it's been updated with a meeting type.

The problem I've been experiencing is that when adding the comments it's also overwriting any existing comments as well or adding comments to existing meetings that have been added that don't have a comment.

What i'm looking to put together is almost like a check list, so once the cell becomes active, if the cell is blank, do nothing, once active and it's not blank and doesn't have a comment, do nothing, but once active and the user has just entered something from the drop down menu add the comment of application.username and the date and time.

He's what I've cobbled together so far.

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell = "" Then
Exit Sub
End If



If Not IsEmpty(ActiveCell) Then
Else
With ActiveCell
.AddComment
.Comment.Text Text:=Application.UserName & Chr(10) & "Updated " & Now()
.Comment.Shape.TextFrame.AutoSize = True
End With
Exit Sub
End If

End Sub

Any help on this is greatly appreciated.

Thanks
Gary

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    With Target
        If .Comment Is Nothing Then
            .AddComment
            .Comment.Text Text:=Application.UserName & Chr(10) & "Updated " & Now()
            .Comment.Shape.TextFrame.AutoSize = True
        End If
    End With
End Sub

 

[gswann]

DanteAmor that's perfect, thank you so much, I really appreciate it.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.