View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Charlie Charlie is offline
external usenet poster
 
Posts: 703
Default Spaces in Textbox

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


"NickHK" wrote:

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

NickHK

"Duncan" wrote in message
oups.com...
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 wrote:

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

NickHK

"Duncan" wrote in message
ups.com...
ummm......please explain Nick?

Sorry but I dont really understand your answer.

Duncan


NickHK wrote:
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" wrote in message
oups.com...
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