CODEWITHSUNDEEP
excel
Asireb Rimas
Asireb Rimas

Reputation: 3

Alternative VBA for removing spaces in cells?

There is a problem at work that I would like to address in a different manner. The coworkers forget to click a VBA button prior to saving the sheet.

This what I currently have in the sheet:

Sub TrimExample1()
Dim Rng As Range
Set Rng = Selection
For Each Cell In Rng
Cell.Value = Trim(Cell)
Next Cell
End Sub

It would be helpful if you could remove the space or spaces when you leave the cell or when you save the sheet. This way for sure the task is done.

Thank you, Sam

Upvotes: 0

Views: 39

Answers (1)

Skin
Skin

Reputation: 11277

There are two events you should look at capturing on your ThisWorkbook object.

Solution

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

... and ...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Note: Take note of the Target parameter, that's your range of cells that were updated/changed/selected.

Either of those will do what you want but be aware, any changes you make will invalidate the undo stack and that is often not desirable.

Also, you’ll need to make sure you set Application.EnableEvents = False when you update the cell and then back to True once you’re done. Failure to do that will cause you some grief.

If you want to do it before save, then you'll need to work on what exactly needs to be trimmed because your current code will not suffice.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

It's hard to give you a definitive answer for what you should do but those events will solve your problem if implemented correctly.

Upvotes: 1

Related Questions