Results 1 to 8 of 8

Thread: VBA code not working in a Range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code not working in a Range

    Hi Guys,

    I have the following code to add a comment each time a cell value changes, however if I change the Range to "N25" everything works perfectly, but if I want to have the code to cover all rows in column "N" I don't get any comment appear when the value changes.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Range("N:N")
    On Error Resume Next
    If Rng.Comment Is Nothing And Len(Rng.Value) > 0 Then
    Rng.AddComment Now & "-" & Rng.Value
    Else
    Rng.Comment.Text vbNewLine & Now & "-" & Rng.Value, Len(Rng.Comment.Text) + 1
    End If
    Rng.Comment.Shape.TextFrame.AutoSize = True
    End Sub

    Thanks,

    Gavin

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,191
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA code not working in a Range

    Try this.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    
        Set Rng = Range("N:N")
    
        If Intersect(Target, Rng) Is Nothing Then Exit Sub
    
        On Error Resume Next
    
        If Target.Comment Is Nothing And Len(Target.Value) > 0 Then 
            Target.AddComment Now & "-" & Target.Value
        Else
            Target.Comment.Text vbNewLine & Now & "-" & Target.Value, Len(Target.Comment.Text) + 1
        End If
    
        Target.Comment.Shape.TextFrame.AutoSize = True
    
    End Sub
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code not working in a Range

    Hi Norie,

    Still no good.

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code not working in a Range

    Hi Norie,

    I apologize I should have mentioned earlier, I have the following formula in column "N" =INDEX(A$1:M$1,MATCH(TRUE,(T25:AF25<>0)*(COLUMN(T25:AF25)-COLUMN(T25)+1)>=D25,0))

    If I use the code I have and only select one cell in "N", it works, but not when I select all on "N", if I type over the formula using my code I still don't get a comment.

    When I use your code the only time it returns a comment is when I type over the formula.

    is there away I can get the comment by leaving the formula in place.

    Thanks

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,191
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA code not working in a Range

    The change event is only triggered when a manual change is made on the worksheet.
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code not working in a Range

    Excuse my ignorance as I am new to VBA, why does the code work if I only have the range as one cell i.e. "N25" I change the information in cell "D25" which changes the information in "N25" and inserts a comment.
    As soon as I change the range to anything other than a single cell no comment is inserted.

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,191
    Post Thanks / Like
    Mentioned
    63 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA code not working in a Range

    Gavin

    Your original code would be triggered by a manual change in any cell on the worksheet.

    The code itself doesn't actually check, or do anything, with the cell/range that has been changed but it does look at/work with column N.

    Why it only changes N25 I'm not sure, do any other cells in column N have comments?
    If posting code please use code tags.

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code not working in a Range

    Hi Norie,

    No Other comments in N.

    I will work on a different outcome.

    Thanks for your help.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •