How to call VBA function from excel

Question

I have en excel file where i have to put validation rule. I have one cell let says "customer Time" where user can enter time but it is customize time. User can enter time like that

23:45
98:20
100:30

User cannot enter string and no special character except colon. I have made one macro and it works perfectly accoriding to customer demand. Here is macro

Public Function isValidTime(myText) As String
Dim regEx
Set regEx = New RegExp   'Regular expression object
regEx.Pattern = "^[0-9]+([:]+[0-9]+)*$"  ' Set pattern.
If regEx.test(myText) Then
isValidTime = myText
Else
isValidTime = "Null"
End If
End Function

Note: To test this macro you have to go to VBA ide in Tool then reference and then select microsoft visual basic vbascript 5.5

Now i want to call this at excel. I can call like =IsValidTime("23:43") and getting result but customer is not interested to call this. Customer need a excel where he/she enter the value and value will validate according to above criteria and get the exact value or Null.

I dont know how to fix this task. I have Validated date and time as well from Data and then data validation and set the rule and it works perfect, i need the same way for my this rule as well. Any help will be highly appreciated...

Thanks Kazmi

Answer 1

You can use the Worksheet_Change event inside the sheet. Inside the VBE, select the sheet and choose Workhseet from the left drop-down and Change from the right.

Enter the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then 'assumes user input cell is A1

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    On Error GoTo ErrTrap

    Target.Value = isValidTime(Target.Value)

End If

KeepMoving:

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    Exit Sub 

ErrTrap:

    MsgBox Err.Number & Err.Description
    Resume KeepMoving


End Sub

Public Function isValidTime(myText) As String

Dim regEx

Set regEx = New RegExp   'Regular expression object

regEx.Pattern = "^[0-9]+([:]+[0-9]+)*$"  ' Set pattern.

If regEx.test(myText) Then

    isValidTime = myText

Else
    isValidTime = "Null"

End If

End Function