| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces in Textbox
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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 Charlie wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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 wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Spaces in Textbox
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.) "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
| HELP! I Lost The Ability To Advance From TextBox To TextBox With the ENTER Or The TAB Keys | Excel Programming | |||
| Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
| Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
| limit textbox to numbers or spaces? | Excel Programming | |||
![Duncan[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
