• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Prohibit certain special characters in column

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?
0
nainil
Asked:
nainil
  • 15
  • 12
  • 4
  • +2
3 Solutions
 
Rory ArchibaldCommented:
Dim rng as range
set rng = sheets("Sheet1").Range("A:A")
If application.countif(rng, "*^*) > 0 or application.countif(rng, "*|*) > 0 Then
   msgbox "Invalid data"
End If

Open in new window


for example.
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
This would be a more appropriate screenshot. I am setting the data validation for Col I.

Sid
Untitled.jpg
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
nainilAuthor 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.
0
 
SiddharthRoutCommented:
Ah Ok. :)

Sid
0
 
SiddharthRoutCommented:
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
0
 
nainilAuthor Commented:
rorya:
I am seeing an invalid character in the statement..."*|*)

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

Typo, rory missed the closing quotation marks:

If application.countif(rng, "*^*") > 0 or application.countif(rng, "*|*") > 0 Then
0
 
SiddharthRoutCommented:
Sample File Attached.

Type typing or pasting in Col I.

Sid

Code Used

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

Open in new window

Book1.xls
0
 
SiddharthRoutCommented:
Oops Please change line 2 to

Application.EnableEvents = False

Sid
0
 
Rory ArchibaldCommented:
Thanks, Stephen!
0
 
broro183Commented:
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
0
 
SiddharthRoutCommented:
>>>"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
0
 
broro183Commented:
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
0
 
nainilAuthor 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.
0
 
SiddharthRoutCommented:
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
0
 
broro183Commented:
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...
0
 
broro183Commented:
You're too quick for me Sid :-)
0
 
nainilAuthor 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
0
 
SiddharthRoutCommented:
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
0
 
nainilAuthor 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.




0
 
SiddharthRoutCommented:
You mean a formula kind of thing?

Sid
0
 
nainilAuthor 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

0
 
SiddharthRoutCommented:
What does Range C2 Have?

Sid
0
 
nainilAuthor 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.
0
 
SiddharthRoutCommented:
Sure gimme a moment

Sid
0
 
SiddharthRoutCommented:
Ok What I have done is increased the capability of cell C2. YOu can now either enter 1 Character or more than one separated by commas for example you can have in C2

^

or

^,|

C1 will have the length required.

See sample file attached

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim ValChars As String, MyArray() As String
    Dim i As Long
    
    On Error GoTo Whoa
    
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        ValChars = Range("C2").Value
        
        If InStr(1, ValChars, ",") Then
            MyArray = Split(ValChars, ",")
            For i = 0 To UBound(MyArray)
                If InStr(1, Target.Value, MyArray(i)) Then
                    Target.ClearContents
                    MsgBox "Invalid String"
                    Exit Sub
                End If
            Next i
        Else
            If InStr(1, Target.Value, ValChars) Then
                Target.ClearContents
                MsgBox "Invalid String"
                Exit Sub
            End If
        End If
        If Len(Trim(Target.Value)) <> Val(Trim(ActiveSheet.Range("C1").Value)) And _
        Len(Trim(Target.Value)) <> 0 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

Sample.xls
0
 
nainilAuthor 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.
0
 
SiddharthRoutCommented:
Col A (see Col 1 in the code below)

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

Open in new window


Sid
0
 
nainilAuthor 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.
0
 
SiddharthRoutCommented:
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
0
 
nainilAuthor 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
0
 
nainilAuthor Commented:
Some interesting thoughts shared on how to use Excel-VBA to do data validation!

Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 15
  • 12
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now