Worksheet_SelectionChange

[RichardU]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not ActiveCell = Range("I11") Then
    VendorLength = Len(Range("I11").Value)
    If VendorLength = 0 Then Exit Sub
    If VendorLength <> 6 Then
    MsgBox Prompt:="Vendor Number must be 6 digits long!!!", Buttons:=vbOKOnly, Title:="Vendor Number"
    Range("I11").Value = ""
    Range("I11").Select
    End If
    If VendorLength = 6 Then
    CharacterStart = InStr(1, Range("I11").Value, 8, 1)
        If CharacterStart = 1 Then Exit Sub
        MsgBox Prompt:="The Vendor Number must start with an 8" & Chr(13) & "e.g." & Chr(13) & "800001", Buttons:=vbOKOnly, Title:="Vendor Number"
        Range("I11").Value = ""
        Range("I11").Select
    End If
End If

If Not ActiveCell = Range("I12") Then
    BankAccountNumber = Range("I12").Value
    BankAccountNumberLength = Len(BankAccountNumber)
    If BankAccountNumberLength = 0 Then Exit Sub
    If BankAccountNumberLength <> 8 Then
    MsgBox Prompt:="Bank Account Number must be 8 digits long!!!", Buttons:=vbOKOnly, Title:="Bank Account Number"
    Range("I12").Value = ""
    Range("I12").Select
    End If
End If

End Sub

I am using this code but i want it to work if the user selects any other cell, at the moment it does work for the vendor number but the bank account number only works if they click in cell I11 not in every other cell???

 

[Lewiy]

I think that the Worksheet_Change event would be more suitable, but either way, give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$11" Then
    If Len(Target.Value) <> 6 And Len(Target.Value) <> 0 Then
        MsgBox "Vendor number must be 6 digits long"
        Range("I11") = ""
        Range("I11").Select
    End If
    If Left(Target.Value, 1) <> "8" Then
        MsgBox "Vendor number must start with an 8"
        Range("I11") = ""
        Range("I11").Select
    End If
End If
If Target.Address = "$I$12" Then
    If Len(Target.Value) <> 8 And Len(Target.Value) <> 0 Then
        MsgBox "Bank Account number must be 8 digits long"
        Range("I11") = ""
        Range("I11").Select
    End If
End If
End Sub

 

[Lewiy]

Incidentally, the reason that the bank account part of your code was not working is because it never reached that section of the code due to the "Exit Sub" statements above it which would always fire in the event that the Bank Account section was required, thus stopping the macro before it got there.