VBA Macro event triggered MsgBox for multiple rows

[Poppashango]

I received some awesome help by a new found friend on another form..but I am still in need of some help. Maybe someone here can help me. So thank you in advance!

I am currently running a code which opens a message box when certain events happen based on user input. This current code works on one line of data. Is there anyway I can expand this to work on 30 lines of data individually? In other words, the event is triggered when the criteria is met on line J10:T10, but has no bearing on the input on the next line, J11:T11, which should trigger the same msgbox if the criteria is met on that line.

Please Login or Register  to view this content.

Thank you!

[DonkeyOte]

First, modify rngInterest to be the range against which the code should be applied.

If the point was not made already you should modify the Interest is Nothing test to use rngInterest rather than explicitly reference the range details again
(ie rngInterest should be that value - means you need only update one value if you opt to alter the range)

Second, if you wish for multiple rows to be updated simultaneously you will need to introduce some iteration into proceedings.... if you wish to prevent that you will need to add some further checks and indeed an "undo" into the code. Which is your preference ?

[Poppashango]

DKO! Nice to see you here as well! I was having issues with the other site last night,timing out and such..possibly my own connection so I came over here for help. I changed the Intersect to nothing from Range to rngInterest as you suggested. Now I am still at the mercy of the changes being made to this worksheet. I am not sure what you are asking in the second question at this point so I am going to give it a try.

I do want all of the rows to be active, but only as individual test. I changed the Range to include all of the rows and columns, but the test within the macro is now checking every row to each other. In other words, If I placed a code 41 in row 10 and then placed a code 25 in row 17 the message box will trigger. This should not happen. The msgbox should only trigger if the 41 and 25 are on the same row. I am not sure if it is even possible to do this. Here is the code below now with the whole range on what I have declared there are to be no more changes to the worksheet with the Big desk..LOL.

Please Login or Register  to view this content.

DonKO, thank you again for all of your help.

[DonkeyOte]

The point I was trying to make (I think) was that in these instances there is nothing to prevent a user from updating multiple cells simultaneously, eg:

On a blank sheet select A2:C12, type in 2 and whilst holding CTRL press ENTER

You will find that 2 has been added to all cells in the range in one go.
It follows the same can be done with validation cells (so long as the entry value is valid)

So the key in these instances when using a Change event like this is whether you want to permit the mass entry or not.

If mass entry is "ok" then you need to introduce some iteration into the code such that each row of the range of interest is processed / validated on an individual basis

If mass entry is "not ok" then you need to add an Undo into proceedings so as to reverse the mass entry action.

The choice as they say is yours... below are the two alternatives...

Mass Entry - permitted:

Please Login or Register  to view this content.

Mass Entry - not permitted:

Please Login or Register  to view this content.

[Poppashango]

Hey DKO! I used the Mass entry not permitted code, and it works great! Thanks again for all of your help. I am so grateful for you taking the time to help me out. Thanks again!