Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Werkzeug
BildScreenshot zu Werkzeug Werkzeug-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Userform
BildScreenshot zu Userform Userform-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

Datengültigkeit

Betrifft: Datengültigkeit von: Blaumann
Geschrieben am: 01.10.2014 18:59:14

Hallo,

bin neu hier und hoffe alles richtig zu machen.

Ich habe für eine Zelle die Datengültigkeit eingerichtet, und habe jetzt ein Problem mit der Liste dafür.

Die liste kommt aus Spalte G aus der zweiten Tabelle, das wäre auch kein Problem, wenn da nicht der Aufbau der Spalte wäre.

Zum einen gibt es Leerzeilen, dann ist es eine Namensliste, der Namen kann aber aus Titel, Vornamen, Nachnamen bestehen aber auch nur aus Vornamen und Nachnamen.
zB.
Peter Klein
Dr.Werner Gross
Peter-Paul Mever
Karl Schmidt-Meyer
Dr.Hans-Peter Ludwig-Müller

Die Auswahlliste sollte jetzt ohne Leerzeilen und sortiert nach Nachnamen erscheinen, wenn ich aber ausgewählt habe, soll der Name in der Zelle wieder in der richtigen Reihenfolge stehen.

Man sagte mir das es hier eine VBAlösung gebe,ich konnte aber nichts finden. Das liegt aber vielleicht daran das ich außer den Editor sonst keine Ahnung von VBA habe und vielleicht falsch gesucht habe.

Könnt ihr mir bitte helfen.

Viele Grüsse Peter

  

Betrifft: AW: Datengültigkeit von: {Boris}
Geschrieben am: 01.10.2014 21:33:06

Hi Peter,

ich hab Dir mal das Prinzip gebaut - siehe Anlage. Deine Originaldaten stehen dabei in Spalte B und werden in den Spalten A, C und D "verformelt".
Danach hab ich die Spalten A:D nach Spalte A aufsteigend sortiert.
Schau Dir auch den vergebenen dynamischen Namen "Nachnamen" an - ebenso das Ereignismakro für die gelbe Zelle E2 (Change-Ereignis).

https://www.herber.de/bbs/user/92936.xlsm

VG, Boris


  

Betrifft: Kleiner Nachtrag von: {Boris}
Geschrieben am: 01.10.2014 21:35:24

Ich hatte das Change-Ereignis ohne Fehlerbehandlung programmiert - MIT ist es aber besser:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler
If Target.Address(0, 0) = "E2" Then
    Application.EnableEvents = False
    Target = WorksheetFunction.VLookup(Target, Range("A:B"), 2, 0)
End If
Err_Handler:
Application.EnableEvents = True
End Sub
VG, Boris


  

Betrifft: AW: Kleiner Nachtrag von: Blaumann
Geschrieben am: 02.10.2014 11:13:38

Hallo Boris,

vielen Dank für deine Mühe, das geht auch in die richtige Richtung nur kann ich das leider nicht anwenden, da ich in Tabelle2 keine Änderungen vornehmen kann, da diese dauernd aktualisiert wird.

Das ist auch einer der Gründe warum da Leerzeilen vorhanden sind.

Wie oben schon erwähnt, suche ich eine Lösung die völlig ohne Hilfsspalten und Formeln auskommt

Man sagte mir, das es hier im Forum mal eine Lösung gab, nur finde ich nichts, das mag aber auch daran liegen, das ich vielleicht mit falschen Suchbegriffen gesucht habe.

Trotzdem noch mal vielen Dank für deine Mühe.

Gruss Peter


  

Betrifft: AW: Das sollte machbar sein... von: Ewald
Geschrieben am: 02.10.2014 23:21:29

Hallo Peter,

..jedoch braucht es mehr Info.

Wie heißt die Tabelle mit der Datenüberprüfung
Welche Zelle hat die Datenüberprüfung
Wie heißt die Tabelle wo die Namen stehen
In welchem Bereich stehen dort die Namen
Bleiben die Tabellennamen immer gleich

Gruß Ewald


  

Betrifft: AW: Das sollte machbar sein... von: Blaumann
Geschrieben am: 03.10.2014 00:08:24

Hallo Ewald,

gut das ich reingeschaut habe, dachte schon ich müsste durch das lange Wochenende bis nächste Woche warten.

Also

Tabelle1 heißt Testformular und die Datenüberprüfung ist in Zelle B3

Tabelle2 heißt Prüfdaten und die Namen befinden sich in Spalte G von Zeile2 bis Zeile 101, nur wie gesagt sind nicht immer alle Zeilen belegt.

Die Tabellennamen bleiben immer gleich, möchte nur nochmals erwähnen das ich in der Tabelle Prüfdaten nichts verändern darf.

Gruss Peter


  

Betrifft: AW: Schau mal ob das paßt... von: Ewald
Geschrieben am: 03.10.2014 00:59:29

Hallo Peter,

.. und deinen Vorstellungen entspricht.

https://www.herber.de/bbs/user/92954.xlsm

Gruß Ewald


  

Betrifft: AW: Schau mal ob das paßt... von: {Boris}
Geschrieben am: 03.10.2014 08:29:22

Hi Ewald,

das Trennen von Vor- und Nachname ist aber nicht so trivial.

Karl Maria von Weber
Dr. Franz Meier
Paul Heinz Müller
usw...

Da bedarf es einer umfassenderen Prüfung. Nicht umsonst sind die Formeln in meiner Beispielmappe da recht komplex ;-)

Ansonsten ist Dein Vorschlag aber natürlich gut.

VG, Boris


  

Betrifft: AW: Nicht für beliebige Namenstrennung... von: Ewald
Geschrieben am: 03.10.2014 13:35:17

Hallo Boris,

..nutzbar. Da hast du recht, ich habe mich an den Beispielnamen orientiert.

Hier ist eine gewisse Strüktur zu erkennen:

Titel enden mit Punkt

Mehrfachnamen(sowohl Vornamen und Nachnamen)sind - getrennt

Vorname und Nachname sind durch Leerzeichen getrennt.

Ohne eine gewisse verwendete Struktur geht es aber nicht.

Namen die eine bestimmte Reihenfolge nicht einhalten, keine Trennzeichen und kein Erkennungsmerkmal beinhalten,lassen sich sowieso nicht immer richtig trennen.

Gruß Ewald


  

Betrifft: Namens(trennungs)logik von: {Boris}
Geschrieben am: 03.10.2014 16:25:01

Hi Ewald,

eigentlich muss man nur nach dem LETZTEN vorkommenden Leerzeichen trennen - es sei denn, es existiert ein Adelszusatz wie von, zu, ob, van, de, auf, vom usw.. - dann muss man eben direkt vor diesem Zusatz trennen. Nach dieser Logik verfährt die Formel die ich verwendet hatte. Damit erschlägt man (in Deutschland) mindestens 99,9% aller Namen.

VG, Boris


  

Betrifft: AW: Namens(trennungs)logik von: Ewald
Geschrieben am: 03.10.2014 18:40:34

Hallo Boris,

das ist auch in VBA kein Problem

Right(str, Len(str) - InStrRev(str, " "))

nur paßt das leider nicht immer, wie du schon erwähnt hast die alten Adelstitel, aber auch das Weglassen des Bindestrichs bei Doppelnamen.(obwohl rechtlich nicht zulässig)

Hast du dann noch Ausländische Namen wo Doppelnamen ohne Bindestrich eigentlich die Regel sind, paßt es nicht mehr.

Hier ist es dann schon wichtig welche Struktur die Namen im bestimmten Fall haben.

Beim Vornamen ist es ja ähnlich und auch beim Titel (ob nur der erste Titel genommen wird, oder auch mehrere).

Gruß Ewald


  

Betrifft: AW: Namens(trennungs)logik von: Blaumann
Geschrieben am: 03.10.2014 19:43:43

Hallo Ewald,

ich bin begeistert, so habe ich mir das vorgestellt.

Da ich aber VBA-Neuling bin, kannst du mir erklären, wie ich das jetzt in meine Datei bekomme.

Aber schon mal vielen Dank für deine Hilfe.

Gruss Peter


  

Betrifft: AW: Namens(trennungs)logik von: Ewald
Geschrieben am: 03.10.2014 23:33:12

Hallo Peter,

das sollte dir doch gelingen,

öffne meine Beispieldatei und minimiere sie, öffne dann deine Datei.

mit Alt+F11 öffnest du jetzt den Explorer.

Auf der linken Seite siehst du jetzt die zwei Projekte (meine Beispieldatei und deine Datei.

Falls ein Projekt nicht ganz geöffnet ist, klicke ganz links auf das Plus-Kästchen

Das Modul1 kannst du einfach per DragandDrop in dein Projekt ziehen.

Klicke jetzt doppelt auf "Diese Arbeitsmappe" in meiner Beispielmappe. Markiere auf der rechten Seite den kompletten Code und rechte Maustaste Kopieren.

Klicke jetzt doppelt auf "Diese Arbeitsmappe" in deiner Datei und füge auf der rechten Seite (rechte Maustaste Einfügen) den Code ein.

Nun kannst du den Editor schließen.

Speichere deine Datei und schließe sie. anschließend noch die Beispielmappe ohne Speichern schließen.

Nun kannst du deine Datei öffnen und testen ob alles funktioniert.

Falls etwas nicht funktioniert oder sonstige Fragen auftauen, einfach melden.

Gruß Ewald


  

Betrifft: AW: Namens(trennungs)logik von: {Boris}
Geschrieben am: 03.10.2014 23:13:27

Hi Ewald,

das letzte Leerzeichen zu finden ist ja easy. Du musst aber - gerade für die Adligen - noch die separate Abfrage machen.
Die von mir gepostete Formel beinhaltet schon einigen Hirnschmalz - und funktioniert für DEUTSCHE Namen eben in 99,9% aller Fälle.
Bau sie doch einfach in Dein VBA-Script ein - dann ist doch alles gut!

VG, Boris


  

Betrifft: AW: Nun ja... von: Ewald
Geschrieben am: 04.10.2014 01:00:35

Hallo Boris,

..Excelformeln läßt grüßen.

aber das muß ich mir noch überlegen, denn es müssen alle mögliche Nachnamenvorsätze enthalten sein und das größere Problem ist dann die Sortierung.

Die DIN 31638 geht davon aus das Nachnamenvorsätze nicht zur Sortierung herangezogen werden.

Einen Johann Wolfgang von Goethe wirst du wohl nie unter v finden, Excel würde dies aber dann tun.

Dann gibt es wohl noch eine Regel, das bei Großschreibung (Von) auch nach diesem sortiert wird.

Deshalb zögere ich noch, zu sagen, mit einer Funktion alle möglichen Nachnamen richtig wiedergeben zu können.

Aus diesem Grund tendiere ich daher im Moment, eher noch auf die vom User definierte Form seiner Namenseinträge zu reagieren.

Gruß Ewald

es müssen alle


  

Betrifft: AW: Nun ja... von: Blaumann
Geschrieben am: 05.10.2014 11:18:06

Hallo,

habe es tatsächlich hinbekommen und es klappt auch alles.

Eine Kleinigkeit hätte ich noch, wenn ich die Datengültigkeit aufklappe, sehe ich nur ein paar Einträge und muss dann doch sehr viel scrollen.

Wie kann ich das ändern, das mehr angezeigt werden.

Gruss Peter


  

Betrifft: AW: Nun ja... von: Ewald
Geschrieben am: 05.10.2014 23:48:46

Hallo Peter,

mit dem Zellendropdown ist da nichts zumachen, die zeigt immer nur 8 Einträge an.

Da ja in den Tabellen nichts verändert werden soll,wäre eine Userform mit einer Listbox das richtige.

Kannst ja erstmal in meiner Beispieldatei testen,ob du es hinbekommst.

In den VB-Editor gehen, Einfügen/Userform

Jetzt Rechtsklick auf die Userform/Eigenschaften

Height 520
Width 170

Eigenschaftenfenster schließen,auf die Userform klicken
das kleine Fenster Werkzeugsammlung erscheint.dort mit der Maus über die Symbole gehen bis Listenfeld erscheint(sollte ganz oben rechts sein). Darauf klicken und dann in die Userform einfügen. Das Listenfeld so ziehen,das die ganze Userform ausgefüllt ist.
Jetzt Rechtsklick auf das Listenfeld/Eigenschaften

Dort bei Font die Schriftart,Schriftgröße,Schriftschnitt wählen. (10,Fett,wäre ein Vorschlag)
Eigenschaftenfenster schließen, Rechtsklick im linken Fenster auf die Userform/Code anzeigen.

dort dann auf der rechten Seite folgenden Code einfügen

Private Sub ListBox1_Click()
ActiveSheet.Range("D3").Value = ListBox1.Value
Unload UserForm1
End Sub
nun mußt du in "Diese Arbeitsmappe" (das kennst du ja schon)

dort gibt es zwei Codes
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
diese,aber nur diese komplett löschen.

Dann fügst du dort folgenden Code ein
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x As String
If ActiveSheet.Name <> "Testformular" Then Exit Sub
If Target.Address(0, 0) = "B3" Or Target.Address(0, 0) = "D3" Then
        x = VNwechsel(Target.Value)
      Application.EnableEvents = False
        Target.Value = x
     Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name <> "Testformular" Then Exit Sub
If Target.Address(0, 0) = "B3" Then
    Dim arr As Variant
   
    arr = DDliste(Sheets("Prüfdaten").Range("PUR"))
 With Range("B3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(arr, ",")
        .InCellDropdown = True
    End With
End If
If Target.Address(0, 0) = "D3" Then
   
    arr = DDliste(Sheets("Prüfdaten").Range("PUR"))
    With Range("D3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(arr, ",")
        .InCellDropdown = False
    End With
    UserForm1.ListBox1.List = arr
    UserForm1.Left = ActiveCell.Left + (ActiveCell.Width + 20)
    UserForm1.Show
End If

End Sub
Den VB-Editor nun schließen.

Wenn du nun in Testformular in die Zelle D3 klickst, sollte die Userform erscheinen, bei Klick auf einen Namen in der Liste wird dieser in D3 eingetragen und die Userform geschlossen.

Gruß Ewald


  

Betrifft: AW: Namens(trennungs)logik von: Ewald
Geschrieben am: 06.10.2014 00:13:22

Hallo Boris,

habe in der Wikepedia gefunden, das bei Auflistungen von Namen Namenszusätze an den Vornamen angehängt werden. Sind Titel vorhanden, folgen die anschließend.

"Ursula van der Leyen" wird zu "Leyen Ursula van der"

Dies würde dann auch die richtige Sortierung in Excel ergeben.

Leider läßt sich das jetzt nicht mehr mit einer Funktion in beiden Richtungen erschlagen, es sei denn man gibt einen weiteren Parameter vor der die Richtung bestimmt.

Zum Testen mal zwei Subs

Sub TVN_TO_NVT()   'Titel/Vorname/Nachname nach Nachname/Vorname/Titel
Dim anzpoint
Dim mytit
Dim myvor
Dim mynach
Dim Ergebnis
Dim a
Dim b
a = ActiveCell.Value
anzpoint = InStrRev(a, ".")

    If anzpoint = 0 Then
        mytit = ""
        b = a
    Else
        mytit = Left(a, InStrRev(a, "."))
        b = Mid(a, InStrRev(a, ".") + 1, Len(a))
    End If

    myvor = Left(b, InStrRev(b, " "))
    mynach = Right(b, Len(b) - InStrRev(b, " "))
If anzpoint = 0 Then
Ergebnis = mynach & " " & myvor
Else
Ergebnis = mynach & " " & myvor & " " & mytit
End If
MsgBox Ergebnis
End Sub
Sub NVT_TO_TVN()     'Nachname/Vorname/Titel nach Titel/Vorname/Nachname
Dim anzpoint
Dim mytit
Dim myvor
Dim mynach
Dim Ergebnis
Dim a
Dim b
a = ActiveCell.Value
anzpoint = InStrRev(a, ".")
If anzpoint = Len(a) Then
    mytit = Right(a, Len(a) - InStrRev(a, " "))
    b = Left(a, Len(a) - Len(mytit))
    mynach = Left(b, InStr(b, " "))
    myvor = Mid(b, InStr(b, " ") + 1, Len(b) - (InStr(b, " ") + 1))
    Ergebnis = mytit & " " & myvor & " " & mynach
End If
If anzpoint = 0 Then
    mynach = Left(a, InStrRev(a, " "))
    myvor = Right(a, Len(a) - InStrRev(a, " "))
 Ergebnis = myvor & " " & mynach
 End If
 MsgBox Ergebnis
End Sub
Es werden auch Mehrfachtitel berücksichtigt, wenn die vorgeschriebene Schreibweise (titel1,Punkt,Titel2,Punkt) eingehalten wird.

Gruß Ewald


  

Betrifft: AW: Namens(trennungs)logik von: Blaumann
Geschrieben am: 07.10.2014 23:17:51

Hallo Ewald,

habe es in der Beispieldatei hinbekommen, muß es jetzt noch in meine Datei übertragen.

Die große Liste erleichtert die Auswahl erheblich.

Eine Frage habe ich noch, hat die Zelle D3 dann noch die Datenüberprüfung noch, wenn ich in die Zelle klicke, sehe ich den Pfeil dort nicht mehr.

Gruss Peter