Prohibit certain special characters in column

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

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

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

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

Oops Please change line 2 to

Application.EnableEvents = False

Sid

Thanks, Stephen!

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

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

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

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

Select allOpen 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

Select allOpen in new window

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

You're too quick for me Sid :-)

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	^

Select allOpen 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

Select allOpen in new window

Sid

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

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

Select allOpen in new window

What does Range C2 Have?

Sid

This could serve as a reference point for the validation.

Something similar to the following:

 

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

Select allOpen 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

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

Select allOpen in new window

Sid

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

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

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

Thank you