Validation of Cells

[MagiCat]

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.

 

[Damon Ostrander]

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:

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]

Thanks, that works great. Had to edit it to get it validate any time a range of cells was changed but it worked great.