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?
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