Trying to replace invalid keyed text and also warn the user (1 Viewer)

[ghudson]

I am trying to prevent a user from keying spaces, quotes and double quotes in specific columns. I have my code
working to where I am able to replace the invalid characters but I want to warn the user with a message box
each time I have to correct their invalid text entries.

Where or how do I place my message box so that the user is only warned after they commit the error? My
current code below is triggering the message box not matter if the user keys the invalid characters or not.

Can you help me with how to only display the error message when the user has keyed an invalid text? I am also
open to any suggestions on how else to code my routine if there is a more efficient way to do it versus how I
have it written. I did a lot of searching and this was the results of my efforts. I am using Excel 2003. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Worksheet_Activate
 
    If Target.Column = 2 Then 'column B, all cells in the column
        Application.EnableEvents = False
        Target = Replace(Target, " ", "")
        MsgBox "You keyed an invalid space in cell " & Target.Offset(0, 0).Address(False, False) & ".  Your space was removed."
        Application.EnableEvents = True
    End If
 
    If Target.Column = 4 Then 'column D, all cells in the column
        Application.EnableEvents = False
        Target = Replace(Target, """", "IN")
        MsgBox "You keyed an invalid double quote in cell " & Target.Offset(0, 0).Address(False, False) & ".  Your double quote was converted into " & "''IN'' for inches."
        Application.EnableEvents = True
    End If
 
Exit_Worksheet_Activate:
    Application.EnableEvents = True
    Exit Sub
 
Err_Worksheet_Activate:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Worksheet_Activate
 
End Sub

 

[RuralGuy]

Sorry I don't know the answer but welcome back to the forum! Long time, no see.

 

[Brianwarnock]

Check the length of the Target before and after the replace and if it is different then issue the message.

brian

Edit Scrap that TB=Targret before replace
TA =Target after replace
Now compare TA and TB this will work even for other instances when the length doesn't change.
Actually you don't need TA just TB<> Target, OH! the joys of typing as you think!!

 

[chergh]

I would do it like:

    If Target.Column = 2 Then 'column B, all cells in the column
        Application.EnableEvents = False
        If CBool(InStr(1, Target.Value, " ")) = TRUE Then
            Target = Replace(Target.Value, " ", "")
            MsgBox "You keyed an invalid space in cell " & Target.Offset(0, 0).Address(False, False) & ".  Your space was removed."
        End If
        Application.EnableEvents = True
    End If
 
    If Target.Column = 4 Then 'column D, all cells in the column
        Application.EnableEvents = False
        If CBool(InStr(1, Target.Value, """")) = TRUE Then
            Target = Replace(Target.Value, """", "IN")
            MsgBox "You keyed an invalid double quote in cell " & Target.Offset(0, 0).Address(False, False) & ".  Your double quote was converted into " & "''IN'' for inches."
        End If
        Application.EnableEvents = True
    End If

 

[ghudson]

RuralGuy, thank for the welcome back. It has been a long time. My developing needs have been very limited over the past two years. Although I do kinda miss it.

Brianwarnock, thanks for the idea. I had not thought of checking the length.

chergh, Your code works perfectly for what I was trying to do. I have not had to code in Excel in a long time and your suggestion has made my day. Thank you!

 

[Brianwarnock]

Chergh's approach is perhaps the most efficient, I had thought that you might like to change your messages such as

If Target.Column = 2 Then 'column B, all cells in the column
        Application.EnableEvents = False
        TB = (Target)
        Target = Replace(Target, " ", "")
        If TB <> Target Then
        MsgBox "You keyed an invalid space in cell " & Target.Offset(0, 0).Address(False, False) & (Chr(13)) & [TB] & "   replaced by    " & [Target]
        End If
        
        Application.EnableEvents = True
    End If

I hadn't elaborated before as i didn't want to risk insulting a man of your calibre. I thought that maybe you were having a "Senior moment"

Brian

 

[ghudson]

I hadn't elaborated before as i didn't want to risk insulting a man of your calibre. I thought that maybe you were having a "Senior moment"
Brian

Flattery will get you now where in this forum. It has been over a year since I last posted here so I do feel a little out of practice.

Your solution works just as well. Thanks for the follow-up.

I had already settled on this error message I had modified since it was simple and blunt for which these users need.

MsgBox "You keyed an invalid space in cell " & Target.Offset(0, 0).Address(False, False) & "." & vbCrLf & vbLf & "Your space was removed.", vbInformation, "Invalid Character - Spaces Are Not Allowed In Column B"

But I do like the idea of showing the user a before and after example. I cannot use it with this application for I am also removing single quotes and double quotes in column 4 [' = FT and " = IN] and the before and after is messy since I am doing all the replacing in the same Worksheet_Change event.

Tip of the day: I like to use the Miscellaneous Constants [vbCrLf, vbCr, etc.] instead of the ASCII characters functions like Chr(13).

 

[Brianwarnock]

I like your ip thanks for that.
I don't understand what's messy, how about

If Target.Column = 4 Then 'column D, all cells in the column
        Application.EnableEvents = False
        TB = (Target)
        Target = Replace(Target, """", "IN")
        Target = Replace(Target, "'", "FT")
        If TB <> Target Then
        MsgBox "You keyed an invalid quotes in cell " & Target.Offset(0, 0).Address(False, False) & vbCrLf & [TB] & "   replaced by    " & [Target]
        End If
        
        Application.EnableEvents = True
    End If

Not that I'm trying to persuade you away from Chergh approach.

Brian

 

[ghudson]

I don't understand what's messy,

Oh, I only meant the msgbox trying to display the before and after values since I was replacing the single and double quotes at the same event and the users could have those in the same cell. Your method works fine. Thanks!