Validation of Cells

MagiCat

Board Regular
Joined
May 24, 2006
Messages
121
I've written a function in VBA which returns a true/false value and have saved it in an xla file. Is there any way to validate a cell in Excel based on this function?

The function declaration is:

Function VINValidate(VINNumber As String) As Boolean

Note, I've posted a similar question in the Access forum as well but that's for validation in Access, I need to validate in both programs.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi MagiCat,

I am surprised that Excel does not allow the use of a user-defined function in the validation formula. Fortunately there is a simple way around this: use the worksheet Change event to do the validation. In your case you should be able to put code like this in the worksheet's event code module:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$B$4" Then
      If Not VINValidate(Target.Text) Then
         Application.EnableEvents = False
         Target.Activate
         MsgBox "Not a valid VIN"
         Target.ClearContents
         Application.EnableEvents = True
      End If
   End If
End Sub

This will pop up a message if the user enters an invalid VIN, will clear the cell contents and activate the cell for the user to re-enter the VIN. This code example is based on the validation being for cell B4.

Keep Excelling.

Damon
 

MagiCat

Board Regular
Joined
May 24, 2006
Messages
121
Thanks, that works great. Had to edit it to get it validate any time a range of cells was changed but it worked great.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,794
Messages
5,544,322
Members
410,603
Latest member
rseckler
Top