Excel VBA Listbox und mehr

[peschiber]

Hallo,

zunächst einen herzlichen Dank an all die Lieben, die mir in den letzten Tagen versucht haben als Einteiger in Excel / VBA unter die Arme zu greifen.

Meine Anfrage heute richtet sich ausschließlich an Menschen in diesem Forum, du Lust haben mehr als nur eine Zeil Code, Hinweise zu lesen.

Trotzdem neugierig?
Dann bleibt dir nicht anderes übrig, als meine im Anhang angefügte Datei zu öffnen, zu lesen.

Auf dem Arbeitsbaltt "Hinweise zur Programmierung"
findest du alles, was ich gerne wissen möchte.

Für jeden Hinweis, Verbesserungsvorschlag bin ich dankbar,

mit freundlichen Grüßen Peter, El Salvador

[maninweb]

Hallo,

was die Schleife mit den Controls angeht, schaue mal in Deine Datei rein, die ich hier wieder hochgeladen habe. Du findest dort eine neue UserForm,
die 3 Labels und 2 Buttons hat. Die Buttons machen letztlich dasselbe: den Text der Labels setzen. Ähnliches passiert auch in dem Code, wo Du
schreibst, dass Du es nicht verstanden hast. Der Text von Labels wird dynamisch zur Laufzeit gesetzt. Du bist aber nicht verpflichtet, eine
Schleife zu verwenden. Du kannst also natürlich auch für jedes einzelne Label eine Zeile Code schreiben und den Text setzen.

Jetzt gucken wir mal hier ...

Code:

With Tabelle1
 
    intLeSpalte = .Rows(1).End(xlToRight).Column
    lngZeileMax = .Range("A" & .Rows.Count).End(xlUp).Row
    lbo_Daten.ColumnCount = 6
   
    For intAnz = 1 To intLeSpalte
     
      Controls("Label" & intAnz) = .Cells(1, intAnz)
     
    Next intAnz
   
End With

Tabelle1 ist der Codename der Stammdatentabelle aus Deiner Arbeitsmappe und Tabelle1 ist gleichzeitig ein Objekt, um auf die Daten der Tabelle
zuzugreifen. Das heißt, dass .Cells(1, intAnz) einen Wert aus der Stammdatentabelle abruft; hier aus Zeile 1, also die Überschriften. Die With-Syntax
in Kombination mit der Punkt-Syntax solltest Du verstanden haben. Wenn nicht, dann müsstest Du Dich da einlesen.

Der Code holt sich zudem die Anzahl der Spalten (intLeSpalte) und Zeilen aus den Stammdaten, die bei Spalte A und Zeile 1 beginnen. Für den Code-
Ausschnitt ist aber hier nur die Anzahl der Spalten relevant. Ebenso lbo_Daten.ColumnCount, was zu einem späteren Zeitpunkt relevant wird.
Die Schleife durchläuft dann alle Labels in der UserForm von 1 bis intLeSpalte, also: Label1, Label2, ... und setzt deren Texte = Caption auf
den Wert der Spaltenüberschriften. In ähnlicher Weise passiert das mit den Textboxen. Das geht, weil VBA über das Objekt Controls dynamisch
über die Steuerelementnamen auf die Steuerelemente zugreifen kann.

Noch ein paar Tipps:

Der Code da oben (und woanders) adressiert Eigenschaften nicht vollständig sondern bezieht sich auf die als Standard definierte Eigenschaft.
Für Cells ist das Value, für ein Label ist das Caption. Besser und vor allem lesbarer ist es, die Eigenschaften explizit zu adressieren. Aus dem
Code würde dann werden:

Code:

Controls("Label" & intAnz).Caption = .Cells(1, intAnz).Value

Definiere keine Variablen, wenn Du sie nicht brauchst: z.B. hast Du locked = True drin. Wozu? Verwirrt nur. Ich schlage vor Variablen oben
am Anfang der Prozedur zu deklarieren, nicht irgendwo mitten drin. Das wird übersichtlicher.

Viele Steuerelemente haben Standardnamen. Würdest Du jetzt hingehen und das Label2 löschen (z.B. aus Versehen) und wieder ein neues
hinzufügen, würde dieses nicht Lable2 heißen, sondern vielleicht Label15. Der Code oben wird dann in einen Fehler laufen. Somit: habe die
Kontrolle über Deinen Code und benenne die Steuerelemente selbst. Damit Deine Benennungen über die Schleife adressiert werden können,
muss eine Zahl am Ende sein, also z.B. MeinLabelName_1, MeinLabelName_2, ... und daraus wird Controls("MeinLabelName_" & intAnz)

Die Anzahl Deiner Textboxen ist verhältnismäßig begrenzt. Es stellt sich die Frage, ob Du wirklich eine Schleife via Controls für Deine
Textboxen brauchst, oder ob Du nicht einfach die Textboxen alle aussagekräftig benennst und dann pro Textbox diese dann z.B.
auf Locked=True setzt. ich würde das als sinnvoller erachten, denn der Code wird lesbarer.

Gruß

[peschiber]

Hallo Maniweb,

ganz herzlichen Dank für deine ausführliche Hilfe.
Das entscheidende Schlüsselwort, welches ich nicht verstanden hatte war "Controls".
Langsam erahne ich, was damit gemeint ist.
Vor allem finde ich deinen Hinweis sehr wichtig:

Viele Steuerelemente haben Standardnamen. Würdest Du jetzt hingehen und das Label2 löschen (z.B. aus Versehen) und wieder ein neues
hinzufügen, würde dieses nicht Lable2 heißen, sondern vielleicht Label15. Der Code oben wird dann in einen Fehler laufen.


Als Anfänger, dem fundiertes Basiswissen fehlt, habe ich den Fehler gemacht, die automatisch vergebenen Labelbezeichnungen zu änden, weil sie mir zu wenig aussagekräftig waren und mich dann gewundert habe, warum es zu Fehlermeldungen kam.

Controls:
Ich hatte zwar im Prinzip verstanden, was die Routine bewirkt, die mir dankswerter Weise Klaus-Dieter geschrieben hatte, konnte mir aber die Syntax nicht erklären.
Es war das Schlüsselwort "Controls"
Ich fand es richtig cool,  dass die Labels entsprechen der "Überschriften" aus dem Arbeitsblatt "Stammdaten" ausgelesen und übergeben werden.
Du schreibst:

Damit Deine Benennungen über die Schleife adressiert werden können, muss eine Zahl am Ende sein, also z.B. MeinLabelName_1, MeinLabelName_2, ... und daraus wird Controls("MeinLabelName_" & intAnz).

Bevor ich mich jetzt daran mache für die für Labels und Textfelder aussagekrätige Namen zu vergeben, eine Verständigungsfrage.
Momentan läuft die Prozedur wie folgt ab:

Code:

Controls("Label" & intAnz).Caption = .Cells(1, intAnz).Value

Mit anderen Worten:
Hol dir den Wert, der in der ersten Zeile und der Spalte 1 des Arbeitsblatt "Stammdaten" steht und übergib diesen Wert an Label xy im Formular.
konkret: intAnz hat den Wert 1,
schreibe den Wert aus .Celle(1, intAnz) in Label1.
Diesen Vorgang wiederhole so oft, bis die letzte Spalte erreicht wird!

Meine Frage:
Was passiert, wenn ich den Labels im Formular aussagekräftiger Namen zuweise:
statt "Label2" zum Beispiel "LabelAnrede_2 ? > (gleiches würde für Textfelder gelten.)

Beim ersten Durchgang müsste bei geänderten Labelnamen die Anweisung zum Beispiel lauten:
Controls("LabelAnrede_" & intAnz)
beim zweiten Durchlauf: Controls("LabelTitel_" & intAnz)  oder?

Wenn ich das richtig verstanden habe, kann ich aber in diesem Fall die Schleife vergessen, da der Name des Labels bei jedem Duchgang sich ändert.
oder?

Du schreibst zwar:

Die Anzahl Deiner Textboxen ist verhältnismäßig begrenzt. Es stellt sich die Frage, ob Du wirklich eine Schleife via Controls für Deine ...

Ich fand als Einsteiger die Schleife deshalb so genial, weil ich mich trotz der "wenigen" vorhandenen Textfelder / Labels schon nach kurzer Zeit nicht mehr erinnern konnte, wie ich Labelxy benannt hatte.
Von daher fand ich die Bennung von Label1 usw ziemlich praktisch

Deshalb habe ich jetzt versucht, einen anderen Weg zu beschreiten:
über objControl.Tag
Jedem Textfeld wird im Eigenschaftsfeld ein Tag zugewiesen, der der Spalte in "Tabelle1" entspricht.
Hätte dann auch den Vorteil, dass ich zum Beispiel neue Spalten einfügen kann > "Geschlecht" fehlt zum Beispiel.
Bei einer von mir angelegten neuen Minidatei hat das auch hervorragend geklappt > Werte der Textfelder aus dem Formular wurden entsprechend in die "Tabelle1" übertragen, egal welchen Namen ich für die Textbox vergeben hatte, ob "Textbox02" oder "Pumpelmus".

Code

---

Private Sub cmd_ok_Click()
  Dim lngneueZeile As Long
  Dim lngTag As Long
  Dim objControl As Control
  With Tabelle1
 
  lngneueZeile = .Range("A" & .Rows.Count).End(xlUp).Row + 1
 
  For Each objControl In Me.Controls
   
    If TypeName(objControl) = "TextBox" Then          'prüfen if objContol ein textbox ist
   
      lngTag = objControl.Tag                          'ermitteln den in Eigenschaft des Textfeldes vergebenen Tag
     
      'trage den gefundenen Wert aus dem Textfeld in die Spalte ein, die die Nr. des Tag enthält
      ' Nr siehe "Tabelle_Test", war zu faul, die Spalten zu zählen, daher "Tabelle_Test"
       
      Tabelle1.Cells(lngneueZeile, lngTag).Value = objControl.Value
   
    End If
 
  Next objControl

End With

End Sub

Das hat  auch in der Minidatei hervorragen geklappt.

Leider!
Beim Übertragen auf meine "eigentliche" Datei, die ich inzwischen in "Stammdate01" umbennen wollte,
geht aber aus mir momentan unerfindlichen Gründen nicht, heißt also noch "Listbox03-2"
bekomme ich dann die Fehlermeldung:
"Next ohne For".
Sei doch bitte so nett und schau dir meine modifizierte Fassung an >

Private Sub cmd_Neuer_Eintag_sichern_Click()
Wäre schön, wenn du den Fehler finden würdest und mir Bescheid gibst.

Was leider noch gar nicht funktioniert ist
Änderung vornehmen beziehungsweise "Änderung sichern".
Ich habe zwar eine rudimentäre Vorstellung wie ich vorgehen müsste:
Nimm den in TextBox1 gespeicherten Wert - momentan entspricht er noch dem Zeilenwert in der Tabelle -
"grase" dann alle Textfelder ab und speichere den Wert in der Tabelle1 iin der Zeile x n den entsprechenden Spalten xy.
Meine Versuche sind leider immer gescheitert, weil Fehlermeldungen auftraten.

Auch dafür wäre ich dir sehr dankbar, wenn du mir einen Tipp geben könntest.

BItte nicht verwundert sein!
Viele Dinge verhalten sich noch nicht so, wie ich sie anstrebe.
Sind halt meine ersten Gehversuche.

Was mich unter anderem momentan stört ist folgender Sachverhalt:
Um bei der Neueingabe eines Datensatzes zu verhindern, dass User versehentlich auf vorhandene Listbox klicken, habe ichsie auf "unsichtbar" geschaltet,
Statt dessen soll ein ziemlich großes Textfeld angezeigt - Lückenbüßer für ausgeblendete Listbox - in dem darauf hingewiesen wird, den neuen Datensatz zu sichern.
Etwas nervig:
Einfügung eines neuen Zeilevorschubs funktioniert scheinbar nicht! > sieht nicht gut aus!

Code:

---

TextBox_Hinweis_fuer_Neuer_Eitrag_Aenderungen.Text = "Bitte nicht " & vbCrLf & _

    "vergessen! " & vbCrLf & "neuen Eintrag zu sichern!"

Ich habe es auch mit chr(13) versucht, leider das gleiche Ergebnis.
Wäre schön, wenn du dafür eine Lösung hättest.

Einen herzlichen Dank für deine Hilfe, Peter

PS.:
Frage der Höflichkeit:

Ich bin begeistert wie viele nette Menschen in diesem Forum bereit sind zu helfen und oft so schnell und ausführlich antworten.
Auf meine Anfrage von gestern Nacht habe ich innerhalb weniger Stunden gleich mehrere Anworten erhalten.
Natürlich werde ich versuchen auf jeden Hinweis entsprechend zu antworten.
Anderseits möchte ich es vermeiden mich ständig zu wiederholen.
Wäre es unhöflich, wenn ich an User x schreibe:
Danke ...., bitte sei so lieb und schau dir meine Antwort an, die ich dem User y geschrieben habe.

Davon ausgehend, dass auch andere User den Eintrag lesen, möchte ich vermeiden, dass diese dann zum x-ten Male den gleichen Schmus von mir durchles
Zu welcher korrekten, vor allem höflichen Verhaltensweise würdest du mir raten?

Und noch eine Frage:
Rechtschreibkorrektur.

Ich nutze hier vor Ort abwechselnd Spanische, Englische Tastatur.
Da ich nicht so ein großer Tipper vor dem Herrn bin, verschreibe ich mich ziemlich häufig, betrifft vor allem Umlaute, aber nicht nur.

Frage:
Gibt es für dieses Forum keine "automatische" Korrekturhilfe?
Fr entsprechenden Hinweis wäre ich dankbar.

Was ich auch nicht verstehe in diesem Forum:
Wenn ich mir die "Vorschau" anschaue, werden oft Zeilen eingefügt, die im Editiermodus gar nicht vorhanden war.
Nach dem Aufruf der "Vorschau" tauchen die vorher nicht gesetzten Zeilenumbrüche dann ebenfalls im Editierfenster auf, sie zu entfernen, zwecklos, beim nächsten Mal das gleiche Spiel.
Was mache ich falsch?

[Kuwer]

Hallo Peter,

Was ich auch nicht verstehe in diesem Forum:
Wenn ich mir die "Vorschau" anschaue, werden oft Zeilen eingefügt, die im Editiermodus gar nicht vorhanden war.
Nach dem Aufruf der "Vorschau" tauchen die vorher nicht gesetzten Zeilenumbrüche dann ebenfalls im Editierfenster auf, sie zu entfernen, zwecklos, beim nächsten Mal das gleiche Spiel.
Was mache ich falsch?

Du machst da nichts falsch. Der Editor ist fehlerhaft. Wir sind dran.

Gruß Uwe

[schauan]

Hallöchen,

die Vorschau scheint die Ursache zu sein. Verwende diese möglichst nicht oder nur 1x.
Ich habe mal die Leerzeilen in Deinem Beitrag reduziert.

Sollten trotzdem Leerzeilen entstehen und Du willst sie entfernen, dann bitte
- Beitrag editieren, ohne die Vorschau zu verwenden

[mumpel]

Ich mache es immer anders. Bevor ich die Vorschau aufrufe kopiere ich den Inhalt des Textfeldes und füge ihn dann wieder ein.

[schauan]

Peter, mal noch 'ne Frage. Welchen Browser benutzt Du?

[peschiber]

Hallo, danke der Nachfrage

Ich nutze Firefox 84.0 (64-Bit)
Windows 10 Prof (64-Bit)

Warum fragst Du danach?

Grße, Peter

[schauan]

Ich hab den Edge und vielleicht kommen die Leerzeilen auch mehr oder weniger in Abhängigkeit vom Browser

[maninweb]

Hallo peschiber,

ich habe so den Eindruck, als hätte mein Beitrag zu etwas mehr Durcheinander beigetragen, als beabsichtigt.
Vielleicht klärt ja folgendes mehr auf.

In VBA gibt es sogenannte Auflistungsobjekte, wie beispielsweise Worksheets, Shapes und bei UserForms Controls.
Auflistungsobjekte erkennst Du daran, dass sie in der Regel im Plural sind. Auflistungsobekte führen eine Liste von
Objekten eines entsprechenden Typs.

Worksheets beinhaltet z.B. eine Liste der Tabellen (Worksheet). Auf ein Element der Auflistung Worksheets kannst
Du per Index - Worksheets(1) ist die erste Tabelle - oder per Name - Worksheets("Stammdaten")  ist die Tabelle
zu den Stammdaten, die nicht die erste Tabelle in der Mapp sein muss - zugreifen. Eine Tabelle ist ein Worksheet.

Ähnlich ist das mit den Steuerelementen in Deiner UserForm. Controls beinhaltet zur Laufzeit (!) eine Liste aller
Steuerelemente Deiner UserForm. Das können Labels, Textboxen, Listboxen usw. sein. Jedes Steuerelement
ist ein Control - mit unterschiedlichen Eigenschaften.

Wie bei Worksheets kannst Du nun per Index (eine Zahl) oder per Name (ein String) innerhalb der Controls-
Auflistung zugreifen. Angenommen, Du hättest eine UserForm mit nur einem einzigen Steuerelement, das
z.B. eine Textbox wäre und txtAnrede heißen würde, könntest Du zur Laufzeit wie folgt darauf zugreifen:

Code:

Controls(1).Name
Controls("txtAnrede").Name

Jetzt nehmen wir aber mal, wie in Deinem Fall an, dass Du ganz viele Steuerelemente unterschiedlichen Typs
hast und Du einer bestimmten Anzahl der Steuerelemente (hier Labels) einen Text zuweisen möchtest.

Würdest Du nun eine Schleife bauen, die indexbasiert - Controls(n) - alle Steuerelemente durchgeht, müsstest
Du in der Schleife unterscheiden, welcher Typ es denn ist und auch gucken, ob Du das richtige Steuerelement
erwischt hast. Zu umständlich.

Also gehen wir hin und orientieren uns an den Namen des Steuerelements, den Controls kann auch über einen
String auf das Steuerelement zugreifen. Wenn also die Labels Label1, Label2 usw. heißen, kann man sich in
einer Schleifen die Namen zusammenbauen: "Label" & n - n ist der Index. Controls("Label" & n) greift also
aus z.B. Label15 zu, wenn n = 15 ist.

Das hat dann Klaus-Dieter gemacht. Das funktioniert aber nur solange, wie denn auch die Steuerelelement-
namen genau passen zu dem wie Du sie beschriften möchtest.

Sobald Du ein Steuerelement in Deine UserForm einfügst erhält dieses einen Namen, der automatisch von VBA
zugewiesen wird. VBA numeriert aber stur durch. Löscht Du mal eins - das kann auch viel später sein, wird beim
erneuten Einfügen einfach nur hochgezählt. Du müsstest in einem solchen Fall daran denken, das wiederherge-
stellte Steuerelement entsprechend zu benennen. 

Mein Ansatz (das wollte ich dann auch im ersten Beitrag vermitteln) ist daher, grundsätzlich Steuerelemente umzu-
benennen. Wenn ich persönlich mal welche brauche, die in der Art und Weise wie bei Dir iterierbar sein sollen,
dann erhalten die beispielsweise Namen wie STC_Dynamic_01, STC_Dynamic_02, usw. Letztlich mache ich zwar
dasselbe, was Excel gemacht hat, aber ich habe meine eigene Systematik hinterlegt. Ich erkenne auch Jahre
später, was ich da gemacht habe. Natürlich, der Anteil vor der Zahl muss dann schon gleich sein, sonst
klappt auch das nicht.

Du musst das natürlich nicht so machen. Das ist reine Geschmackssache.

------------------------------------------------------

Zu den Fehler, die Du siehst: Du hast ein End If vergessen, und zwar vor dem Next objControl.
Ausserdem ist die Eigenschaft Tag ist keine Zahl, sondern ein String. VBA wandelt das jetzt
implizit in eine Zahl um.


Gruß