We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Prohibit certain special characters in column

Medium Priority
415 Views
Last Modified: 2012-05-11
I have a column in excel sheet. I want to validate that the column does not contain any of the following characters.

‘^’ (carot) or ‘|’ (pipe).

How can one achieve this using VBA?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
nainil: I see that you wanted to do this via VBA. Why not use Data Validation so that the user will not be able to enter those characters only in the first place?

Please see snapshot.

Sid
Untitled.jpg
This would be a more appropriate screenshot. I am setting the data validation for Col I.

Sid
Untitled.jpg

Author

Commented:
SiddharthRout:

I tried using the 'Data Validation' before posting here.

When people paste the information directly to the cells, the data validation does not work.; defeating the purpose of the validation.

Hence, VBA.
Ah Ok. :)

Sid
There is one more VBA Code which will prevent Data from being entered (by typing or pasting) at real time, which is a slight modification of Rory's code.

Sid

Author

Commented:
rorya:
I am seeing an invalid character in the statement..."*|*)

Author

Commented:
SiddharthRout: I would love to see the same.
Not for points.

Typo, rory missed the closing quotation marks:

If application.countif(rng, "*^*") > 0 or application.countif(rng, "*|*") > 0 Then
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Oops Please change line 2 to

Application.EnableEvents = False

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Thanks, Stephen!
CERTIFIED EXPERT

Commented:
hi everyone,

Nainil,
Just in case you aren't already aware, a potential downside of using a "Worksheet_Change" event macro is that it results in the "Undo stack" being wiped each time it runs (unless some more complicated code is used - I think, it is over my head! ;-)). This can result in some unhappy users if they are prone to making mistakes.

Sid,
Would your code benefit from having a test to ensure that "target.cells.count = 1"?
(I'm feeling a bit lazy & haven't tested it... plus it's time for bed:-) )

Rob
>>>"Undo stack" being wiped each time it runs

That is the case with almost any macro run that makes Changes to the sheet :)

>>>Would your code benefit from having a test to ensure that "target.cells.count = 1"?

You could put in an error handler if you wish :)

Sid
CERTIFIED EXPERT

Commented:
That is the case with almost any macro run that makes Changes

True, I guess I was stating the obvious there. I have looked over people's shoulders as they are working on files from other departments because they say something like "I can't copy anything" or "why doesn't [ctrl + z] work" and I can't offer a solution from my desk - but the game is given away when I spot the brief flicker as the change code does whatever :-)

Rob

Author

Commented:
Thank you Broro183 & SiddharthRout:

A quick concern: "I can't copy anything" or "why doesn't [ctrl + z] work"

Would this happen when I use the code? May be you can provide some insights on what the implications will be as I do not want frustrate the end user.
nainil: It will happen with almost any code that you use :) You will not be able to use 'Undo' for your previous actions. Excel will rebuild your "Undo stack" and then you can use it :)

Sid
CERTIFIED EXPERT

Commented:
hi Nainil,

as per StephenJR earlier comment this is "not for points"


The best way to find out is to try it & see. Then perhaps get someone else to try it, but give them the title of "Tester" instead of end-user ;-)
The implications are pretty well explained by the quotes, but the impact may vary a lot from user to user depending on how they use/populate the file.

If you explain it to the users, they may be quite happy with the limitations when they know that it ensures good quality data entry.

---------
It's been a long day at work, so the following is just me throwing ideas around - others may have good reason to rebut my suggestions (or my implementation?) - I'd appreciate the feedback as I'm just filling in time... :-)

On the other hand, you could decrease the number of times the Undo Stack is cleared by using Rorya's code in the worksheet event macro for deactivating the sheet because this may (depending on the user's approach to populating the sheet) happen less often than a cell being changed on the sheet. For example:

'in a normal module
Option Explicit
Sub CheckData(rng As Range, Optional ByRef Cncl As Boolean)
    If Application.CountIf(rng, "*^*") > 0 Or Application.CountIf(rng, "*|*") > 0 Then
        MsgBox "Invalid data, please correct this before changing sheets or saving the file.", vbOKOnly, "WARNING!"
        rng.Parent.Activate
        Cncl = True
    End If
End Sub

'in the specific Sheet module
Option Explicit
Private Sub Worksheet_Deactivate()
    Call CheckData(Me.Range("A:A"), False)
End Sub

'Also, you could ensure the that the sheet is checked when the file is closed by adding the below into the ThisWorkbook module
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call CheckData(Me.Worksheets("Sheet1").Range("A:A"), Cancel)
End Sub

Open in new window


Of course, and, there is usually a caveat - this requires that the user has "macros enabled". Just like the other approaches you can see it has some weaknesses. It may just come down to specific user training...

I don't recommend this approach, but, you could even ignore the users & instead of telling them to make a correction you could just over-ride any errors by wrapping the below snippet in one of the event macros shown above:

With ThisWorkbook.Worksheets("Sheet1").Range("A:A")
    .Replace What:="^", Replacement:="_", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    .Replace What:="|", Replacement:="_", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With

Open in new window



hth
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...
CERTIFIED EXPERT

Commented:
You're too quick for me Sid :-)

Author

Commented:
SiddharthRout:
I am wondering if there is a better approach...

I have multiple sheets, with multiple columns.
If I list the information (as below),
 
Column	MaxSize	InvalidChars
A	4	|,^
B	10	^,%
C	2	^

Open in new window


is it possible to provide validation using a combination of the parameters listed?
Like Max Char and invalid characters / prohibited values for each column?

So, for Column A, we would validate on max length of 4 and 2 invalid characters (|, ^)?


PS. Increasing the points on the question due to increased complexity. Appreciate the input
You mean like this? This will not only check for the characters but will also ensure that the length has to be 4.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = True
    
    On Error GoTo Whoa
    
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If InStr(1, Target.Value, "|") Or InStr(1, Target.Value, "^") Then
            Target.ClearContents
            MsgBox "Invalid String"
        ElseIf Len(Trim(Target.Value))<> 4 Then
            Target.ClearContents
            MsgBox "Invalid Length"
        End If
    End If
    
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Open in new window


Sid

Author

Commented:
SiddharthRout:

Wow, that is perfect.

I would be much helpful (for the administrator) if one could read the maxlength and  "prohibited characters" from a cell and perform the validation for that particular column...

This would remove the need to maintain the code and promote dynamism.




You mean a formula kind of thing?

Sid

Author

Commented:
something like this...

It does not work for me (not sure how to split the comma separated value and put them in the validation function...
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = True
    
    On Error GoTo Whoa
    
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If InStr(1, Target.Value, activesheets.range("C2")) Or InStr(1, Target.Value, "^") Then
            Target.ClearContents
            MsgBox "Invalid String"
        ElseIf Len(Trim(Target.Value))<> Activesheet.range("C1").value Then
            Target.ClearContents
            MsgBox "Invalid Length"
        End If
    End If
    
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Open in new window

What does Range C2 Have?

Sid

Author

Commented:
This could serve as a reference point for the validation.

Something similar to the following:

 
Column	MaxSize	InvalidChars
A	4	|,^
B	10	^,%
C	2	^

Open in new window


Column 1 consists of the column names
Column 2 consists the max length for each column
Column 3 consists of list of prohibited characters.

If the validation procedure could pick the values from this column (located on the same sheet or some other MASTER sheet, it will be helpful.

This will reduce the dependency on hardcoding values and refer to the cells, should some items change.
Sure gimme a moment

Sid
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
That is helpful.
In your sample file, what is the column which is validated?

Sorry, I am not too savvy with VBA :), a newbie.
Col A (see Col 1 in the code below)

If Not Intersect(Target, Columns(1)) Is Nothing Then

Open in new window


Sid

Author

Commented:
Hmm, it worked for a few rows in column A.

Then, when I pasted a value from one cell (which was invalid), I lost the validation.
Pasting values is also trapped. You shouldn't loos the validation.

1) Have you made any changes to the code?
2) Please exactly explain the step you followed above?

Sid

Author

Commented:
lets try this: I used the string asd^ (pasted it in say D5)
Copy it and paste it in A1... validation works.

put text (type in or paste) dsaasdqw in A4...

Validation should fail

Author

Commented:
Some interesting thoughts shared on how to use Excel-VBA to do data validation!

Thank you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.