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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top