VBA Help: Removing Spaces From Left and Right of A Cell On Worksheet_Change

[milwphil]

I'm trying to come up with code and keep the same type of method consistent with the spreadsheet that I inherited.

All I want to accomplish is that if someone types something in a cell and it has spaces on the front end or back end of whatever they typed, the space is removed immediately (after they select another cell)

This is where I thought it was most logical to Trim the spaces, but it's just not happening on Dates etc.

Can anyone tell me what I'm missing or doing wrong?:

For Each oCell In Target
    oCell.Value = Validations.CleanNonStndChars(CStr(oCell.Value))
    oCell.Value = WorksheetFunction.Trim(CStr(oCell.Value))
   Next oCell

THANKS IN ADVANCE!

 

[milwphil]

Ok I got it to trim down- THANK YOU for your patience, but I'm still having problems with deleting data and receiving Debug errors. When I delete/clear the data, I receive:

Run-time Error '-2147417848 (80010108)':
Method 'Value' of object 'Range' failed

At the red line of code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
For Each oCell In Target
    [COLOR=red][B]oCell.Value = WorksheetFunction.Trim(oCell.Value)
[/B][/COLOR]Next oCell
 ..............

Any suggestions? I really appreciate you taking the time with this!

 

[Jonmo1]

You may also want to disable events...

Basically you have an almost never ending loop..

Changing the value of a cell, triggers the Change Event.
Change Event Code then changes the value of target, triggering the Change event again
Change Event Code then changes the value of target, triggering the Change event again
Change Event Code then changes the value of target, triggering the Change event again
Change Event Code then changes the value of target, triggering the Change event again
etc....


At the very beginning of the code, put

Application.EnableEvents = False

Then the rest of your code

Then at the end, reset it to True

Application.EnableEvents = True



Not sure that's the problem, but it should definately help..

 

[milwphil]

WOW! Jonmo1, you are an absolute life saver! I'm humbled beyond words. AWESOME! IT WORKS PERFECTLY!

Thank you so much for following this post and responding so quickly!

Excellent explanations as well!

It is much appreciated!

 

[Jonmo1]

Glad to help, thanks for the nice feedback.