Office: (Office 2013) Tabelle mit Auswahl Listbox und Combobox befüllen (bzw. Werte überschreiben, ändern)

Hallo zusammen!

Habe da ein kleines Problem, eigentlich mindestens 2. Das erste, bin VBA-Anfänger und habe 1000 Ideen, was ich gerne umsetzen möchte, aber keine Ahnung wie genau es geht. Mit Hilfe habe ich die im Anhang beigefügte Datei erstellt. Vielleicht sollte ich kurz beschreiben, was ich umsetzen will, für den Fall, dass jemand die Datei sich anschaut und sich fragt warum so kompliziert...

Also. Meine Ausgangstabelle (Tabelle1) hat Werte in insgesamt 17 Spalten. Für das Problem was ich im Moment lösen will, kommt es auf die Spalten L, M, H und G an. Umgesetzt via Makro-Rekorder habe ich, dass er zunächst die Spalten L, M und G filtert. Und zwar nach L darf nicht leer sein, M muss leer sein und G muss leer sein. Im Anschluss wird das ganz sortiert und zwar so, dass die Zellen mit Wert in G oben stehen. Die so sortierten Daten sollen nun in eine Listbox übertragen werden. Im Aschluss soll man in der Listbox sich Zeilen auswählen können und einer Zelle pro Zeile (Spalte G) soll dann der Wert aus einer im Userform vorhandenen ComboBox zugewiesen werden.

In meiner Datei bekomme ich es hin, dass ich Werte in die Tabelle3 bekomme, aber ich finde einfach keine Lösung, wie ich die Werte in die Tabelle1 zurückbekomme. Mir ist klar, mit meinem Code kann das nichts werden, aber meinen vermurksten Versuch will ich keinem zumuten.

Vielleicht hat wer ne Lösung für mich. Als wäre das nicht kompliziert (genug), will ich aber gleich darauf hinweisen, dass dies nur ein Zwischenschritt ist. Die optimale Lösung wäre, wobei ich keine Ahnung habe, ob das beim jetzigen Aufbau überhaupt funktionieren kann oder ich mit weiteren Listboxen arbeiten müsste, ist, dass wenn ich Einträge in der Listbox makiere, er die Listbox durchsucht, ob es weitere Einträge (Zeilen) gibt, die den gleichen Wert wie Spalte B haben. Wenn die Bedingung erfüllt wäre, sollte er die Werte ebenfalls markieren, damit auch die dann beim Speichern mit dem Wert aus der Combobox in Spalte G befüllt werden.

Ich hoffe es ist einigermaßen verständlich geworden, was mein Ziel ist.

Hier der Code, mit dem ich filtere und sortiere...

Code:

Private Sub CommandButton1_Click()
Dim Zelle As Range, arrTemp(), tmpCounter As Long




'+--------------------------------------------------------------------------------------------------+
'|  Filtern über die Autifilter-Option.                                                             |
'+--------------------------------------------------------------------------------------------------+
'|  ErsteZelle      = 1. Zelle der zu filternden Datenbank.                                         |
'|  Spalte          = Spaltennummer der Datenbank, in welchem sich das Filterkriterium befindet.    |
'|  FilterKriterium = Filterkriterium nach welcher in der angegebenen Spalte gefiltert werden soll  |
'+--------------------------------------------------------------------------------------------------+
Range("A3").AutoFilter Field:=12, Criteria1:="<>"
Range("A3").AutoFilter Field:=13, Criteria1:="="
Range("A3").AutoFilter Field:=7, Criteria1:="="
    
  
'+---------------------------------------------------------------------------------------------+
'|  Sortiert Spalte H                                                                          |
'+---------------------------------------------------------------------------------------------+
Columns("H:H").Select
ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort.SortFields.Add Key:= _
Range("H2:H10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

    With ActiveWorkbook.Worksheets("Tabelle1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    

    For Each Zelle In Intersect(Tabelle1.UsedRange.SpecialCells(xlCellTypeVisible), ThisWorkbook.Worksheets("Tabelle1").Columns(1))
        ReDim Preserve arrTemp(tmpCounter)
        arrTemp(tmpCounter) = Zelle.Row
        tmpCounter = tmpCounter + 1
    Next
    

For tmpCounter = 1 To UBound(arrTemp)
   With UserForm1.ListBox1

      .AddItem Tabelle1.Cells(arrTemp(tmpCounter), 1)
      .List(.ListCount - 1, 1) = Tabelle1.Cells(arrTemp(tmpCounter), 2)
      .List(.ListCount - 1, 2) = Tabelle1.Cells(arrTemp(tmpCounter), 3)
      .List(.ListCount - 1, 3) = Tabelle1.Cells(arrTemp(tmpCounter), 4)
      .List(.ListCount - 1, 4) = Tabelle1.Cells(arrTemp(tmpCounter), 8)
      .List(.ListCount - 1, 5) = Tabelle1.Cells(arrTemp(tmpCounter), 7)
   End With
Next

    

End Sub

Mit dem kann ich die Werte aus der Listbox in Tabelle3 schreiben...

Code:

Private Sub CommandButton4_Click()

Dim i As Integer
Dim rngAusgabe As Range


On Error Resume Next

Set rngAusgabe = Sheets("Tabelle3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)


With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then

rngAusgabe.Offset(-1, 1).Value = .Column(0, i)
rngAusgabe.Offset(-1, 2).Value = .Column(1, i)
rngAusgabe.Offset(-1, 3).Value = .Column(2, i)
rngAusgabe.Offset(-1, 4).Value = .Column(3, i)
rngAusgabe.Offset(-1, 8).Value = WorksheetFunction.Proper(ComboBox1.Text)

Set rngAusgabe = rngAusgabe.Offset(1, 0)

End If
Next i
End With
Set rngAusgabe = Nothing
End Sub

Schon jetzt vielen Dank für Eure Unterstützung!
Grüße
Sandor

 

Hi,

verstehe ich das richtig, dass dein Problem darin besteht, dass du nicht weißt in welcher Zeile im Tabellenblatt sich deine in der ListBox ausgewählten Daten befinden? Oder meinst du etwas anderes?

Bis später,
Karin

 

Hallo Karin,

mit der Spalte und Tabelle bekomme ich ja hin. Wenn ich den Code auf

Code:

rngAusgabe.Offset(-1, 6).Value = WorksheetFunction.Proper(ComboBox1.Text) 

ändere und natürlich die Tabelle, dann landet das ja schon mal in der richtigen Tabelle und der richtigen Spalte, nur ist der Zellenbezug noch nicht richtig. Es soll eben nichts neues geschrieben werden, sondern eine bestehende Zeile bzw. in der Zeile in Spalte H ein Wert geändert/hinzugefügt werden.

Grüße

 

Hi,

ich würde ganz anders vorgehen: erweitere deine ListBox um 1 Spalte und schreibe die Zeilennummer in diese Spalte (die Spaltenbreite kannst du auf 0 setzem damit sie nicht sichtbar ist) - dann kannst du immer auf diese Zeilennummer zurückgreifen.

Das Füllen der ListBox würde ich anders machen, und zwar ohne das temporäre Array - einfach so:

Code:

    For Each Zelle In Intersect(Tabelle1.UsedRange.SpecialCells(xlCellTypeVisible), ThisWorkbook.Worksheets("Tabelle1").Columns(1))
       With UserForm1.ListBox1
          .AddItem Tabelle1.Cells(Zelle.Row, 1)
          .List(.ListCount - 1, 1) = Tabelle1.Cells(Zelle.Row, 2)
          .List(.ListCount - 1, 2) = Tabelle1.Cells(Zelle.Row, 3)
          .List(.ListCount - 1, 3) = Tabelle1.Cells(Zelle.Row, 4)
          .List(.ListCount - 1, 4) = Tabelle1.Cells(Zelle.Row, 8)
          .List(.ListCount - 1, 5) = Tabelle1.Cells(Zelle.Row, 7)
          .List(.ListCount - 1, 6) = Zelle.Row
       End With
    Next

Bis später,
Karin

 

Das zurückschreiben will mir nicht gelingen. Mit dem Code bin ich ja schon mal in der richtigen Tabelle. Ist ja auch keine Schwierigkeit. Aber es scheitert am zurückschreiben in die richtige Zeile... Mit dem Code kann es nicht klappen, weil er ja die Zeilennummer nicht überschreiben kann, also das ist mir schon klar...

Code:

Private Sub CommandButton4_Click()

Dim i As Integer
Dim rngAusgabe As Range

Set rngAusgabe = Sheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Offset(0, 0)


With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then

rngAusgabe.Offset(0, 0).Value = .Column(0, i)
rngAusgabe.Offset(0, 1).Value = .Column(1, i)
rngAusgabe.Offset(0, 2).Value = .Column(2, i)
rngAusgabe.Offset(0, 3).Value = .Column(3, i)
rngAusgabe.Offset(0, 6).Value = WorksheetFunction.Proper(ComboBox1.Text)
rngAusgabe.Offset(0, -1).Value = .Column(7, i)

Set rngAusgabe = rngAusgabe.Offset(1, 0)


End If
Next i
End With
Set rngAusgabe = Nothing
End Sub

Wie spreche ich die die korrekte Zeile an... also die Zeilennummer steckt ja hier drin...

Code:

rngAusgabe.Offset(0, -1).Value = .Column(7, i)

.

Danke!!!

 

Gelöst...

Code:

Private Sub CommandButton4_Click()
   
   Dim i As Integer
   Dim rngAusgabe As Range
  
'+---------------------------------------------------------------------------------------------+
'| Ausgewählte Listboxeinträge werden um den gewählten Wert aus der ComboBox ergänzt / geändert|
'+---------------------------------------------------------------------------------------------+

   With Me.ListBox1
      For i = 0 To .ListCount - 1
      If .Selected(i) Then
         Set rngAusgabe = Sheets("Tabelle1").Columns(1).Find(.List(i, 0), LookIn:=xlValues, lookat:=xlWhole)
         If Not rngAusgabe Is Nothing Then
'           rngAusgabe.Offset(0, 3).Value = .Column(3, i)
            rngAusgabe.Offset(0, 6).Value = WorksheetFunction.Proper(ComboBox1.Text)
         End If
      End If
      Next i
   End With
   
   Set rngAusgabe = Nothing
   
'+---------------------------------------------------------------------------------------------+
'| Filter wird aufgehoben und Spalte A neusortiert                                             |
'+---------------------------------------------------------------------------------------------+
Selection.AutoFilter
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Tabelle1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Tabelle1").Sort.SortFields.Add Key:=Range("A2:A52" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Tabelle1").Sort
        .SetRange Range("A1:Q52")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
End Sub