Code to invoke a Macro when the user leaves the active sheet

[aresquare1]

I have a macro to sort a named range that works well but I need to have it run every time when a user leaves the active sheet. That way if they change anything in the named range, when they leave that sheet, it will re-sort that range. The code below is what I have to sort the range.

If anyone can help with my question or can simplify my code below, I would be so grateful.
RR

Please Login or Register  to view this content.

[6StringJazzer]

Your code is not complete; it is just a fragment. What Sub is it in? What module is it in?

when a user leaves the active sheet

When the user leaves any active sheet? Or is there one sheet in particular you are interested in?

If it's one sheet in particular use this code in the VBA module for that sheet:

Please Login or Register  to view this content.

Also your code looks like it is from the macro recorder, so has some bloat. I would put it in the code module for sheet Project Role and rewrite it like this:

Please Login or Register  to view this content.

[aresquare1]

Sorry Jeff for the confusion. As you can tell, I know VERY LITTLE about macros so I REALLY appreciate your help.

The range that I am trying to sort is on a sheet called "Project Role". This range named "Proj_Role" is used as a list for input purposes on other sheets and thus the contents need to be in Alphabetical order. If one of the users inserts a row with a new Role or changes a current Role name in the current list, when they leave the "Project Role" sheet, I want the range to be sorted at that point. I don't know how to answer your questions so I'll include the new version which has what I hope to be the properly inserted revision to my previously recorded code.

Please Login or Register  to view this content.

[6StringJazzer]

All the code you are showing here should be in the VBA module for the worksheet Project Role. The new sub I provided would be in that module and revised to be:

Please Login or Register  to view this content.

Your sort code is hard-coded to sort rows 4-42 so if someone inserts a new row it won't work correctly anymore. If you attach your file here (see yellow banner at the top of the page) I can revise your code to use the name Proj_Role. I can't test code without the file and data.

[aresquare1]

Thanks again for your help and patience. I attached my file.

RR

[6StringJazzer]

See how the data on Sector is a Table? That is what I would do with Project Role, instead of making it a named range. But I didn't change it.

I did change the code to refer to Proj_Role. I moved the Macro3 into the sheet module and renamed it. Sub Macro3 still exists but now calls the new sub, which will allow you to invoke it from the user interface. I removed Module2, it was redundant. I added the Worksheet_Deactivate Sub.

I didn't touch Module1, not sure what you are trying to do there.

On Project Roles I changed (Bottom) to zz(Bottom) so it will always sort to the bottom of the list.

I tested everything and it works.

[aresquare1]

Thanks for all your help. It works as I hoped it would; no surprise to you though.

I'm sure I'll have other questions as this model expands.

RR

[6StringJazzer]

Glad to help! If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.