CODEWITHSUNDEEP
excelvba
james daley
james daley

Reputation: 55

Validate a cell in excel using vba?

I am trying to check, using vba, whether a cell contains no less than 7 and no more than 10 characters. I also want to check that only numbers are put into the cell and no letters, except unless the characters 'GB' prefix a series of numbers.

So if I type

'12345678'in my cell then this is a valid entry and I do not get an error message, and if I type in 'GB1234567' then this is also a valid entry.

However if I typed '12343543GB' this is not valid or if I typed any other letters like 'HGDFEGEFS' this is not valid

does anyone know how I could adjust my code to get this to work? thanks

If Len(Range("D21").Value) < 7 Or Not IsNumeric(Range("D21").Value) Then
Dim AckTime2 As Integer, InfoBox2 As Object
    Set InfoBox2 = CreateObject("WScript.Shell")
    'Set the message box to close after 10 seconds
    AckTime2 = 1.5
    Select Case InfoBox2.Popup("Ooops!" & vbNewLine & vbNewLine & "Please go back and check the VAT number.", _
    AckTime2, "Cannot Submit the Form!", 0)
        Case 1, -1
    End Select

Else

MsgBox "No Error"
End If

Upvotes: 0

Views: 216

Answers (2)

ZAT
ZAT

Reputation: 1347

You could try this as well:

Sub chekk()
Set rngg = Range("D21")
Valu = rngg.Value
numPart = Mid(Valu, 3)

If (IsNumeric(numPart) And Mid(Valu, 1, 2) = "GB") Or IsNumeric(Valu) Then

    If Len(Valu) >= 7 And Len(Valu) < 10 Then
    MsgBox "valid"
    Else: MsgBox "invalid"
    End If

Else: MsgBox "invalid"
End If

End Sub

Upvotes: 0

Bathsheba
Bathsheba

Reputation: 234875

Personally I'd avoid encoding the validation in VBA as that could be hard to locate as your codebase matures and other folk maintain it.

I'd consider doing the validation on the worksheet. If A1 contains the text then this formula

=IF(AND(LEN(A1)>=7, LEN(A1)<=10),IF(ISNUMBER(A1),TRUE,IF(LEFT(A1,2)="GB",ISNUMBER(MID(A1,3,10)),FALSE)))

does the validation for you. (It's a little unwieldy but you could always split it up into more than once cell.) You can then use this cell in conjunction with Excel's cell validation and avoid VBA altogether.

Another alternative would be to use VBA to validate a regular expression. But unfortunately this is not built natively in VBA.

Upvotes: 2

Related Questions