JohnGow383
Board Regular
- Joined
- Jul 6, 2021
- Messages
- 141
- Office Version
- 2013
- Platform
- Windows
Hi. I need help with to expand my worksheet_change code.
I have multiple worksheet_change triggers and actions but just can't figure out how to do the following.
I have a range ("M4:M53") which is already detecting certain case words that are the lowest non-empty cell in the range and triggering various macros which including talking and adding and removing comments. These work all fine. I need these case words to now be a condition which when met will fire a macro to copy and paste the contents of cell C4 to another cell when C4 has been changed. I already have the child macros prepared and ready to be called. The case words are "SOP", "ROP", "SOP2" and "ROP2". These are already being used as triggers. The trigger cell is C4. The action is copy C4 into another cell (Macro ready for this).
So for example with "SOP":
When "SOP" matches the last non-empty cell in range.("M4:M53") and when a number is entered into C4 to then fire another macro called CopyMTCtratSOP. The macro CopyMTCtratSOP should fire again if Cell C4 is altered (for example if they have entered a wrong number in). It should then never fire again once SOP is no longer the lowest non empty cell in the range. I have data validation in the cells of M4:M53 so only these cases can be selected. There should only be one "SOP", "ROP", "SOP2" and "ROP2" selected in the entire range and I haven't yet found a way to stop a 2nd SOP being selected by mistake (which would screw it up) but that's another problem for another day. Here is my current code, I think it can be added quite easily. Lots of it you can ignore. I was able to do the above with FAOP as this is always in M3 and I did it by testing whether the cell M4 is empty to fire similar macro.
Thanks
I have multiple worksheet_change triggers and actions but just can't figure out how to do the following.
I have a range ("M4:M53") which is already detecting certain case words that are the lowest non-empty cell in the range and triggering various macros which including talking and adding and removing comments. These work all fine. I need these case words to now be a condition which when met will fire a macro to copy and paste the contents of cell C4 to another cell when C4 has been changed. I already have the child macros prepared and ready to be called. The case words are "SOP", "ROP", "SOP2" and "ROP2". These are already being used as triggers. The trigger cell is C4. The action is copy C4 into another cell (Macro ready for this).
So for example with "SOP":
When "SOP" matches the last non-empty cell in range.("M4:M53") and when a number is entered into C4 to then fire another macro called CopyMTCtratSOP. The macro CopyMTCtratSOP should fire again if Cell C4 is altered (for example if they have entered a wrong number in). It should then never fire again once SOP is no longer the lowest non empty cell in the range. I have data validation in the cells of M4:M53 so only these cases can be selected. There should only be one "SOP", "ROP", "SOP2" and "ROP2" selected in the entire range and I haven't yet found a way to stop a 2nd SOP being selected by mistake (which would screw it up) but that's another problem for another day. Here is my current code, I think it can be added quite easily. Lots of it you can ignore. I was able to do the above with FAOP as this is always in M3 and I did it by testing whether the cell M4 is empty to fire similar macro.
Thanks