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!

 

[kpark91]

Is there really a way to change multiple cells at the same time in Change event?

Try

Target.Value = Trim$(CStr(Target.Value))

 

[CWatts]

Try using a worksheet change function:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Value = WorksheetFunction.Trim(Target.Value)
End Sub

 

[milwphil]

kpark91 and CWatts,

Thanks for the responses! Unfortunately, I haven't been able to get the cells to trim down.

Kpark91, where should I be putting that code? I assume it would replace:

oCell.Value = WorksheetFunction.Trim(CStr(oCell.Value))

CWatts, I don't understand but I'm getting an error message "Ambigiuous Name Detected: Worksheet_Change"

THANKS AGAIN!
Phil

 

[CWatts]

It's not code that you just place anywhere, it is specific to the sheet. If you look in your project explorer you'll see a tre where you'll have your worksheets listed and your modules.

Doubleclick on the worksheet you want that code to apply to.

Up top, in the drop down, change "(General)" to "Worksheet". In the next sheet change "SelectionChange" to "Change"

You'll see it creates Sub templates for each type. Just replace the default with the code I provided and modify it as you would any VBA code.

 

[Jonmo1]

Is there really a way to change multiple cells at the same time in Change event?

Try

Target.Value = Trim$(CStr(Target.Value))

Yes..

Example, if you copied a range...Say A1:E10
And pasted it to G2

Then 50 cells (G2:K11) all just changed at the same time.
And Target is now represents 50 cells (G2:K11), so this line will error
Target.Value = WorksheetFunction.Trim(Target.Value)


That's the purpose for looping through Target..

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

Hope that helps.


Another way to change multiple cells at once is to highlight a range, say A1:A10, type anything, and press CTRL + ENTER

 

[milwphil]

Thanks for the response! It appears anytime I use that code on "Worksheet" "Change" no matter what...the second macro also listed as "Worksheet_Change" is flagged as an error (same "ambigious name" error).

I was able to incorporate the line of code into the other "Worksheet_Change" and it removed the spaces without receiving an error.

Of course, Excel can't be that simple:

The problem I'm having now is that when I delete the data from the cells-(cut or keystroke delete), I get a Runtime Error '13' Type Mismatch at that the line I added. Here's my entire macro:

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=red][B]Target.Value = WorksheetFunction.Trim(Target.Value)[/B][/COLOR]
 If Target.Cells.Count = 1 Then
  Constants.DisableWorkbook
  If Target.Row > 1 Then
   Select Case Target.Column
    Case 1
     Validations.OrderReport Target
   End Select
  End If
  Constants.EnableWorkbook
 ElseIf Target.Cells.Count > 1 Then
  If Operations.CheckUndoStack("Paste") Then
   Constants.DisableWorkbook
   Constants.DisableScreen
 
   Dim oCell As Range
 
   Application.Undo
   Target.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
 
 
   For Each oCell In Target
    oCell.Value = Validations.CleanNonStndChars(CStr(oCell.Value))
   Next oCell
   Constants.EnableScreen
   Constants.EnableWorkbook
  End If
  If Target.Cells.Locked Then
   Target.Locked = False
  End If
 End If
End Sub

 

[Jonmo1]

The problem I'm having now is that when I delete the data from the cells-(cut or keystroke delete), I get a Runtime Error '13' Type Mismatch at that the line I added. Here's my entire macro:

See previous post #6.

When you cut a row, you're changing multiple cells at the same time, so you have to loop through target.

 

[milwphil]

Jonmo1,

Thank you for your response! And please forgive me, but I'm not sure where I should add the loop:

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

I think I may be using it incorrectly because by itself and how I'm adding it-it's not trimming the data.

 

[Jonmo1]

Put it right at the same place you have the red highlighted line..

Just REPLACE this line
Target.Value = WorksheetFunction.Trim(Target.Value)

With

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