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.

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect
Dim cell As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
cell = WorksheetFunction.TRIM(cell)
Next cell
ActiveSheet.Protect

End Sub

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:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim UsedRange1 As Range:    Set UsedRange1 = Range("B6:C22")
    Dim UsedRange2 As Range:    Set UsedRange2 = Range("B24:G36")
    
    If Not Intersect(Target, UsedRange1) Is Nothing Or Not Intersect(Target, UsedRange2) Is Nothing Then
        Target = WorksheetFunction.Trim(Target)
    End If
    
End Sub

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