Office: VBA - CBO - Eingabe nur in festgelegtem Format ermöglichen

Hallo,
ich habe mal wieder keinen blassen Schimmer, und im WWW finde ich auch keinen Zündfunken.

Was ich möchte ist bestimmt möglich, nur wie aufwändig wird das ganze?
Wenn jemand die Lösung hinbekommen könnte, es aber zu viel Arbeit ist, dann bitte sagen, denn dann weiß ich wenigstens das es zu aufwändig ist und überlege mir Alternativen.


gegeben:

- 2 Comboboxen (AktiveX-Steuerelemente die direkt auf einem Sheet liegen)
- mit diesen CBOs werden Lagerorte eingegeben, je nachdem per Tastatur oder per Auswahl aus dem Drop-Down der CBO.

- die Lagerorte bestehen entweder aus zwei Buchstaben und vier 2-stelligen Zahlen, oder nur aus vier 2-stelligen Zahlen
- diese 4 bzw. 5 Zweiergruppen sind jeweils durch ein Minuszeichen getrennt.

Bsp: AB-01-01-01-01 oder 01-01-01-01



gesucht:

Um Eingabefehler zu vermeiden und die Lagerorte (wegen weiterführenden Makros und Abfragen bzw. Funktionen) immer in exakt diesen beiden Formatierungen zu haben, möchte ich folgendes:

entweder:

- die Minuszeichen/Bindestriche während der Eingabe automatisch einfügen (sprich nach dem 2. Buchstaben bzw. jeweils der 2. Zahl per Makro über das (?) Change-Ereignis)

- Leerstellen verhindern (Replace im Change-Ereignis funktioniert leider nicht oder meine Syntax ist falsch)

Code:

ComboBox1.Replace What:=" ", Replacement:=""

- alles außer Buchstaben und Zahlen verhindern

- Buchstaben nur in der ersten Zweiergruppe zulassen, danach nur Zahlen

- kleine Buchstaben in große umwandeln

- nach der 4. Zahlengruppe keine weitere Eingabe zulassen, und auch kein Minuszeichen einfügen



oder:

Alternativ könnte beim Verlassen der Box eine Abfrage erfolgen, ob das Format ins Schema passt. Bei Fehler halt eine MessageBox mit Hinweis und den Fokus zurück in die CBO (Die letzten beiden Dingen bekomme ich hin , nur die Abfrage nicht.) ;-)
Wäre möglicherweise erheblich einfacher?



Ich hoffe ich habe nichts vergessen, und ich hoffe Ihr versteht was ich meine.
Bin gespannt ob und wie das zu lösen ist.

 

Hallo Achim,
die Striche werden automatisch eingefügt sobald das nächste Zeichen eingegeben wird.

Code:

Option Explicit

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Len(Application.Substitute(ComboBox1, "-", "")) Mod 2 = 0 And Len(ComboBox1) <> 0 Then ComboBox1 = ComboBox1 & "-"
End Sub

Gruß Hajo

 

Moin Hajo,

lieben Dank für die frühe Hilfe.

Dein Code klappt im Prinzip, die Striche werden nach jeder 2. Stelle eingefügt.

Jetzt muss ich nur noch die anderen Bedingungen in den Griff bekommen.

- momentan sind Leerstellen noch möglich und werden auch von deinem Code als Stelle mitgezählt
- die Gesamtlänge der Eingabe ist nicht begrenzt
- wenn im DropDown der CBO ein Lagerort aufgeführt ist, der mit dem gleichen Zeichen anfängt wie das erste was ich eingebe, so füllt sich die CBO mit dem Ort aus der Liste, fügt einen Strich hinten an und schreibt dann mit dem 2. Zeichen welches ich eingebe weiter.
Bsp:
AB-01-01-01-01 in der Liste vorhanden
ich gebe "A" ein: CBO = AB-01-01-01-01
ich gebe "B" ein: CBO = AB-01-01-01-01-B

usw.


Leider verstehe ich Deinen Code nicht komplett:

Code:

Then ComboBox1 = ComboBox1 & "-" 

ist klar

Code:

Len(ComboBox1) <> 0

wenn "LEN" = Länge ist, ist mir das auch klar

aber:

Code:

Len(Application.Substitute(ComboBox1, "-", "")) Mod 2 = 0

was macht die "Application.Substitute" und welche Parameter werden da in den Klammern angegeben?
und was bedeutet "Mod 2 = 0" ?

 

Hallo Achim,

die Zeichenfolge enthält "-" die Anzahl ist unklar, die Länge muss aber durch 2 Teilbar sein darum werden die "-" durch nichts ersetzt.
Ich hatte das Beisopiel nur auf die schnelle nachgebaut nur Neueingaben. Ein Nach des kompletten Beispiel sieht bestimmt anders aus als das Original. Darum baue ich das nicht nach.

Gruß Hajo

 

Re: VBA - CBO - Eingabe nur in festgelegtem Format ermöglich

Ich bin eigentlich kein großer Fan von Manipulationen der User-Eingaben, weil das zu oft mehr Verwirrung und Komplikationen schafft, als daß es die Sache einfacher macht. Ich würde von daher eigentlich lieber deine 2. Variante (Prüfung nach abgeschlossener Eingabe) verfolgen. Aber im Prinzip müßte folgender Code das eigentlich so ziemlich alles schaffen:

Code:

Option Explicit

Dim S2alt As String

Private Sub ComboBox1_Change()

    Const zulaessig1 = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Const zulaessig2 = "0123456789"
    Const zulaessig3 = "-"

    Dim S1 As String
    Dim S2 As String
    Dim L As Long
    Dim N As Long
    Dim Z As String
    Dim C As String
    Dim P As Long

    S1 = ComboBox1.Text
    'Leerzeichen entfernen, in Großbuchstaben wandeln, auf 14 Zeichen Länge begrenzen
    S2 = Left$(Trim$(UCase$(S1)), 14)

    'automatisch - anfügen, wenn Position erreicht
    L = Len(S2)
    If (L = 2) Or (L = 5) Or (L = 8) Or (L = 11) Then
        S2 = S2 & "-"
    End If

    'auf gültige Zeichen überprüfen
    N = 1
    While (N <= Len(S2))
        Select Case N
            Case 1, 2:
                'Buchstaben und Ziffern erlaubt
                Z = zulaessig1
            Case 4, 5, 7, 8, 10, 11, 13, 14:
                'nur Ziffern erlaubt
                Z = zulaessig2
            Case 3, 6, 9, 12:
                'nur - erleubt
                Z = zulaessig3
        End Select
        C = Mid$(S2, N, 1)
        P = InStr(Z, C)
        If (P < 1) Then
            'wenn ungültiges Zeichen, dann restaurieren des vorherigen Wertes
            S2 = S2alt
            N = Len(S2)
        End If
        N = N + 1
    Wend

    S2alt = S2
    If (S2 <> S1) Then
        'wenn (automatische) Änderung, dann aktuellen Wert in ComboBox und Hilfsvariable speichern
        ComboBox1.Value = S2
    End If

End Sub

Mindestens ein Problemfall bei der Eingabe könnte den Anwender verwirren: Wenn nachträglich ein Tippfehler korrigiert werden soll, verhält sich das Ding... ähm... nunja... merkwürdig. Zum Beispiel:
* Die Eingabe "AB-12-34-56-78" wurde gemacht und als gültig auch nicht weiter angemeckert.
* Jetzt stellt sich aber heraus, daß die "34" da drin doch eigentlich eher eine "39" werden sollte.
* Geht man jetzt mit dem Cursor vor die "4" und drückt "Entf", dann... merkt das Makro sofort, daß der "-" an einer Stelle steht, wo eigentlich nur eine Ziffer zugelassen ist. Folglich wird der vorherige Zustand wiederhergestellt und die "4" steht wieder da.
* Gleiches Problem entsteht natürlich auch, wenn man hinter der "4" die "<--" (Backspace) Taste benutzt.

Solange es nur um eine Ziffer geht, gibt es noch einen Workaround: Die Ziffer markieren und ohne Löschen direkt übertippen. Bei 2 Zeichen muß man das jeweils einzeln machen. Die "34" in eine "43" ändern wird also schon etwas aufwändiger.

Wie gesagt: Ist nicht so schön, die User-Eingaben direkt zu manipulieren, aber bis zu einem gewissen Grad geht es.

BTW: Ist es denn wirklich sinnvoll, die ComboBox mit einer Liste von gültigen Werten zu versorgen, aber trotzdem auch Werte außerhalb der Liste zuzulassen, sofern sie nur das Format erfüllen?

Man könnte das Makro übrigens auch erweitern, daß es neue, formell gültige, Werte an die Füll-Liste anhängt...

Gruß, Michael

 

Hallo Hajo,

danke für die Erklärung.
Ich erwarte auch nicht das Du ins blaue hinein etwas nachbaust.
Ein Tipp welche Funktion oder Application, am besten welche genaue Syntax man verwenden könnte reicht doch vollkommen.
Den Rest kann ich mir selber zurechtstricken (solange ich den Code halt verstehe).

Habe weitergebastelt und bin jetzt soweit:

- es wird erkannt ob die erste Stelle ein Buchstabe ist, wenn ja wird die 2. Stelle auch für Buchstaben erlaubt und die MaxLength auf 14 gesetzt. Wenn nein sind nur Zahlen erlaubt und die MaxLenght ist 11
- die Befüllung aus der DropDownListe macht keinen Ärger mehr, habe die MachtRequiered-Eigenschaft auf "false" gesetzt.




Bis auf zwei Dinge ist es jetzt eigentlich fertig:

- anstatt per Ascii-Case auf Zahlen zu prüfen und die Buchstaben mit "Case Else" abzutuen, würde ich gerne expliziet auf Buchstaben prüfen.
Kann mir jemand die Bereiche der großen und kleinen Buchstaben nennen? (finde im Netz gerade nicht das passende, zuhause habe ich es evtl.)

- wenn ich auf den ersten beiden Stellen Buchstaben eingebe, und dann einfach nochmal auf einen Buchstaben drücke, wird dieser zwar "KeyAscii = 0" gesetzt, der Bindestrich wird aber trotzdem eingefügt. So erhalte ich eine Reihe von Bindestrichen. Jemand ne Idee wie ich das unterbinde oder den Strich dann jedesmal wieder entferne?

Code:

Private Sub ComboBox6_LagerortZugang_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    Select Case KeyAscii
        Case 116: KeyAscii = 84     'wandelt t in T
        Case 118: KeyAscii = 86     'wandelt v in V
    End Select
    
    'Abfrage ob 1. Stelle ein Buchstabe
    If Len(ComboBox6_LagerortZugang) = 0 Then
        Select Case KeyAscii
            Case 48 To 57   'Zahlen
                ComboBox6_LagerortZugang.MaxLength = 11
                b = 0
            Case Else
                ComboBox6_LagerortZugang.MaxLength = 14
                b = 1
        End Select
    End If
    'wenn 1. Stelle eine zahl, dann 2. Stelle auch
    If Len(ComboBox6_LagerortZugang) = 1 And b = 0 Then
        Select Case KeyAscii
            Case 48 To 57
            Case Else
                KeyAscii = 0
        End Select
    End If
    
    'nach der 2. Stelle nur noch Zahlen erlauben
    If Len(ComboBox6_LagerortZugang) >= 2 Then
        Select Case KeyAscii
            Case 48 To 57
            Case Else
                KeyAscii = 0
        End Select
    End If
    
    'nach jeder 2. Stelle ein "-" einfügen
    If ComboBox6_LagerortZugang.MaxLength = 11 Then
        If Len(Application.Substitute(ComboBox6_LagerortZugang, "-", "")) Mod 2 = 0 And Len(ComboBox6_LagerortZugang) <> 0 And Len(ComboBox6_LagerortZugang) < 11 Then
            ComboBox6_LagerortZugang = ComboBox6_LagerortZugang & "-"
        End If
    Else
        If ComboBox6_LagerortZugang.MaxLength = 14 Then
            If Len(Application.Substitute(ComboBox6_LagerortZugang, "-", "")) Mod 2 = 0 And Len(ComboBox6_LagerortZugang) <> 0 And Len(ComboBox6_LagerortZugang) < 14 Then
                ComboBox6_LagerortZugang = ComboBox6_LagerortZugang & "-"
            End If
        End If
    End If
    
End Sub

Hallo miriki,

ganz lieben dank auch Dir für Deine Hilfe.
Habe deinen code mal überflogen und werde ihn mir heute abend in ruhe anschauen.
Wie es momentan aussieht habe ich ja eine Lösung (bis auf 2 Dinge).

In diesem Fall macht es für unseren Ablauf Sinn:
gelistet sind alle Lagerorte an denen der Artikel aktuel gelagert ist, eingeben kann ich aber natürlich auch alle anderen.
Je nach dem was ich gerade benötige.

Ich habe mich bemüht Eingabefehler möglichst von vornherein zu unterbinden, und andererseits die Eingabe durch Vorgaben bzw. automatisches Ausfüllen und beschränken der Listen auf das wirklich benötigte zu vereinfachen.
Bisher bin ich zufrieden.

 

Oh, jetzt, wo Du's sagst... So explizit hatte ich die Regel nicht im Source. Mit etwas Kosmetik und dieser Erweiterung siehe unten...

Die ASCII-Codes der Ziffern liegen von 48 (0) bis 57 (9). Die Großbuchstaben fangen bei 65 (A) direkt hinter dem @ an und gehen dann bis 90 (Z). Die entsprechenden Kleinbuchstaben sind jeweils 32 Werte darüber, fangen also bei 97 direkt hinter dem ' an und gehen bis 122.

Das Problem dabei ist, daß man sich irgendwann leicht mal verzettelt, weil es immer noch eine Situation gibt, die man nicht abgefangen hat. Ein Beispiel, bei dem es problematisch wird, hatte ich ja schon angeführt. Das Einfügen über's Clipboard ist sicherlich auch nicht unproblematisch, je nachdem, ob nur ein oder mehrere Zeichen eingefügt werden.

Aber solange man mit dem zufrieden ist, was man sich hingewurschtelt hat, ist es auch völlig ok.

Gruß, Michael

Code:

Option Explicit

Dim S2alt As String

Private Sub ComboBox1_Change()

    Const Z1 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Const Z2 = "0123456789"
    Const Z3 = "-"

    Dim S1 As String
    Dim S2 As String
    Dim L As Long
    Dim N As Long
    Dim Z As String
    Dim C As String
    Dim P As Long
    Dim A As Boolean

    S1 = ComboBox1.Text
    'in Großbuchstaben wandeln
    S2 = UCase$(S1)

    'automatisch - anfügen, wenn Position erreicht
    L = Len(S2)
    If (L = 2) Or (L = 5) Or (L = 8) Or (L = 11) Then
        S2 = S2 & "-"
    End If

    'auf gültige Zeichen überprüfen
    N = 1
    While (N <= Len(S2))
        Select Case N
            Case 1:
                'Buchstaben und Ziffern erlaubt
                Z = Z1 & Z2
            Case 2:
                If (A) Then
                    'wenn 1. Stelle Buchstabe, dann 2. auch
                    Z = Z1
                  Else
                    'sonst nur Ziffern in 2. Stelle
                    Z = Z2
                End If
            Case 4, 5, 7, 8, 10, 11, 13, 14:
                'nur Ziffern erlaubt
                Z = Z2
            Case 3, 6, 9, 12:
                'nur - erleubt
                Z = Z3
        End Select
        C = Mid$(S2, N, 1)
        P = InStr(Z, C)
        If (P < 1) Then
            'wenn ungültiges Zeichen, dann restaurieren des vorherigen Wertes
            S2 = S2alt
            N = Len(S2)
        End If
        If (N = 1) Then
            'Sonderbehandlung: 1. Stelle
            If (P <= 26) Then
                '- Buchstabe: 2. Stelle muß auch Buchstabe, max. Länge ist 14
                A = True
                S2 = Left$(S2, 14)
              Else
                '- Ziffer: 2. Stelle muß auch Ziffer, max. Länge 11
                A = False
                S2 = Left$(S2, 11)
            End If
        End If
        N = N + 1
    Wend

    S2alt = S2
    If (S2 <> S1) Then
        'wenn (automatische) Änderung, dann aktuellen Wert in ComboBox und Hilfsvariable speichern
        ComboBox1.Text = S2
    End If

End Sub

 

Danke für die Infos Miriki.

Die Geschichte mit den "-----" wenn ich zuviele Buchstaben drücke habe ich auch gelöst (siehe im Code ganz unten).

Deine Meinung zu dieser Art Reglementierung kann ich zwar verstehen, andererseits denke ich mir, wenn ich an der Quelle Fehler ausschließen kann....

Immerhin denke ich dass es sinnvoll wäre zumindest einen Teil Deines Codes noch mit ein zu bauen (LostFocus), als Abgleich ob die Eingabe dem Format entspricht.

Denn eigentlich funktioniert jetzt alles exakt so wie ich möchte (ausgiebig getestet), ausser... ja ausser ich wähle einen Eintrag aus der DropDownListe, setze den Cursor manuell irgendwo in die Mitte des Textes, lösche einen Teil des Eintrages und Tippe dann den Rest.
Dann, ja dann passieren seltsame Dinge.

Natürlich könnte man jetzt sagen, wer sowas macht muss halt die Augen offen haben.
Andererseits, wofür mache ich mir die ganze Arbeit wenn ich solche Lücken lasse?

Heut abend nochmal in Ruhe überlegen.


So siehts jetzt aktuell aus:

Code:

Private Sub ComboBox6_LagerortZugang_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii
        Case 116: KeyAscii = 84     'wandelt t in T
        Case 118: KeyAscii = 86     'wandelt v in V
    End Select
    
    'Abfrage ob 1. Stelle ein Buchstabe oder eine Zahl
    If Len(ComboBox6_LagerortZugang) = 0 Then
        Select Case KeyAscii
            Case 48 To 57   'Zahlen
                ComboBox6_LagerortZugang.MaxLength = 11
                b = 0
            Case 65 To 90, 97 To 122        '65 - 90 Großbuchstaben, 97 - 122 Kleinbuchstaben
                ComboBox6_LagerortZugang.MaxLength = 14
                b = 1
            Case Else
                KeyAscii = 0
        End Select
    End If
    
    'wenn 1. Stelle ein Buchstabe, dann 2. Stelle auch und sonst nichts
    If Len(ComboBox6_LagerortZugang) = 1 And b = 1 Then
        Select Case KeyAscii
            Case 65 To 90, 97 To 122        '65 - 90 Großbuchstaben, 97 - 122 Kleinbuchstaben
            Case Else
                KeyAscii = 0
        End Select
    End If
    
    'wenn 1. Stelle eine Zahl, dann 2. Stelle auch
    If Len(ComboBox6_LagerortZugang) = 1 And b = 0 Then
        Select Case KeyAscii
            Case 48 To 57   'Zahlen
            Case Else
                KeyAscii = 0
        End Select
    End If
    
    'nach der 2. Stelle nur noch Zahlen erlauben
    If Len(ComboBox6_LagerortZugang) >= 2 Then
        Select Case KeyAscii
            Case 48 To 57   'Zahlen
            Case Else
                KeyAscii = 0
        End Select
    End If
    
    'nach jeder 2. Stelle ein "-" einfügen
    If ComboBox6_LagerortZugang.MaxLength = 11 Then
        If Len(Application.Substitute(ComboBox6_LagerortZugang, "-", "")) Mod 2 = 0 And Len(ComboBox6_LagerortZugang) <> 0 And Len(ComboBox6_LagerortZugang) < 11 Then
            ComboBox6_LagerortZugang = ComboBox6_LagerortZugang & "-"
        End If
    Else
        If ComboBox6_LagerortZugang.MaxLength = 14 Then
            'verhindern das nach dem 2. Buchstaben durch drücken weiterer Buchstaben "-" eingefügt wird
            If Len(ComboBox6_LagerortZugang) = 2 Then
                Select Case KeyAscii
                    Case 48 To 57   'Zahlen
                        If Len(Application.Substitute(ComboBox6_LagerortZugang, "-", "")) Mod 2 = 0 And Len(ComboBox6_LagerortZugang) <> 0 And Len(ComboBox6_LagerortZugang) <14> 2 Then
                    If Len(Application.Substitute(ComboBox6_LagerortZugang, "-", "")) Mod 2 = 0 And Len(ComboBox6_LagerortZugang) <> 0 And Len(ComboBox6_LagerortZugang) < 14 Then
                        ComboBox6_LagerortZugang = ComboBox6_LagerortZugang & "-"
                    End If
                End If
            End If
        End If
    End If
    
End Sub

 

Moin,
wollte nochmal kurz Rückmeldung geben.

Hajo, mit Deinem Code hast Du mir den entscheidenden Zündfunken gegeben.
Danke dafür.

Miriki, Deinen Code habe ich gestern mal kurz laufen lassen.
Da wäre noch einiges an Feinschliff nötig, nicht alles klappt so wie gewünscht.
Leider hatte ich vergessen mir die Datei von der Arbeit nach Hause zu schicken und konnte deshalb gestern Abend nichts mehr ausprobieren.
Deshalb kann ich momentan auch nicht beurteilen welche Lösung mir besser gefällt oder in meinen Augen sinnvoller erscheint.

Aber ganz lieben Dank für Deine Zeit und Deine Anregungen.



Da ich an anderer Stelle eh eine Abfrage habe ob und wie alle Boxen ausgefüllt sind, habe ich mich entschieden die letzten probleme einfach so zu lösen:

Code:

Private Sub ComboBox6_LagerortZugang_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    'mit TAB-Taste zur nächsten Box springen
    If KeyCode = 9 Then
        <blablabla>
    Else
        If ComboBox6_LagerortZugang.MaxLength = 14 And Len(ComboBox6_LagerortZugang) = 14 Then
            KeyCode = 0
            ComboBox6_LagerortZugang = ""
        Else
            If ComboBox6_LagerortZugang.MaxLength = 11 And Len(ComboBox6_LagerortZugang) = 11 Then
                KeyCode = 0
                ComboBox6_LagerortZugang = ""
            End If
        End If
    End If

End Sub

(Wenn die Box komplett ausgefüllt ist, und dann nochmal eine Taste gedrückt wird, dann leert sie sich, ausser bei der TAB-Taste)

Ich habe einiges getestet und es nicht mehr geschafft einen Fehler zu produzieren.

Somit ist die nächste Hürde auf dem Weg zum Ziel genommen.
Danke Euch.