Run Macro on exit

Hi,

I am using the following Macro to TRIM text in an Information Sheet. The text must be without leading spaces since formulas in other worksheet would not work properly. This is just a safeguard in case entry is sloppy.

I would like to activate the Macro upon leaving the sheet. I tried "deactivate" but that causes a lengthy calculation. Is there a way to avoid the lengthy calculation? The text that needs to be trimmed is in B6:C22 and B24:G36.

Please Login or Register  to view this content.

Thanks in advance for any help!

Johnmus,

How about instead of a worksheet deactivate or activate event, you update it as the cell contents change in the desired areas with a worksheet change event, like so:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Tigeravatar,

Thanks for your help. This is an excellent idea. However, I can't seem to get it to work except for numbers. The macro trims spaces with numbers but not text. I did format cells to General.

Johnmus

Originally Posted by Johnmus

Tigeravatar,

Thanks for your help. This is an excellent idea. However, I can't seem to get it to work except for numbers. The macro trims spaces with numbers but not text. I did format cells to General.

Johnmus

Johnmus,

Do you mean something like
' 123 123 ' -> 123 123
' abc abc ' -> abc abc

or
' 123 123 ' -> 123123
' abc abc ' -> abcabc

Where it is supposed to preserve (1 space) or eliminate all spaces in the middle? Because it should eliminate spaces at the beginning and leave 1 space between words regardless of whether the cell contains numbers, letters, or any combination of them. Also, you could attach an example so I can get a better idea of what you're looking for.

tigeravatar,

After I saved the workbook and opened the sheet it works perfectly! I made some adjustments to the Ranges and tried the adjustments without saving.

Thanks for your help. This Forum is great! I will mark this SOLVED.

John