So I have some code that comments on cells based on information beside of it and it is working almost flawlessly. However, I am having an issue where it is working on all sheets in the entire workbook instead of just sheet 3 like it is supposed to. I have this code set up on sheet 3 only in the VBA projects. So my question is how would I make this work only on one single sheet? Here is my code

Private Sub Worksheet_Calculate()

ActiveSheet.UsedRange.ClearComments

Dim targetRng As Range, commentSrcRng As Range
Dim strPrefix As String 'string Prefix

Set targetRng = Application.Range("D16,D18,D20,D22,D24,D26,D28,D30,D32,D34,D36,D38")
Set commentSrcRng = targetRng.Offset(0, 1)

Dim cel As Range
Dim i As Long
i = 1
    For Each cel In targetRng
        If cel <> "" Then
            cel.AddComment
                cel.Comment.Visible = False
                cel.Comment.Shape.TextFrame.AutoSize = True
                cel.Comment.Text strPrefix & commentSrcRng.Cells(i)
        End If
            i = i   2
    Next

CodePudding user response：

However, I am having an issue where it is working on all sheets in the entire workbook

That's because you've coded it to work on the ActiveSheet

To refer to the sheet the event is coded in, use Me

Private Sub Worksheet_Calculate()
    Me.UsedRange.ClearComments

    Dim targetRng As Range, commentSrcRng As Range
    Dim strPrefix As String 'string Prefix

    Set targetRng = Me.Range("D16,D18,D20,D22,D24,D26,D28,D30,D32,D34,D36,D38")
    Set commentSrcRng = targetRng.Offset(0, 1)

    Dim cel As Range
    Dim i As Long
    i = 1
    For Each cel In targetRng
        If cel <> "" Then
            cel.AddComment
            cel.Comment.Visible = False          cel.Comment.Shape.TextFrame.AutoSize = True
            cel.Comment.Text strPrefix & commentSrcRng.Cells(i)
        End If
        i = i   2
    Next
End Sub