VBA-Excel-Code zum automatischen Formatieren von Text (Kreditkartennummern) basierend auf der Länge der Zeichenfolge?

Question

Hintergrund:

1. In Excel werden in Zahlenformatzellen Ziffern nach der 15. in Nullen geändert (Grund). Dieses Verhalten betrifft die Aufzeichnung von Kreditkartennummern, die länger als 15 Ziffern sein können. Eine Möglichkeit, dieses Verhalten in Excel zu umgehen, besteht darin, Zellen, die Kreditkartennummern enthalten (vor der Eingabe der Daten), als Zeichenfolgen statt als Zahlen zu formatieren.
2. Um diese Zeichenfolge in einem benutzerfreundlicheren Format anzuzeigen, füge ich eine Hilfsspalte hinzu, die die Zeichenfolge aufnimmt und in Abschnitte unterteilt.
3. Dazu teste ich die Länge der Zeichenfolge. Wenn sie 16 Zeichen hat, teile ich sie in 4 4 4 4 (Canadian VISA/MC) auf. Wenn sie 15 Ziffern hat, teile ich sie in 3 6 5 (Canadian Amex) auf.
   

Frage:
Können Sie meinen Arbeitsblattcode für VBA anpassen, um bei jeder Datenänderung dynamisch das gewünschte Format auf die Spalte mit meinen Kreditkartenzeichenfolgen anzuwenden?

Answer 1

Erstens müssen Sie unbedingt Textformat in Ihrer Spalte haben B. Denn sonst erledigt Excel seine Arbeit vor diesem Code, sodass der Code die Arbeit nicht zuverlässig erledigen kann.

Anschließend müssen Sie das VBA-Fenster öffnen und das Modul des Arbeitsblatts öffnen, das Sie automatisch aktualisieren möchten. Fügen Sie in dieses Modul diesen Code ein:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  Dim rIntersect As Range
  On Error GoTo errH
  Application.EnableEvents = False
  Set rIntersect = Intersect(Me.Range("Table1[Text]"), Target)
  If Not rIntersect Is Nothing Then
    For Each c In rIntersect
      If IsNumeric(c.Value) Then
        If Len(c.Value) = 14 Then
          c.Value = Format(c.Value, "@@@ @@@@@@ @@@@@")
        ElseIf Len(c.Value) = 16 Then
          c.Value = Format(c.Value, "@@@@ @@@@ @@@@ @@@@")
        End If
      End If
    Next
  End If
errH:
  Application.EnableEvents = True
End Sub

^{(Danke,@Ron, zur Lenkung auf die FormatFunktion.)}

Drittens: Aktualisieren Sie den Tabellennamen Table1im Code mit Ihrem tatsächlichen Tabellennamen.

Worksheet_Changeist derEreignisdas jedes Mal ausgelöst wird, wenn sich etwas im Arbeitsblatt ändert. In diesem Code stellen wir zunächst sicher, dass sich genau in der Spalte mit der Kreditkartennummer etwas geändert hat, und erst in diesem Fall beginnen wir mit der Überprüfung der Länge und der Änderung der Werte.