"" Then Application.EnableEvents = False Call CleanTrimCells_Looping Application.EnableEvents = True If Target <> "" Then If Len(Target) <> 7 Then MsgBox ("The Employee ID must be 7 numbers long. Please re-enter a valid Employee ID.") .">
Most valuable professional
  • Hot Topics

Excel Characters Must Be in List

Hi; Is there anyway to modify this code, to check to make sure that a formatted text column cell has 7 characters out of the basic number list (0 to 9), and no alpha characters? I had to make Column A text, due to some Employee ID numbers containing a lead ZERO, if it's changed to Numeric it will cause a problem. Presently, this code does not work well with a text formatted column, Pasting in data got around my validations, but I changed the format to allow for that, so now this code hosts a few problems. 1. even though I am forcing a Paste Special Values only, it generated a Run-time error '13': Type mismatch, until I placed this in the code: On Error Resume Next Now, everything that is pasted in turns red, even using the forced Paste Special. Folks can either type in entries manually, or they cheat and paste entries in, which made me try to put this together. Private Sub Worksheet_Change(ByVal Target As Range) 'MsgBox Target.Address If Intersect(Target, Range("A2:A65000")) Is Nothing Then Exit Sub On Error Resume Next If Target <> "" Then Application.EnableEvents = False Call CleanTrimCells_Looping Application.EnableEvents = True If Target <> "" Then If Len(Target) <> 7 Then MsgBox ("The Employee ID must be 7 numbers long. Please re-enter a valid Employee ID.")

This question generated 12 answers. To proceed to the answers, click here.

This thread is current as of May 11, 2017.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel