Spaces in Textbox

[Duncan]

Hi All,

I pose what may be a tricky question,

I want to error when textboxes have just spaces in, I can disallow
spaces with code I found on here by Darrin Henshaw but here is the
tricky part,

What I want in the textbox may have two parts, like first name then
surname with a space inbetween. (Fname Lname) or two spaces (Some
Company Ltd).

With Darrins solution (which I will post below) he has managed to stop
ALL spaces from being entered in the textbox, But I want to allow
spaces IF there is something in the textbox that isnt a space (as
above)......

or.....if you could envisage a validation that flagged up just spaces
and disallowed this (all my validation is on my 'submit' button) then
this might be easier, I dont know how to check for this

Any help would be much appreciated

(I will post what I found on here that im trying to adapt below)

Duncan

Private Sub EName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
With Me.EName
Select Case True
Case (KeyCode >= 97 And KeyCode < 122)
'exit quietly
Case (KeyCode >= 48 And KeyCode < 57)
'exit quietly
Case (KeyCode >= 65 And KeyCode < 90)
'exit quietly
Case KeyCode = 9 'tab
Case KeyCode = 8 'backspace
If Len(.Text) > 0 Then
.Text = Left(.Text, Len(.Text) - 1)
End If
KeyCode = 0
Case Else
KeyCode = 0
Beep
End Select
End With
End Sub

 

[NickHK]

Why not just give the user a text box for each separate piece of information
they need to enter ?
More simple for them and you.

NickHK

 

[Duncan]

ummm......please explain Nick?

Sorry but I dont really understand your answer.

Duncan

 

[NickHK]

Give the user 2 text boxes; one for First name, one for surname.
Then is much clearer what is expected.

NickHK

 

[Duncan]

ah, I see what you mean now, but these boxes may contain up to 4 words.

Such as: "A1 Technology Services Ltd"

and I dont really want 4 boxes on my form....

Duncan

 

[NickHK]

Duncan,
Validate after the input.
You can LTrim/RTrim and Replace multiple space with a single.

NickHK

 

[Charlie]

Replacing multiple spaces (e.g. two spaces with one) may still leave behind
two contiguous spaces (e.g. if there are three spaces together). Try using
"ReplaceAll"

CompressedString = ReplaceAll(OrigStr, " ", " ")

or

CompressedString = Trim(ReplaceAll(OrigStr, " ", " "))

to trim the ends too.

Public Function ReplaceAll(txt As String, FindAll As String, ReplaceWith As
String) As String
'
' the Replace function only makes one pass and the resulting replacement
may produce
' another substring requiring replacement - ReplaceAll will loop until all
substrings
' have been replaced
'
ReplaceAll = txt
If FindAll <> "" And FindAll <> ReplaceWith Then
Do While InStr(ReplaceAll, FindAll) > 0
ReplaceAll = Replace(ReplaceAll, FindAll, ReplaceWith)
Loop
End If
'
End Function

 

[Duncan]

Charlie:

Thank you very much, Exactly what I was after. (something that I cannot
understand enough to create myself, but know enough to use!)

Duncan

 

[Dave Peterson]

Or

CompressedString = application.trim(OrigStr)

The worksheet function: =trim(a1)
will eliminate the duplicated embedded spaces.

It works differently than VBA's Trim function.

 

[Charlie]

Well I'll be a #^%&!! I didn't know that. (Most of my functions are from my
VB5-6 projects, before doing much in Excel.)