Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema InputBox
BildScreenshot zu InputBox InputBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Label
BildScreenshot zu Label Label-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

InputBox in eine ListBox ändern | Herbers Excel-Forum


Betrifft: InputBox in eine ListBox ändern von: Dirk der Xte
Geschrieben am: 26.01.2010 14:25:48

Hallo allerseits,

habe mal eine Frage zur ListBox.

Über eine InputBox filtere ich Daten in TB "Master" nach Spalte D und verarbeite diese dann weiter.
Klappt auch alles wunderbar!
Da diese Vorgehensweise nicht für jeden praktikabel ist (man muss die Bereiche die gefiltert werden sollen ja schließlich kennen) suche ich eine komfortable Möglichkeit diese über eine Auswahlliste zur Verfügung zu stellen.

Bei meiner Recherche bin ich über den Begriff ListBox gestolpert und gehe davon aus, dass dies die Lösung meines Problems sein könnte!

Nur wie bekomme ich eine solche ListBox in meinen bestehenden CODE?

Hat vielleicht jemand eine Idee?!

Gruß Dirk

Option Explicit

Private Sub CommandButton2_Click()

Dim strBereich As String
strBereich = InputBox("Geben Sie bitte einen Bereich ein:")

Worksheets("Master").Rows(1).autofilter Field:=4, _
Criteria1:=strBereich, Operator:=xlAnd

End Sub

  

Betrifft: Wert über ComboBox in UserForm auswählen von: NoNet
Geschrieben am: 26.01.2010 16:11:30

Hallo Dirk,

eine ListBox (und auch ComboBox) kann man nicht einfach "mal so" verwenden, sondern dazu benötigt man einen Container : Ein Tabellenblatt in der diese Listbox platziert ist oder typischerweise ein UserForm.

Ich empfehle Dir, im VB-Editor ein UserForm einzufügen, auf das Du ein Label (Beschriftungsfeld), eine ComboBox (anstatt Listbox) und 2 CommandButtons einfügst.

Kopiere dann folgenden Code in das UserForm (Rechtsklick auf das UF - "Code anzeigen") :

Private Sub UserForm_Initialize()
    'Hier ein paar Beispielwerte für die ComboBox :
    Me.ComboBox1.AddItem "Wert 1"
    Me.ComboBox1.AddItem "Wert 2"
    Me.ComboBox1.AddItem "Wert 3"
    Me.ComboBox1.AddItem "Wert 4"
End Sub

Private Sub CommandButton1_Click()
    strBereich = ComboBox1.List(ComboBox1.ListIndex) 'Ausgewählten Wert zuweisen
    Unload Me 'UserForm entladen
End Sub

Private Sub CommandButton2_Click()
    strBereich = "" 'Variable leeren => ABBRUCH
    Unload Me       'UserForm entladen
End Sub
In ein allgemeines Modul (z.B. "Modul1") kopierst Du dann diesen Code :
Public strBereich As String 'Öffentliche Variable - wird im UserForm gefüllt

Sub UF_Anzeigen()
    UserForm1.Show 'UserForm anzeigen
    
    If strBereich <> "" Then
        Worksheets("Master").Rows(1).AutoFilter Field:=4, _
            Criteria1:=strBereich, Operator:=xlAnd
    End If
End Sub
Deinem CommandButton in Tabelleblattz weist Du per Rechtsklick das Makro "UF_Anzeigen" zu :

Private Sub CommandButton1_Click()
UF_Anzeigen
End Sub


Und so sieht das dann in etwa aus :



Gruß, NoNet


  

Betrifft: was für eine perfekte Beschreibung! von: Dirk der Xte
Geschrieben am: 26.01.2010 16:49:38

Hallo NoNet,

vielen Dank für deine ausführliche Anleitung!
Habe es entsprechend nachgebaut (war ja jetzt auch nicht mehr schwer) :-) und es läuft!

Unter Me.ComboBox1.AddItem "Wert 1" trage ich ja die Bereiche ein, die ich filtern möchte?!
Gibt es hier ein Maximum an Eingaben (es sind ca. 35 und es werden immer mehr), oder könnte man die existierenden Bereiche auch automatisch zur Auswahl einlesen, so wie eine Gültigkeitsliste?

Gruß Dirk


  

Betrifft: Me.ComboBox1.RowSource = "$A$2:$A$99" von: NoNet
Geschrieben am: 26.01.2010 17:17:57

Hallo Dirk,

in die ComboBox kannst Du nahezu unbegrenzt Einträge aufnehmen (ich habe das eben mal mit 100.000 Einträgen getestet).
Alternativ kannst Du aber auch einen Quellbereich mit Daten festlegen, die hier angezeigt werden sollen :

Me.ComboBox1.RowSource = "$A$2:$A$99"

Diese Anweisung listet alle Zellen von A2:A99 in der ComboBox auf.
Wenn die letzte belegte Zeile variieren kann (also nicht 99, sondern z.B. 45 oder 510 oder 2000 etc.), dann kann man diese Anweisung etwas flexibilisieren :

Me.ComboBox1.RowSource = "$A$2:$A$" & Cells(Rows.Count, 1).End(xlUp).Row

Hier das komplette Sub dazu (die grünen Zeilen können auch gelöscht werden, da nur Bemerkungszeilen) :

VBA-Code:
Private Sub UserForm_Initialize()
    'Hier ein paar Beispielwerte für die ComboBox :
'    Me.ComboBox1.AddItem "Wert 1"
'    Me.ComboBox1.AddItem "Wert 2"
'    Me.ComboBox1.AddItem "Wert 3"
'    Me.ComboBox1.AddItem "Wert 4"
    'Alternative : Werte aus A2 bis letzten eintrag der Spalte A festlegen :
    Me.ComboBox1.RowSource = "$A$2:$A$" & Cells(Rows.Count, 1).End(xlUp).Row
End Sub
Code eingefügt mit Syntaxhighlighter 4.15


Allerdings werden hier auch doppelte Einträge aufgelistet, wenn diese Werte in Spalte A mehrfach enthalten sind.
Um alle Werte nur EINMALIG aufzulisten und Leerzellen aus der ComboBox zu eliminieren, dient folgender Code :

VBA-Code:
Private Sub UserForm_Initialize()
    '26.01.2010, NoNet - www.excelei.de (z.Zt. Offline !)
    'ComboBox mit allen Werten der Spalte A ohne Leerzellen und ohne doppelte Einträge füllen :

    Dim lngZ As Long
    Dim objColl As New Collection 'Collection zur EINMALIGEN Auflistung aller Werte

    On Error Resume Next
    For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'Alle Werte der Spalte A ab Zeile 2
        If Cells(lngZ, 1) <> "" Then objColl.Add Cells(lngZ, 1), "X" & Cells(lngZ, 1)
    Next

    For lngZ = 0 To objColl.Count
        Me.ComboBox1.AddItem objColl(lngZ) 'ComboBox mit einmaligen Werten der Collection befüllen
    Next
End Sub
Code eingefügt mit Syntaxhighlighter 4.15


Gruß, NoNet

PS: Danke für das Lob ;-)


  

Betrifft: ComboBox bleibt leider leer von: Dirk der Xte
Geschrieben am: 26.01.2010 17:41:08

Hallo NoNet,

Wahnsinn!

Nur leider bleibt die ComboBox mit der Variante Me.ComboBox1.RowSource = "$A$2:$A$" & Cells(Rows.Count, 1).End(xlUp).Row leer!?
Da ich in Spalte D filtere, habe ich die Zeile wie folgt angepasst:
Me.ComboBox1.RowSource = "$D$2:$D$" & Cells(Rows.Count, 4).End(xlUp).Row

Doch da tut sich nichts.
Auch bei der letzten Variante (ohne Doppelte) bleibt die CB leer

Was habe ich da falsch gemacht?

Gruß Dirk


  

Betrifft: AW: Wert über ComboBox in UserForm auswählen von: Volker
Geschrieben am: 26.01.2010 20:10:31

Hallo Dirk Hallo NoNet.
Dirk ich habe eine ähnliches Problem wie du. Kannst du deine Lösung mal hochladen?..
Oder NoNet, kann du deine Lösung mal Hochladen. Habe nähmlich es nicht so hinbekommen wie du beschrieben hast.
Gruss Volker


  

Betrifft: Demo-Mappe von: Mücke
Geschrieben am: 26.01.2010 20:54:15

Hallo Volker,

anbei eine abgespeckte Demo-Mappe mit allen Varianten von NoNet.
Ich hoffe es hilft Dir weiter.

Gruß Dirk

https://www.herber.de/bbs/user/67528.xls


  

Betrifft: Daten aus Blatt 'Master' einlesen von: NoNet
Geschrieben am: 26.01.2010 21:21:39

Hallo Dirk, hallo Volker,

vielen Dank für die DEMO-Mappe ! Deine Umsetzung entspricht genau meinen Vorgaben.
Anhand der DEMO-Mappe konnte ich nun auch den gewünschten Aufbau erkennen :
Ich ging bisher davon aus, dass der Button im gleichen Tabellenblatt wie die Daten platziert ist.
Da die Daten jedoch im Blatt "Master" stehen, der Button im Blatt "Start", muss man im UserForm noch mit angeben, dass die Daten für die ComboBox nicht aus dem aktuellen Blatt "Start", sondern aus dem Blatt "Master" stammen, dann klappt das Befüllen der ComboBox einwandfrei :

Private Sub UserForm_Initialize()
    '26.01.2010, NoNet - www.excelei.de (z.Zt. Offline !)
    'ComboBox mit allen Werten der Spalte D ohne Leerzellen und ohne doppelte Einträge füllen
    'Daten aus Blatt "Master"

    Dim lngZ As Long
    Dim objColl As New Collection 'Collection zur EINMALIGEN Auflistung aller Werte

    On Error Resume Next
    With Sheets("Master")
        For lngZ = 2 To .Cells(Rows.Count, 4).End(xlUp).Row 'Alle Werte der Spalte D ab Zeile 2
            If .Cells(lngZ, 4) <> "" Then objColl.Add .Cells(lngZ, 4), "X" & .Cells(lngZ, 4)
        Next
    End With

    For lngZ = 0 To objColl.Count
        Me.ComboBox1.AddItem objColl(lngZ) 'ComboBox mit einmaligen Werten der Collection befü _
llen
    Next
End Sub


Gruß, NoNet


  

Betrifft: eine DEMO-Mappe sagt mehr als 1.000 Worte von: Dirk der Xte
Geschrieben am: 26.01.2010 21:36:40

Hallo NoNet,

auf die Idee mit der DEMO-Mappe hätte ich ja auch gleich kommen können ;-)
Nun ja, jetzt funktioniert es tadellos! Vielen Dank!

Ein letzte Nachfrage :-)
Würde es sehr viele Umstände machen, den CODE so/zu anzupassen/erweitern, das die Bereiche sortiert in der Liste stehen?
Das würde dem Ganzen die Krone aufsetzten!!!!

Danke
und Gruß
Dirk


  

Betrifft: Krönungszeremonie von: NoNet
Geschrieben am: 26.01.2010 21:57:53

Hallo Dirk,

ich stehe zwar nicht so sehr auf Monarchien, hier aber dennoch die gewünschte Krönungszeremonie :

Private Sub UserForm_Initialize()
    '26.01.2010, NoNet - www.excelei.de (z.Zt. Offline !)
    'ComboBox mit allen Werten der Spalte D ohne Leerzellen und ohne doppelte Einträge füllen :

    Dim lngZ As Long, lngC As Long, strTemp As String
    Dim objColl As New Collection 'Collection zur EINMALIGEN Auflistung aller Werte

    On Error Resume Next
    With Sheets("Master")
        For lngZ = 2 To .Cells(Rows.Count, 4).End(xlUp).Row 'Alle Werte der Spalte D ab Zeile 2
            If .Cells(lngZ, 4) <> "" Then objColl.Add .Cells(lngZ, 4), "X" & .Cells(lngZ, 4)
        Next
    End With

    'Collection-Objekt sortieren :
    For lngZ = 0 To objColl.Count
        For lngC = 0 To lngZ
            If objColl(lngC) > objColl(lngZ) Then
                strTemp = objColl(lngZ)
                objColl(lngZ) = objColl(lngC)
                objColl(lngC) = strTemp
            End If
        Next
    Next
    
    For lngZ = 0 To objColl.Count
        Me.ComboBox1.AddItem objColl(lngZ) 'ComboBox mit einmaligen Werten der Collection befü _
llen
    Next
End Sub


Gruß, NoNet

PS: Sämtliche potentiellen "Jobangebote" in Deinem Königreich - inkl. Hofnarr lehne ich aber schon im Vorfeld ab ;-))


  

Betrifft: mein Königreich wird DIR ... von: Dirk der Xte
Geschrieben am: 26.01.2010 22:14:36

Hallo NoNet,

... einen Orden verleihen, für die wirklich perfekte Lösung incl. anschaulicher Beschreibung!
Solche Hilfestellungen kriegt man nicht alle Tage!

Die Krone sei DEIN! :-)

Vielen Dank
und einen schönen Abend
Gruß Dirk



  

Betrifft: AW: InputBox in eine ListBox ändern von: Beverly
Geschrieben am: 26.01.2010 16:27:30

Hi Dirk,

eine Möglichkeit:

Option Explicit

Private Sub CommandButton1_Click()
    Worksheets("Master").Range("D1").AutoFilter Field:=4, Criteria1:=ListBox1.List(ListBox1. _
ListIndex, 0), Operator:=xlAnd
End Sub

Private Sub ListBox1_GotFocus()
    Dim objDictionary As Object
    Dim varBereich As Variant
    Dim loZaehler As Long
    Dim arrDaten As Variant
    Set objDictionary = CreateObject("Scripting.Dictionary")
    With Worksheets("Master")
        varBereich = .Range("D2", .Range("D2").End(xlDown))
    End With
    For loZaehler = LBound(varBereich) To UBound(varBereich)
        objDictionary(varBereich(loZaehler, 1)) = 0
    Next
    arrDaten = objDictionary.keys
    ListBox1.List = arrDaten
End Sub
https://www.herber.de/bbs/user/67512.xls


GrußformelBeverly's Excel - Inn


  

Betrifft: ListBox nicht immer im Vordergrund! von: Dirk der Xte
Geschrieben am: 26.01.2010 17:15:46

Hallo Karin,

im Prinzip ist es das, was ich suche. Vielen Dank!

Würde gerne noch zwei Änderungswünsche anbringen, wenn erlaubt.

Die ListBox darf/soll nicht immer im Vordergrund stehen und
könnte man die Bereiche auch sortiert einlesen?!

Dann wäre es perfekt!

Danke
und Gruß
Dirk


  

Betrifft: AW: ListBox nicht immer im Vordergrund! von: Beverly
Geschrieben am: 26.01.2010 20:14:43

Hi Dirk,

zu Frage 1: die ListBox kann man z.B. ein- und aussblenden
zu Frage 2: die Daten können auch sortiert eingelesen werden

Option Explicit
Private Sub CommandButton1_Click()
    Worksheets("Master").Range("D1").AutoFilter Field:=4, Criteria1:=ListBox1.List(ListBox1. _
ListIndex, 0), Operator:=xlAnd
    ListBox1.Visible = False
End Sub

Private Sub CommandButton2_Click()
    Dim objDictionary As Object
    Dim varBereich As Variant
    Dim loZaehler As Long
    Dim arrDaten As Variant
    ListBox1.Visible = True
    Set objDictionary = CreateObject("Scripting.Dictionary")
    With Worksheets("Master")
        varBereich = .Range("D2", .Range("D2").End(xlDown))
    End With
    For loZaehler = LBound(varBereich) To UBound(varBereich)
        objDictionary(varBereich(loZaehler, 1)) = 0
    Next
    arrDaten = objDictionary.keys
    QuickSort arrDaten
    ListBox1.List = arrDaten
End Sub

Sub QuickSort(ByRef VA_Array, Optional V_Low1, Optional V_High1)
' https://www.herber.de/forum/ _
messages/1009306.html

    On Error Resume Next
    Dim V_Low2 As Long, V_High2 As Long
    Dim V_Val1, V_Val2 As Variant
    If IsMissing(V_Low1) Then
        V_Low1 = LBound(VA_Array, 1)
    End If
    If IsMissing(V_High1) Then
        V_High1 = UBound(VA_Array, 1)
    End If
    V_Low2 = V_Low1
    V_High2 = V_High1
    V_Val1 = VA_Array((V_Low1 + V_High1) / 2)
    While (V_Low2 <= V_High2)
        While (VA_Array(V_Low2) < V_Val1 And _
            V_Low2 < V_High1)
            V_Low2 = V_Low2 + 1
        Wend
        While (VA_Array(V_High2) > V_Val1 And _
            V_High2 > V_Low1)
            V_High2 = V_High2 - 1
        Wend
        If (V_Low2 <= V_High2) Then
            V_Val2 = VA_Array(V_Low2)
            VA_Array(V_Low2) = VA_Array(V_High2)
            VA_Array(V_High2) = V_Val2
            V_Low2 = V_Low2 + 1
            V_High2 = V_High2 - 1
        End If
    Wend
    If (V_High2 > V_Low1) Then Call _
        QuickSort(VA_Array, V_Low1, V_High2)
    If (V_Low2 < V_High1) Then Call _
        QuickSort(VA_Array, V_Low2, V_High1)
End Sub

GrußformelBeverly's Excel - Inn


  

Betrifft: vielen Dank ... von: Dirk der Xte
Geschrieben am: 26.01.2010 21:06:11

Hallo Karin,

... die CODE-Erweiterung für die Sortierung ist ja gigantisch!

Eine Nachfrage hab ich aber noch :-)
Kann man deine ListBox auch über/von/in einer UserForm starten?
Irgendwie will die Platzierung in der Tabelle nicht so wie ich will!

Danke
und einen schönen Abend!

Gruß Dirk


  

Betrifft: AW: vielen Dank ... von: Beverly
Geschrieben am: 27.01.2010 09:31:23

Hi Dirk,

der Code für das UserForm ist der gleiche, nur dass er nicht in die Prozedur Private Sub CommandButton2_Click() kommt sondern in das UserForm_Activate Ereignis.


GrußformelBeverly's Excel - Inn


Beiträge aus den Excel-Beispielen zum Thema "InputBox in eine ListBox ändern"