# Limiting the use of Special Characters



## spdkilz02 (Sep 9, 2016)

I am working on a sheet that I need to limit the use of special characters. I can only except all alphanumeric and "-" in a range of cells. B4:B2503

I can't use Data Validation for this because I am already using it for this range for something else.

Can anyone help with a VBA code that I can use to limit certain characters, or a code to say which characters are allowed?

I want to use a MsgBox to alert the individual they need to fix that cell. I don't want to override the special character.


----------



## Joe4 (Sep 9, 2016)

Have a look here: http://www.mrexcel.com/forum/excel-...n-alphanumeric-other-specific-characters.html


----------



## spdkilz02 (Sep 9, 2016)

I can't see to understand what they are talking about in that thread.


----------



## Joe4 (Sep 9, 2016)

Look at the last post in that thread from Peter.  There is VBA code that will pretty much give you what you need.
You should just be able to follow the steps he gave for pasting the VBA code to your workbook.

There are just a few lines of that code that need to be changed, namely the Range to apply it to specified here:

```
Const myTarget As String = "C1:C100"    '<- Change to suit
```
and this line here:

```
.Pattern = "[^0-9a-z-_]"
```
change to:

```
.Pattern = "[^0-9a-z-]"
```


----------



## Rick Rothstein (Sep 9, 2016)

Give this event code a try...

```
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("B4:B2503")) Is Nothing Then
    For Each Cell In Intersect(Target, Range("B4:B2503"))
      If Cell.Value Like "*[!A-Za-z0-9-]*" Then
        MsgBox "You have at least one invalid character in cell " & _
               Cell.Address(0, 0) & ". Please enter your value " & _
               "again and this time remember that only letters, " & _
               "digits and/or dashes are allowed!", vbCritical
        Application.Undo
        Exit Sub
      End If
    Next
  End If
End Sub
```

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.


----------



## anbaz (Dec 15, 2022)

Dear Rick,



Rick Rothstein said:


> Give this event code a try...
> 
> ```
> Private Sub Worksheet_Change(ByVal Target As Range)
> ...


Dear Rick,

I saw the above code which is very closely matches to my requirement. But I would like to customize your code a bit to meet my exact requirement. My requirement is to allow only numeric, alphabets and some specific special characters such as $ & * ( ) < > / \ -
*0-9 – Allowed
a-z and A-Z - Allowed
$ & * ( ) < > / \ - Allowed*

If user enters except the above-mentioned characters in worksheet, then I want Msg box to alert the user and clear the cell.
I tried modifying the code as mentioned below but it doesn’t work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

            If Not Intersect(Target, Range("A:D")) Is Nothing Then
            For Each Cell In Intersect(Target, Range("A:D"))
*If Cell.Value Like "*[!A-Za-z0-9$ & *()<>/\]*" Then*

                  MsgBox "You have at least one invalid character in cell " & _
                 Cell.Address(0, 0) & ". Please enter your value " & _
                 "again and this time remember that only letters, " & _
                 "digits and/or dashes are allowed!", vbCritical
                  Application.Undo
          Exit Sub
             End If
              Next
          End If
     End Sub

Could you please help me with the VBA code? I have been searching for such VBA code to allow only specific special characters in VBA code but none of them are useful. I can’t use data validation as I’m using the same cell range for something else.

Please help. Thank in advance!


----------



## Rick Rothstein (Dec 15, 2022)

You have some space characters in there that should be removed (otherwise spaces will be allowed to be typed in), but other than that, I see nothing wrong with your modification. I used this and it appeared to work fine for me...

If Cell.Value Like "*[!A-Za-z0-9$&*()<>/\]*" Then

In exactly what way did this not work for you (can you give me an example?


----------



## anbaz (Dec 17, 2022)

Rick Rothstein said:


> You have some space characters in there that should be removed (otherwise spaces will be allowed to be typed in), but other than that, I see nothing wrong with your modification. I used this and it appeared to work fine for me...
> 
> If Cell.Value Like "*[!A-Za-z0-9$&*()<>/\]*" Then
> 
> In exactly what way did this not work for you (can you give me an example?


Hi Rick,

Firstly, I wanted to say a big thank you for the quicky reply.

I modified the code to add one space then it worked perfectly. 


  If Cell.Value Like "*[!A-Za-z0-9-$&*()<>/\ ]*" Then

This is how my user creates data in the worksheet. 



RelatedTypeSub TypeStatusCredit CardTransaction/StatementEmail (Registered)SubmittedCredit CardTransaction / StatementSMS (Registered Mobile)Pending <>


----------

