How to trigger a macro when the last value in a column contains the trigger word

JohnGow383

JohnGow383

Board Regular
Joined
Jul 6, 2021
Messages
141
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I would like to create an event to trigger a macro to display a comment in a particular cell when the lowest value in a range in another column is detected. When the column has another word added below it, to then delete the comment.

ie Trigger column range is M4:M53
Trigger word is "SOP" (I have a few trigger words for different comments in different cells but just stating one to get an idea) SOP has to be the lowest populated cell in the column
This is to add a comment into cell D22
When another word is entered below SOP, to then delete the comment in D22

Any help appreciated ty
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Is it always D22 that gets updated ?
Did you really mean a Comment (now called a Note) or did you just mean putting text into D22 ?

This will put a comment (now a Note) in D22 .
Paste into the module of the relevant sheet.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lastRow As Long
  
    If Not Intersect(Target, Range("M4:M53")) Is Nothing Then
        lastRow = Range("M" & Rows.Count).End(xlUp).Row
        If Target.Row = lastRow Then
            Application.EnableEvents = False
          
            On Error Resume Next
                Range("D22").Comment.Delete
            On Error GoTo 0
  
            Select Case Target.Value     ' Change this to UCase(Target.Value) if you want to ignore upper/lower case sensitivity
          
            Case "SOP"
                Range("D22").AddComment ("Last Row Contains " & Target.Value)
          
            Case Else
                ' Comment already deleted as initialisation step
            End Select
            Application.EnableEvents = True
        End If
    End If

End Sub
 
Upvote 0
Alex Blakenburg said:
Is it always D22 that gets updated ?
Did you really mean a Comment (now called a Note) or did you just mean putting text into D22 ?

This will put a comment (now a Note) in D22 .
Paste into the module of the relevant sheet.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lastRow As Long
 
    If Not Intersect(Target, Range("M4:M53")) Is Nothing Then
        lastRow = Range("M" & Rows.Count).End(xlUp).Row
        If Target.Row = lastRow Then
            Application.EnableEvents = False
         
            On Error Resume Next
                Range("D22").Comment.Delete
            On Error GoTo 0
 
            Select Case Target.Value     ' Change this to UCase(Target.Value) if you want to ignore upper/lower case sensitivity
         
            Case "SOP"
                Range("D22").AddComment ("Last Row Contains " & Target.Value)
         
            Case Else
                ' Comment already deleted as initialisation step
            End Select
            Application.EnableEvents = True
        End If
    End If

End Sub
Click to expand...
Hello Alex,

Thanks for your reply.
Yes, I meant a comment with the red triangle comment indicator. For SOP trigger it will be D22, for ROP trigger, D23. There's also SOP2 and ROP2 for cells D25 and D26.

I tried the code however it didn't work. I do also have another private sub with same name (worksheet_change) so maybe this is causing conflict?
 
Upvote 0
So when I deleted the other private sub worksheet_change it worked perfectly. Thank you. Am I able to have multiple triggers and different macros that don't conflict like this?
 
Upvote 0
They will all be in the same worksheet_change macro or at least initiated by it.
Elaborate on what you want it to do and I will try to help you.
 
Upvote 0
Hi. Thanks Alex.
I already have this code using some of the triggers.
Private Sub Worksheet_Change(ByVal Target As Range)

Dim triggercells As Range, lrow As Integer

Set triggercells = Range("M4:M53")

If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then
lrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "M").End(xlUp).Row
If Cells(lrow, 13).Value = "NOON in PORT" Then
Range("I5,E4:F4,E5:F5").Select
Range("E5").Activate
Selection.ClearContents
End If

If Cells(lrow, 13).Value = "NOON in TRANS" Then
Range("I5,E4:F4,E5:F5").Select
Range("E5").Activate
Selection.ClearContents
End If

If Cells(lrow, 13).Value = "ROP" Then
Range("I5,E4:F4,E5:F5").Select
Range("E5").Activate
Selection.ClearContents
End If

If Cells(lrow, 13).Value = "ROP2" Then
Range("I5,E4:F4,E5:F5").Select
Range("E5").Activate
Selection.ClearContents
End If

Range("j13").Select
End If

End Sub

I would need a comment added to the following cells for the following triggers. Note that only one SOP, ROP, SOP2, ROP2 will ever be in the column range.
SOP - Comment in D22 (deleted when not last value in range)
ROP - Comment in D23 (deleted when not last value in range)
SOP2 - Comment in D25 (as above)
ROP2 - Comment in D26 (as above)

I think the above code is not detecting lowest non blank cell for trigger word, however I guess this could be modified to last too so the trigger could do both actions
 
Upvote 0
Am I missing something or are all of your options in the above code doing exactly the same think ie
VBA Code: 
   Range("I5,E4:F4,E5:F5").Select
   Range("E5").Activate
   Selection.ClearContents
 
Upvote 0
See if this works for you:-

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim triggercells As Range, lrow As Integer
    
    Set triggercells = Range("M4:M53")
    
    If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then
        lrow = Cells(Rows.Count, "M").End(xlUp).Row
        If Target.Row = lrow Then
            Application.EnableEvents = False
            Select Case Target.Value            
                Case "NOON in PORT", "NOON in TRANS", "ROP", "ROP2"
                    Range("I5,E4:F4,E5:F5").ClearContents
                Case Else
                    ' No Action Required
            End Select
                     
            On Error Resume Next
                Range("D22").Comment.Delete
                Range("D23").Comment.Delete
                Range("D25").Comment.Delete
                Range("D26").Comment.Delete
            On Error GoTo 0
    
            Select Case Target.Value
                Case "SOP"
                    Range("D22").AddComment ("Last Row Contains " & Target.Value)
                Case "ROP"
                    Range("D23").AddComment ("Last Row Contains " & Target.Value)
                Case "SO2P"
                    Range("D25").AddComment ("Last Row Contains " & Target.Value)
                Case "ROP2"
                    Range("D26").AddComment ("Last Row Contains " & Target.Value)            
                Case Else
                 ' Comment already deleted as initialisation step
            End Select
                         
            Range("j13").Select
            
            Application.EnableEvents = True
        End If
    End If

End Sub
 
Upvote 0
Alex Blakenburg said:
Am I missing something or are all of your options in the above code doing exactly the same think ie
VBA Code: 
   Range("I5,E4:F4,E5:F5").Select
   Range("E5").Activate
   Selection.ClearContents
Click to expand...
Yes they are, I know, not very elegant. Thanks I will try the new code.
 
Upvote 0
Alex Blakenburg said:
See if this works for you:-

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim triggercells As Range, lrow As Integer
    
    Set triggercells = Range("M4:M53")
    
    If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then
        lrow = Cells(Rows.Count, "M").End(xlUp).Row
        If Target.Row = lrow Then
            Application.EnableEvents = False
            Select Case Target.Value            
                Case "NOON in PORT", "NOON in TRANS", "ROP", "ROP2"
                    Range("I5,E4:F4,E5:F5").ClearContents
                Case Else
                    ' No Action Required
            End Select
                     
            On Error Resume Next
                Range("D22").Comment.Delete
                Range("D23").Comment.Delete
                Range("D25").Comment.Delete
                Range("D26").Comment.Delete
            On Error GoTo 0
    
            Select Case Target.Value
                Case "SOP"
                    Range("D22").AddComment ("Last Row Contains " & Target.Value)
                Case "ROP"
                    Range("D23").AddComment ("Last Row Contains " & Target.Value)
                Case "SO2P"
                    Range("D25").AddComment ("Last Row Contains " & Target.Value)
                Case "ROP2"
                    Range("D26").AddComment ("Last Row Contains " & Target.Value)            
                Case Else
                 ' Comment already deleted as initialisation step
            End Select
                         
            Range("j13").Select
            
            Application.EnableEvents = True
        End If
    End If

End Sub
Click to expand...
Thanks. I have tried this code however it is giving an error. Any ideas?
 

Attachments

  • Capture.JPG
    Capture.JPG
    49.3 KB · Views: 5
Upvote 0
You must log in or register to reply here.

Similar threads

JohnGow383
  • Solved
Help with my Worksheet_change code
Replies
2
Views
220
JohnGow383
JohnGow383
M
  • Question
Workbook Event Macros Triggering in Other Workbooks
Replies
7
Views
878
Anthony P Rose
A
L
  • Solved
Worksheet Change_Event not working on cell value change
Replies
9
Views
107
Lil Stinker
L
W
  • Solved
Moving/transferring data between tables with matching column names, not by column order - vba
Replies
5
Views
271
Alex Blakenburg
A
D
  • Question
Issue with EntireRow.Delete
2
Replies
11
Views
830
RoryA
RoryA

Forum statistics

Threads
1,215,375
Messages
6,124,589
Members
449,174
Latest member
chandan4057

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top