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

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

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

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

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

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