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

[JohnGow383]

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

 

[JohnGow383]

U

Thanks. I have tried this code however it is giving an error. Any ideas?

I am no longer getting the debug error message, however, it is clearing the cells in the range but not adding a comment?

 

[JohnGow383]

Also, I've found with the new code, the cells "I5,E4:F4,E5:F5" aren't being cleared. For some reason, it's not detecting the trigger words in the column M

 

[Alex Blakenburg]

1) Since it has errorred out can you in the immediate window paste the below and hit return
Application.EnableEvents = True
when it is turned off the procedure won't run

2) Can you clarify when you want those fields to be cleared.
If it is only when one of those terms are entered onto the last row in column M then that is what the code does and it could be simply that the code is not running, so apply item 1 above

3) The Select Case options are case sensitive if you want that not to be the case then we need to make a couple of changes.
It also requires and exact match so please check that the case matches and that there are no leading or traiiling spaces and the spelling is exactly the same.

 

[JohnGow383]

1.

1) Since it has errorred out can you in the immediate window paste the below and hit return
Application.EnableEvents = True
when it is turned off the procedure won't run

2) Can you clarify when you want those fields to be cleared.
If it is only when one of those terms are entered onto the last row in column M then that is what the code does and it could be simply that the code is not running, so apply item 1 above

3) The Select Case options are case sensitive if you want that not to be the case then we need to make a couple of changes.
It also requires and exact match so please check that the case matches and that there are no leading or traiiling spaces and the spelling is exactly the same.

1. Can you specify exactly where to paste that line? I see it is already at the bottom of the code already.
2. I want the cell range to clear when the trigger is detected. I want to the comments to clear when the last cell in column M isn't the trigger word.
3. The spreadsheet is completed daily. Each day there is a new spreadsheet copied from the previous day and each day there is a new row in column M populated. The trigger words have data validation so there's no way to put the wrong word in or use lower case lettering etc.

 

[Alex Blakenburg]

If you can't see the immediate window enter Ctrl+G and it should pop up.
Paste it into there and hit enter.
Application.EnableEvents = True

The code should
1) Clear the range when
a) the cell being changed is in the last row of M
AND
b) the value entered is one of the Trigger words/phrases in the 1st Select Case statement

2) Clears the comments
a) if the cell being changed is in the last row of M
It clears all the comments at this point
b) it puts 1 comment back if any Trigger words are in the 2nd Select Case statement options

 

[Alex Blakenburg]

I am in Australia an login off soon.
Maybe comment out this line in the code
Application.EnableEvents = False
at least until all the issues are resolved.

You do need to turn back on Events after the last time the code errored out.
Your options are:
Close and reopen the workbook
OR paste Application.EnableEvents = True into the immediate window and hit enter
OR create a little sub in a normal module such as the below and run it.

Sub TurnEventsOn()
Application.EnableEvents = True
end sub

My current thinking is that the code is not running at all since events are still off (False)

If you can't get it working send me as much detail as you can so I can have a look at it tomorrow.

 

[JohnGow383]

If you can't see the immediate window enter Ctrl+G and it should pop up.
Paste it into there and hit enter.
Application.EnableEvents = True

The code should
1) Clear the range when
a) the cell being changed is in the last row of M
AND
b) the value entered is one of the Trigger words/phrases in the 1st Select Case statement

2) Clears the comments
a) if the cell being changed is in the last row of M
It clears all the comments at this point
b) it puts 1 comment back if any Trigger words are in the 2nd Select Case statement options

Hi Alex,
Thanks. I did not know about the immediate window. So I have done that. The macro tries to run but I get the same error as before. For some reason it is not executing. Would it be easier if I emailed you the spreadsheet? You can email me on gpo@gmx.co.uk and I will reply with it. Thanks.

 

[JohnGow383]

I am in Australia an login off soon.
Maybe comment out this line in the code
Application.EnableEvents = False
at least until all the issues are resolved.

You do need to turn back on Events after the last time the code errored out.
Your options are:
Close and reopen the workbook
OR paste Application.EnableEvents = True into the immediate window and hit enter
OR create a little sub in a normal module such as the below and run it.

Sub TurnEventsOn()
Application.EnableEvents = True
end sub

My current thinking is that the code is not running at all since events are still off (False)

If you can't get it working send me as much detail as you can so I can have a look at it tomorrow.

Yeah i can't get it working. It is enabled. No worries, I will try more tonight. Thanks very much for the effort put in thus far, very much appreciated.

 

[JohnGow383]

I am in Australia an login off soon.
Maybe comment out this line in the code
Application.EnableEvents = False
at least until all the issues are resolved.

You do need to turn back on Events after the last time the code errored out.
Your options are:
Close and reopen the workbook
OR paste Application.EnableEvents = True into the immediate window and hit enter
OR create a little sub in a normal module such as the below and run it.

Sub TurnEventsOn()
Application.EnableEvents = True
end sub

My current thinking is that the code is not running at all since events are still off (False)

If you can't get it working send me as much detail as you can so I can have a look at it tomorrow.

Yeah i can't get it working. It is enabled. No worries, I will try more tonight. Thanks very much for the effort put in thus far, very much appreciated.

I am in Australia an login off soon.
Maybe comment out this line in the code
Application.EnableEvents = False
at least until all the issues are resolved.

You do need to turn back on Events after the last time the code errored out.
Your options are:
Close and reopen the workbook
OR paste Application.EnableEvents = True into the immediate window and hit enter
OR create a little sub in a normal module such as the below and run it.

Sub TurnEventsOn()
Application.EnableEvents = True
end sub

My current thinking is that the code is not running at all since events are still off (False)

If you can't get it working send me as much detail as you can so I can have a look at it tomorrow.

Hi Alex. Just to let you know that the code works perfectly. Many thanks for your help.
The reason the code was breaking and disabling events, was because the worksheet was protected doh. Whilst the cells were editable, adding / removing comments is not, so after enabling Edit Objects, the code works perfectly.
Many thanks once again. The code is very elegant.

 

[JohnGow383]

I know this is being pedantic. But is there an easy way to have part of the comment text formatted in bold. The comment text is as follows:
"SOP M/T Counter. Please add the SOP Counter here as well as cell C4"
"ROP M/T Counter. Please add the ROP Counter here as well as cell C4 "
"SOP2 M/T Counter. Please add the SOP2 Counter here as well as cell C4 "
"ROP2 M/T Counter. Please add the ROP2 Counter here as well as cell C4"

So the idea would be to have the first sentance in bold lettering. If it's too much hassle please don't worry and thanks for everything so far.