Change code so it also prevents special characters being entered

[danbates]

Hi,

I have this code that I would like to alter so it prevents the so called special characters as well as numbers being entered into my range.

Special characters being !"£$%^&*()_+=-{}][~#';<>?/.,|\`¬ and so on.

If Target.Column <> 5 Then Exit Sub

Dim cell As Range
    Application.EnableEvents = False
    For Each cell In Target
           If IsNumeric(cell.Value) Then
           MsgBox "NO NUMBERS"
              cell.Value = vbNullString
              cell.Select
           End If
    Next cell
    Application.EnableEvents = True
    
Exit Sub

Thanks

Dan

 

[Tim_Excel_]

Would you be content with a sub that strips all <> [A-z] characters from the selected range?
e.g. 1.Text$ becomes Text

 

[danbates]

Hi Tim_Excel_,

I'm sorry but I don't really understand what you mean.

What I am after is that column E only accepts upper and lower case A-Z.

Thanks

Dan

 

[Tim_Excel_]

What I mean is that you have two options
1. Track every character being typed in by the user
2. Remove all non [A-z] characters AFTER the user has typed them in

the second is significantly easier to program

Sub removechars()
With Worksheets().Sheets() 'references

For each c In .Range("A1:Z20") 'reference

    If Not c.value = "" Then
        i = 0
        l = Len(valstr)
        
        Do            
            i = i + 1
            x = UCase(Mid(valstr, i, 1))
            isletter = Asc(x) > 64 And Asc(x) < 91
            If isletter = False Then
                valstr = Left(valstr, i - 1) & Mid(valstr, i + 1, Len(valstr))
                i = i - 1
                l = Len(valstr)
            End If
        Loop Until i = l
        c.Value = valstr
    End If
Next c

End With
End Sub

Alternatively, you could rewrite this to suit a Worksheet_Change event

 

[danbates]

The second option would be perfect.

I've just tried your code but it comes up with a compile error variable not defined and highlights the c in the line of For Each c In

Thanks again

Dan

 

[Joe4]

I've just tried your code but it comes up with a compile error variable not defined and highlights the c in the line of For Each c In

You must have "Option Explicit" at the top of your code, which is actually a good thing and helps preventing errors. It just requires you to declare all your variables before using them. It is a "best practice" to do this when writing code.

So, just add these four lines at the top of your code (after the "Sub" line):

Dim c as Range
Dim i as Long
Dim x as String
Dim valstr as String

 

[Tim_Excel_]

I'd like to add that I forgot to add the line that populates variable valstr..

If Not c.Value = "" Then        
valstr = c.Value
i = 0
l = Len(valstr)

 

[danbates]

Hi Guys,

I do have Option Explicit at the top of my module. I've always been told it's the better option.

I've added both parts into the code but it still coming up with the same compile error.

Can you check over the code to make sure I've edited it correctly. The red part is where the error is occurring.

Sub removechars()

Dim c As Range
Dim i As Long
Dim x As String
Dim valstr As String


With Worksheets("Changeover Form")


For Each c In .Range("E:E") 'reference


If Not c.Value = "" Then
valstr = c.Value
i = 0
[COLOR=#ff0000]l =[/COLOR] Len(valstr)
        
        Do
            i = i + 1
            x = UCase(Mid(valstr, i, 1))
            isletter = Asc(x) > 64 And Asc(x) < 91
            If isletter = False Then
                valstr = Left(valstr, i - 1) & Mid(valstr, i + 1, Len(valstr))
                i = i - 1
                l = Len(valstr)
            End If
        Loop Until i = l
        c.Value = valstr
    End If
Next c


End With
End Sub

Thanks again

Dan

 

[Tim_Excel_]

You have not declared the 'l' variable...

Furthermore, are you reeeaaally sure you want to loop through ALL cells in col E? Seems a bit excessive to loop through 1,048,576 cells of which most will be empty, most likely.

 

[danbates]

It's working now and yes I understand about column E. I will alter that to suit.

A big thank you to both of you for all your help. It is appreciated.

Thanks again

Dan