Velden met IBAN-rekeningnummer: formaat en validatie

[tomswaelen]

Ik wil een Excel maken waar de gebruiker in verschillende cellen (laat ons zeggen: cellen A1 tem A11) IBAN-rekeningnummers kan invullen. Momenteel werken wij nog volgens BBAN (Belgisch model van rekeningnummers, maar is al jaren achterhaald). Dit wil ik dus anders.

Als een gebruiker in een cel een IBAN invult, zou dit liefst volgens het geijkte formaat moeten (groepjes van 4 karakters), maar dit is niet echt noodzakelijk, maar nice to have. Ik neem aan dat dit kan via een invoermasker, maar daar ben ik geen held in. Heeft iemand dit al staan?

Moeilijker lijkt mij om in elk van die cellen een validatieregel te steken zodat de IBAN correct is. Er zijn wel Excels in omloop op het internet waar een macro checkt op correctheid, maar ik zou het als validatieregel in de cel zelf willen, is dit mogelijk? Met ons huidige BBAN-formaat is dit geen probleem, dat is een mod97-controle.

Kan iemand mij op weg helpen?

 

[madmuppet]

heb je hier iets aan?

https://www.avk.nl/excel-sheet-met-formule-voor-iban-validatie-zoals-de-11-proef

 

[AlexCEL]

Met wat speurwerk op internet, en gebruik makend van bovenstaande link, kom ik op de oplossing in bijlage uit.

IBAN nummers in kolom A krijgen automatisch het format van een IBAN nummer na invoeren. In cel ernaast kan middels een UDF gecontroleerd worden of het een geldig IBAN nummer is.

 

[HSV]

Zo zou die het ook moeten doen in A1:A11.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, y As Long
If Not Intersect(Target, Range("a1:a11")) Is Nothing Then
 If Len(Target) = 18 Then
    Application.EnableEvents = False
      For i = 5 To Len(Target) Step 4
         Target.Characters(i + y, 0).Insert (" ")
         y = y + 1
      Next i
    Application.EnableEvents = True
  End If
 End If
End Sub

 

[tomswaelen]

Die Excel in de eerste link had ik zelf al gevonden, maar het custom voorbeeldje helpt zeker ook Ik krijg dit in eerste instantie wel niet klaar via gegegensvalidatie. Die kolom met TRUE / FALSE kan ik verbergen als workaround, maar de gebruiker zou een foutmelding moeten krijgen als hij een foute IBAN invult.

Hoe speel ik dit klaar? Of valt de foutmelding in bovenstaande code in te bouwen? Spijtig genoeg ken ik zelf bitter weinig van VBA. Ik kan het een beetje interpreteren, that's all...

 

[AlexCEL]

Vervang de code in A2 bijvoorbeeld door:

NL: =ALS(ValidateIban(A2)=WAAR;"ok";"fout!")
EN: =IF(ValidateIban(A2)=TRUE;"ok";"fout!")

En je krijgt een foutmelding als de IBAN in A2 niet klopt....

 

[tomswaelen]

Vervang de code in A2 bijvoorbeeld door:

NL: =ALS(ValidateIban(A2)=WAAR;"ok";"fout!")
EN: =IF(ValidateIban(A2)=TRUE;"ok";"fout!")

En je krijgt een foutmelding als de IBAN in A2 niet klopt....

Bedankt, maar ik bedoelde eigenlijk een foutmelding via een message box, zoals je dat via gegevensvalidatie kunt doen. Dat is wat ik geprobeerd heb, maar om één of andere reden lukt het me niet... De gebruiker zou een fout rekeningnummer eigenlijk niet mogen kunnen invullen. Of is het net dat wat niet gaat omdat de vba pas werkt na invoer? Heb ik nu net mijn eigen vraag beantwoord?

 

[Cobbe]

Zo iets?
Heb de 2 toegepast, Validatie en Voorwaardelijke Opmaak.
Je kan kolom B verbergen.

 

[HSV]

Iets ander en kortere methode inclusief check.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, y As Long, check As Boolean
If Not Intersect(Target, Range("a1:a11")) Is Nothing And Target.Count = 1 Then
 With CreateObject("vbscript.regexp")
            .ignorecase = True
            .Pattern = "^[A-Z]{2}[0-9]{2}[A-Z]{4}[0-9]{10}$"
               check = .test(Target.Value)
        End With
 Application.EnableEvents = False
 If check Then
      For i = 5 To Len(Target) Step 4
         Target.Characters(i + y, 0).Insert (" ")
         y = y + 1
       Next i
    Else
         MsgBox "Verkeerde invoer;" & vbLf & vbLf & _
           "Dit moet zijn:  2 letters gevolgd door 2 cijfers, daarna 4 letters en 10 cijfers.", vbInformation, "Let op"
        Application.Undo
    End If
    Target = UCase(Target)
   Application.EnableEvents = True
 End If
End Sub

 

[tomswaelen]

Hier kan ik zeker mee verder. Bedankt allemaal!

 

[tomswaelen]

Zo iets?
Heb de 2 toegepast, Validatie en Voorwaardelijke Opmaak.
Je kan kolom B verbergen.

Ondertussen heb ik jouw code goed weten te gebruiken, dank daarvoor! (ook aan de rest natuurlijk)

Eén vraagje nog: hoe kan ik er voor zorgen dat de eerste twee letters altijd naar hoofdletters worden omgezet? In de praktijk gaat de Excel enkel gebruikt worden voor Belgische IBANs, bv. BE12 3456 7890 1235. Zou mooi zijn dat het wordt omgezet naar "BE" als iemand zelf "be" typt.

Is niet per sé noodzakelijk, want het werkt nu ook, maar zou wel mooi zijn

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo oeps
    If Target.Column <> 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    With Target
        .Value = Replace(.Value, " ", "")
        .Value = Mid(.Value, 1, 4) & " " & Mid(.Value, 5, 4) & " " & Mid(.Value, 9, 4) & " " & Mid(.Value, 13, 4) & " " & Mid(.Value, 17, 4) & " " & Mid(.Value, 21, 4)
    End With
oeps:
    Application.EnableEvents = True
End Sub

Ik heb ondertussen zelf uitgevist dat dit zou kunnen door dit toe te voegen:

.Value = Replace(.Value, "be", "BE")

Maar dan zou het natuurlijk enkel werken met be (hoewel dat in de praktijk alleen nodig is). Hoe zou ik dit echter doen via hoofdletters?

 

[HSV]

In #9 staat het voor Nederlandse IBAN nummers.
Kleine aanpassing voor de belgen.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, y As Long, check As Boolean
If Not Intersect(Target, Range("a1:a11")) Is Nothing And Target.Count = 1 Then
 With CreateObject("vbscript.regexp")
            .ignorecase = True
            .Pattern = "^[A-Z]{2}[0-9]{14}$"
               check = .test(Target.Value)
        End With
 Application.EnableEvents = False
 If check Then
      For i = 5 To Len(Target) Step 4
         Target.Characters(i + y, 0).Insert (" ")
         y = y + 1
       Next i
    Else
         MsgBox "Verkeerde invoer;" & vbLf & vbLf & _
           "Dit moet zijn:  2 letters gevolgd door 14 cijfers.", vbInformation, "Let op"
        Application.Undo
    End If
    Target = UCase(Target)
   Application.EnableEvents = True
 End If
End Sub