Limiting the use of Special Characters

[spdkilz02]

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]

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

 

[spdkilz02]

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

 

[Joe4]

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]

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]

Dear Rick,

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.

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]

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]

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.

Related	Type	Sub Type	Status
Credit Card	Transaction/Statement	Email (Registered)	Submitted
Credit Card	Transaction / Statement	SMS (Registered Mobile)	Pending <>

 

[Gajendran Yadhav]

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.

Dear Rick,
Greetings.
I was looking for a similar scenario and found your code very helpful...however I just made very little alteration to suit my needs i.e., only UPPERCASES and Numbers. No other characters including Space & Dash.

However, can I have a limitation on Character Length as well included in the above code. I tried my best through Data Validation...however it didn't work as expected.

Requirement = 16 Character length.
It should contain both Alphabets & Numbers.

In the above code, it takes either alphabets full or numbers full or both. Is there any possibility..??

Thanks in Advance & Love from India.

 

[Akuini]

however I just made very little alteration to suit my needs i.e., only UPPERCASES and Numbers. No other characters including Space & Dash.

However, can I have a limitation on Character Length as well included in the above code. I tried my best through Data Validation...however it didn't work as expected.

Requirement = 16 Character length.
It should contain both Alphabets & Numbers.

Maybe this:

If Not cell Like "*[!A-Z0-9]*" And cell Like "*[A-Z]*" And cell Like "*[0-9]*" And Len(cell) = 16 Then
    'do nothing
Else
    MsgBox "Wrong input"
    Application.Undo
    Exit Sub
End If