conditional removal of last chr of string?

[jasonsweeney]

I am trying to remove punctuation marks from a string entered in a tex
box. The code below is triggered whenever the user presses the spac
bar while typing words in a text box.

I only want the last sub-string removed from the word if the las
sub-string is a punctuation mark. As you will note, I have identifie
those punctuation marks that I want to remove below.

But my code does not work....the IF statement is ignored and Exce
removes the last sub-string of ANY string entered into the text box.
Why is this?

Enter into Userform1 with one textbox named textbox1
_________________________________
Private Sub textbox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger
ByVal Shift As Integer)

Dim str() As String
Dim i As Long
Dim lastchrnum As String
Dim lastchr As String
Dim savelastchr As String
'
'
'
If KeyCode = Asc(" ") Then
str = Split(TextBox1.Text, " ")
On Error Resume Next
For i = LBound(str) To UBound(str)
'
' IF last character of string =
' "," = chr(44)
' "." = chr(46)
' "!" = chr(33)
' "?" = chr(63)
' ")" = chr(41)
' "]" = chr(93)
' ">" = chr(62)
' "-" = chr(45)
' "%" = chr(37)
' ":" = chr(58)
' ";" = chr(59)
' """ = chr(34)
'
' Then remove it from string
'
' TAKE OFF PUNCTUATION MARKS
lastchrnum = Len(str(i))
MsgBox "there are this many sub-strings in the string: ["
lastchrnum & "]"
lastchr = Mid(str(i), lastchrnum, 1)
MsgBox "this is last character of the string: [" & lastchr
"]"
MsgBox "The last chr should only be removed if it is one of th
following" & vbNewLine _
& " " & Chr(44) & Chr(46) & Chr(33) & Chr(63) & Chr(41)
Chr(93) & Chr(62) _
& Chr(45) & Chr(47) & Chr(58) & Chr(59) & Chr(34)
'
If lastchr = Chr(44) Or Chr(46) Or Chr(33) Or Chr(63) O
Chr(41) _
Or Chr(93) Or Chr(62) Or Chr(45) Or Chr(47) Or Chr(58) O
Chr(59) _
Or Chr(34) Then
' Code if punctuation mark identified
savelastchr = lastchr
str(i) = Mid(str(i), 1, lastchrnum - 1) ' remove
punctuation
MsgBox "this is the altered string: [" & str(i) & "]"
End If

Next
End If

End Su

 

[Bob Phillips]

Jason,

You multiple if test is invalid. You cannot test like
If A=b or c or d ...
you have to use
If A=b Or A=c Or A=d ...


Here's an amende version

Private Sub textbox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)

Dim str() As String
Dim i As Long
Dim lastchrnum As String
Dim lastchr As String
Dim savelastchr As String
'
'
'
If KeyCode = Asc(" ") Then
str = Split(TextBox1.Text, " ")
On Error Resume Next
For i = LBound(str) To UBound(str)
'
' IF last character of string =
' "," = chr(44)
' "." = chr(46)
' "!" = chr(33)
' "?" = chr(63)
' ")" = chr(41)
' "]" = chr(93)
' ">" = chr(62)
' "-" = chr(45)
' "%" = chr(37)
' ":" = chr(58)
' ";" = chr(59)
' """ = chr(34)
'
' Then remove it from string
'
' TAKE OFF PUNCTUATION MARKS
lastchrnum = Len(str(i))
MsgBox "there are this many sub-strings in the string: [" & lastchrnum & "]"
lastchr = Mid(str(i), lastchrnum, 1)
MsgBox "this is last character of the string: [" & lastchr & "]"
MsgBox "The last chr should only be removed if it is one of the following "
& vbNewLine _
& " " & Chr(44) & Chr(46) & Chr(33) & Chr(63) & Chr(41) & Chr(93) &
Chr(62) _
& Chr(45) & Chr(47) & Chr(58) & Chr(59) & Chr(34)
'
If (lastchr = Chr(44) Or _
lastchr = Chr(46) Or _
lastchr = Chr(33) Or _
lastchr = Chr(63) Or _
lastchr = Chr(41) Or _
lastchr = Chr(93) Or _
lastchr = Chr(62) Or _
lastchr = Chr(45) Or _
lastchr = Chr(47) Or _
lastchr = Chr(58) Or _
lastchr = Chr(59) Or _
lastchr = Chr(34)) Then
' Code if punctuation mark identified
savelastchr = lastchr
str(i) = Mid(str(i), 1, lastchrnum - 1) ' removes punctuation
MsgBox "this is the altered string: [" & str(i) & "]"
End If

Next
End If

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I am trying to remove punctuation marks from a string entered in a text
box. The code below is triggered whenever the user presses the space
bar while typing words in a text box.

I only want the last sub-string removed from the word if the last
sub-string is a punctuation mark. As you will note, I have identified
those punctuation marks that I want to remove below.

But my code does not work....the IF statement is ignored and Excel
removes the last sub-string of ANY string entered into the text box.
Why is this?

Enter into Userform1 with one textbox named textbox1
_________________________________
Private Sub textbox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)

Dim str() As String
Dim i As Long
Dim lastchrnum As String
Dim lastchr As String
Dim savelastchr As String
'
'
'
If KeyCode = Asc(" ") Then
str = Split(TextBox1.Text, " ")
On Error Resume Next
For i = LBound(str) To UBound(str)
'
' IF last character of string =
' "," = chr(44)
' "." = chr(46)
' "!" = chr(33)
' "?" = chr(63)
' ")" = chr(41)
' "]" = chr(93)
' ">" = chr(62)
' "-" = chr(45)
' "%" = chr(37)
' ":" = chr(58)
' ";" = chr(59)
' """ = chr(34)
'
' Then remove it from string
'
' TAKE OFF PUNCTUATION MARKS
lastchrnum = Len(str(i))
MsgBox "there are this many sub-strings in the string: [" &
lastchrnum & "]"
lastchr = Mid(str(i), lastchrnum, 1)
MsgBox "this is last character of the string: [" & lastchr &
"]"
MsgBox "The last chr should only be removed if it is one of the
following" & vbNewLine _
& " " & Chr(44) & Chr(46) & Chr(33) & Chr(63) & Chr(41) &
Chr(93) & Chr(62) _
& Chr(45) & Chr(47) & Chr(58) & Chr(59) & Chr(34)
'
If lastchr = Chr(44) Or Chr(46) Or Chr(33) Or Chr(63) Or
Chr(41) _
Or Chr(93) Or Chr(62) Or Chr(45) Or Chr(47) Or Chr(58) Or
Chr(59) _
Or Chr(34) Then
' Code if punctuation mark identified
savelastchr = lastchr
str(i) = Mid(str(i), 1, lastchrnum - 1) ' removes
punctuation
MsgBox "this is the altered string: [" & str(i) & "]"
End If

Next
End If

End Sub

 

[JE McGimpsey]

One alternative:

Dim str As Variant
Dim i As Long
Dim bRemove As Boolean
str = Split(Textbox1.Text, " ")
For i = LBound(str) To UBound(str)
bRemove = str(i) Like "*[,.!?)>%:;-]" Or _
Right(str(i), 1) = """" Or Right(str(i), 1) = "]"
str(i) = Left(str(i), Len(str(i)) + bRemove)
Next i