How to validate and correct data in excel in realtime?

I know excel has the data validation feature but is there a way to automatically apply the changes for the user instead of prompting them that there is an issue?

For example, we have a general spreadsheet template for our internal system to add items. In the first column, for Item No, there can't be any illegal characters. So if the user enters AN-XR10LP/1 in A1 and then clicks on the next cell, I would want the validation to correct the A1 value to be ANXR10LP1 without the user doing anything.

Any ideas on how I can start with this?

Answers


I think you can only accomplish this with VBA. Try the code below. It may need to be tweaked if you have Data Validation on the cells.

Place this module in the Worksheet Object where you want your data validated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Rows.Count = 1 Then '-> make sure row count is 1 and target is in column A, can further refine if needed

    Dim strValue As String

    strValue = CleanString(Target.Value)

    Application.EnableEvents = False
    Target = strValue
    Application.EnableEvents = True

End If

End Sub

Function CleanString(str As String) As String

CleanString = Replace(str, "-", "")
CleanString = Replace(CleanString, "/", "")
'...
'keep adding replacements as needed


End Function

Create a handler for Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

In the handler, see where Target points to, and apply validation logic basing on that.

Make sure you ignore the Change event raised when you write back corrected values:

Private Sub Worksheet_Change(ByVal Target As Range)
    static self_protect as boolean

    if self_protect then exit sub


    ...
    self_protect = true
    Target.Value = "corrected value"
    self_protect = false
    ...

End Sub

Need Your Help

Problem with Visual C++ program— can't find the Debug CRT

c++ visual-c++ msvcrt

I have a friend who's taking over a Visual C++ project from me and is having trouble running it. It's a graphics application and it uses the Qt GUI library. The reason I mention this is because of...

Linked List Dependent Select Boxes

jquery ruby-on-rails ruby rjs

So I haven't been doing this for long but I'm completely stuck on this. I have a model which looks like this (simplified for brevity):

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.