Office: (Office 2016) Listenfeld aktualisieren

Hallo Forum,
kann mir jemand helfen?
Ich öffne eine UserForm mit Listenfeld und zwei Textboxen.
In die Textboxen werden Werte für einen neuen Eintrag in das Listenfeld eingefügt. Mittels Schaltfläche erfolgt der Übertrag in den Tabellenbereich, aus der das Listenfeld seine Daten bezieht.
Wie kann ich nach Hinzufügen eines Eintrages in das Tabellenblatt, das Listenfeld aktualisieren, sodass es sofort (und nicht erst nach Schließen und wieder Öffnen der Datei, die neuen Inhalte anzeigt?

Ganz nebenbei, wie kann ich hier Code als Code formatieren?

Private Sub UserForm_Initialize()

Dim lngZeile As Long
Dim lngZeileMax As Long
Dim lngz As Long

Me.Caption = "Angebotene Leistung Physiotherapie Frederic Baum"
lblDL = "Neue Dienstleistung hinzufügen"

With Me.lstDienstleistungen
.ColumnCount = 3
.ColumnWidths = "20;200;50"
.Font.Size = 10
End With

With Worksheets("NamensManager")
lngZeileMax = .Range("A" & .Rows.Count).End(xlUp).Row

For lngZeile = 2 To lngZeileMax

Me.lstDienstleistungen.AddItem .Range("A" & lngZeile).Value
Me.lstDienstleistungen.Column(1, lngz) = .Range("B" & lngZeile).Value
Me.lstDienstleistungen.Column(2, lngz) = Format(.Range("C" & lngZeile), "#,##0.00 €")

lngz = lngz + 1

Next lngZeile

End With

End Sub

Vielen Dank für Euren Einsatz

der Mampfred

 

geve der Liste eine Dynamischen Namen
=$E$12:INDEX($E$12:$BE$12;1;ZÄHLENWENN($E$12:$BE$12;">0"))
und weise der Listbox diesen zu.

 

Hallo Hajo_Zi,

sorry, dass ich mich erst jetzt melde, war aber 24 Stunden im Corona-Einsatz.

Ich verstehe deine Antwort nicht. Meine Liste, aus der die UserForm bzw. das Listenfeld ist ein Bereich, der als "intelligenteTabelle" --> tblDienstleistungen definiert ist und befindet sich im Tabellenblatt NamensManager A1:C19.
Wo soll dein dynamischer Name hin?

Ich möchte erreichen, dass mit einem VBA-Code nach dem Zufügen eines neuen Eintrages in das Listenfeld lstDienstleistungen der UserForm, dieses Listenfeld aktualisiert wird.
Zur Zeit muss ich die Datei schließen, neu starten und erst dann ist das Listenfeld im UserForm aktualisiert.
Muss ich jetzt den Code für die Initialisierung nochmal ablaufen lassen, oder geht das auch einfacher?

Viele liebe Grüße

der Mampfred

 

mein Vorschlag war falsc, da Name schon vorhnaden mit inteligenter Tabelle, den Du schon benutzt.
Zu Deinem Problem kann ich nicht schreiben.

Nur wenige schauen auf Deinen Rechner und sehen die Datei.
Ich möchte gerne den Fehler im Original sehen.

Ich baue keine Datei nach. Die Zeit hat schon jemand investiert.

Ein Nachbau sieht meist anders aus als das Original. <B>Darum sollte das Original verlinkt werden.</b>

Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
Hinweis - Anonymisierung und Pseudonymisierung von Beispielmappen - MS-Office-Forum
änderrn.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.

Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)3

Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.

Bilder statt Datei | Excel ist sexy!

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.

Das ist nur meine Meinung zu dem Thema.

Gruß Hajo

 

Hi,

der einfachste Weg überhaupt ist, den DataBodyRange der intelligenten Tabelle (bei mit "Tabelle1") direkt der ListBox zuzuweisen anstelle alle Zeilen einzeln zuzuweisen:

Code:

' Code im Codemodul des UserForms

Private Sub UserForm_Initialize()
    Me.Caption = "Angebotene Leistung Physiotherapie Frederic Baum"
    lblDL = "Neue Dienstleistung hinzufügen"
    With Me.lstDienstleistungen
        .ColumnCount = 3
        .ColumnWidths = "20;200;50"
        .Font.Size = 10
    End With
    Eintragen   '<==Makro für das Zuweisen des Datenbereichs ausführen
End Sub

Sub Eintragen()
    'den Datenbereich der intelligenten Tabelle der listBox zuweisen
    lstDienstleistungen.RowSource = Worksheets("NamensManager").ListObjects("Tabelle1").DataBodyRange.Address
End Sub

Indem man außerdem den Code für das Zuweisen der Daten an die ListBox auslagert, kann man nach dem Eintrag eines neuen Wertes ins Tabellenblatt das Makro Eintragen() völlig problemlos erneut aufrufen und der neue DataBodyRange wird der ListBox zugewiesen.

---

 

Hallo Karin,
danke für deine Mühe.
Deine Erläuterungen zum Auslagern der Zuweisung an die Listbox leuchtet mir ein.
DataBodyRange.Address hatte ich vorher noch nie gehört oder gelesen. (Man kann ja nicht alles kennen).
Ich habe meine Datei mal angehängt.
Mit deinem angezeigten Code wird das Listenfeld nicht befüllt. Anscheinend bin ich zu dumm, deinen Code zu interpretieren.

Viele liebe Grüße

Der Mampfred

 

Hi,

es ist auch logisch, dass nichts angezeigt wird, denn erst in deiner hochgeladenen Mappe ist erkennbar, dass sich deine intelligente Tabelle in einem anderen Tabellenblatt befindet - dessen Name musst du selbstverständlich vor die Adresse setzen.

Ich habe auch gleich mal deinen Code für das Eintragen der Daten in die intelligente Tabelle entsprechend überarbeitet.

Aber: ich habe feststellen müssen, dass das Erweitern der intelligenten Tabelle in deiner Mappe einen Automatisierungsfehler ("Das aufgerufene Objekt wurde von seinen Clients getrennt") auslöst - zumindest bei mir in Excel2016. Die Ursache liegt in solchen Fällen meist darin, dass die Mappe irgendwie korrumpiert ist. Ich habe deshalb die beiden relevanten Tabellenblätter sowie das UserForm in eine neue Arbeitsmappe kopieren müssen. Du kannst ja einfach die betreffenden Codes in deine Originalmappe übernehmen. Falls der Automatisierungsehler bei dir ebenfalls auftritt, musst du so vorgehen wie ich und die Mappe neu erstellen.

---

 

Hallo Karin,
erstmal danke für deine Hilfe. All zu viel Erfahrung mit VBA habe ich ja nicht, deshalb ist mein Code sicherlich an der einen oder anderen Stelle überholungsbedürftig. Viele Sachen kenne ich auch nicht. Trotzdem verstehe ich das verhalten der frmDienstleistungen nicht.
Beim Öffnen der Userform frmDienstleistungen werden ordnungsgemäß alle Inhalte angezeigt.
Jedoch, sobald ich eine neue Dienstleistung hinzufüge, beendet er Excel und öffnet ein neue leere Datei.
Wenn ich in Einzelschritten bei cmdHinzu_Click() durchgehe, bricht er bei

.DataBodyRange.Cells(.ListRows.Count, 1) = Application.Max(.DataBodyRange.Columns(1)) + 1

ab und beendet Excel.

Viele liebe Grüße

Der Mampfred

 

Hi,

ich kann nur raten, dass das generelle Problem darin liegt, dass hier zwischen UserForm und intelligenter Tabelle eine gewisse "Unverträglichkeit" besteht, da sozusagen zwischen diesen beiden Objekten hin und her gewechselt werden muss - weshalb und wieso entzieht sich meiner Kenntnis.
Ich habe die intelligente Tabelle mal in einen Bereich umgewandelt - jetzt sollte kein Fehler mehr auftreten.

---

 

Hallo Karin,

vielen lieben Dank für deine Mühen.
Ich habe das Tabellenblatt Namensmanager aus meiner Datei gelöscht, neu eingefügt und die Dienstleistungen neu eingetragen, aber diesmal wie du es geraten hast nicht mehr als intelligente Tabelle.
Dein Code funktioniert jetzt einwandfrei beim Hinzufügen. Klasse.

Jetzt habe ich aber eine Fehlermeldung beim Löschen eines Eintrages.
Eine nicht näher bezeichnete Fehlermeldung tritt auf.
An dieser Stelle beim Löschen, klinkt er sich aus.

lstDienstleistungen.RemoveItem (lstDienstleistungen.ListIndex)

Kannst du mir hier auch noch mal helfen?

Vielen Dank und liebe Grüße

der Mampfred

 

Hi,

da der Bereich jetzt als Zelladresse übergeben wird, kann nicht einfach eine Zeile daraus gelöscht werden - nach dem Löschen muss das Makro Eintragen() erneut ausgeführt werde.
Frage: soll im Tabellenblatt nur der Zellinhalt der betreffenden Zeile entfernt werden sodass eine Leerzeile entsteht, oder soll die betreffende Zeile komplett aus der Liste im Tabellenblatt gelöscht werden?
Dein derzeitiger Code ist m.E. außerdem falsch, denn er leert immer die Zellen der letzten Zeile der Auflistung im Tabellenblatt und nicht die Zellen der Zeile mit dem in der ListBox gewählten Eintrag.

---

 

Hallo Karin,

wie gesagt, ich bin gerade dabei mich in VBA einzuarbeiten. Deshalb danke für deinen Hinweis und deine Erläuterungen.
Natürlich soll der gewählte Eintrag komplett aus der Liste gelöscht werden.
Bleib gesund und liebe Grüße

der Mampfred

 

Hi,

so sollte der Code zum Löschen aussehen:

Code:

Private Sub cmdClear_Click()
    Dim strRueckfrage As String
    If lstDienstleistungen.ListIndex >= 0 Then
        lblDL = "Dienstleistung wird gelöscht"
        txtNeueDL = lstDienstleistungen.Column(1, lstDienstleistungen.ListIndex)
        txtPreis = Format(lstDienstleistungen.Column(2, lstDienstleistungen.ListIndex), "#,##0.00 €")
        strRueckfrage = MsgBox("Soll die Dienstleistung wirklich entfernt werden?", vbQuestion + vbYesNo, "Löschen")
        If strRueckfrage = "7" Then
            Me.Hide
            Exit Sub
        Else
            With Worksheets("Namensmanager")
                .Cells(lstDienstleistungen.ListIndex + 2, 1).EntireRow.Delete
            End With
        End If
        txtNeueDL = ""
        txtPreis = ""
        Eintragen
        MsgBox "Dienstleistung wurde aus der Liste gelöscht", vbInformation, "Löschen"
        lstDienstleistungen.ListIndex = -1
    End If
End Sub

Du musst allerdings die Formeln in Spalte A durch die Werte ersetzen, denn 1. ergeben sie einen Bezugs-Fehler durch das Löschen von Zeilen und 2. wird die Nr. automatisch vom Code eingetragen, und zwar durch die folgende Zeile in der Prozedur cmdHinzu_Click():

Code:

.Cells(lngZeile, 1) = Application.Max(.Columns(1)) + 1

Damit wird das Maximum aus Spalte A ermittelt und 1 addiert, was der nächsten laufenden Nr. entspricht.

---

 

Hallo Karin,

funktioniert einwandfrei.
Vielen Dank für deine großzügige und vor Allem deine ausdauernde Hilfe und Geduld mit einem VBA-Novizen.
Viele Grüße, habe ein schönes Wochenende und vor allen Dingen bleib gesund.

Der Mampfred