Remove Spaces after update

[Emoncada]

I have about 30 txtboxes that I would like to make sure user doesn't use spaces Is it possible to have a function that afterupdate have all spaces
" " removed. So if this is entered like this

DPT20081229 -EM3829

It would become
DPT20081229-EM3829

And Also if a space is entered at the begining.

[mikerickson]

This uses the BeforeUpdate event.
The routine also works from my (Excel 2004) AfterUpdate event, but focus acts oddly in Excel 2004. Your machine may differ. The Before Update is safer.
If the ActiveControl is still the TextBox in the AfterUpdate event for your computer, it should also work from that event.


Sub NoSpace()
With ActiveControl
.Text = Application.Substitute(.Text, " ", vbNullString)
End With
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Call NoSpace
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Call NoSpace
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Call NoSpace
End Sub

[Bob Phillips]

mikerickson,

Why use an Excel function when there is a perfectly good VBA one



With ActiveControl
.Text = Replace(.Text, " ", vbNullString)
End With


Emoncada,

do you want no spaces allowed? If so, trap the input



Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Chr(KeyAscii) = " " Then
KeyAscii = 0
End If
End Sub

[mikerickson]

Not all perfectly good VB functions are supported on Mac. :(