Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1408to1412
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Dropdownliste aus 2 Spalten
11.02.2015 09:23:03
Hans
Moin moin,
eine Frage hätte ich da noch.
Ich hole mir für eine Dropdownliste die Spalte per VBA Befehl:
Set arr = .Range("A3", .Cells(.Rows.Count, 1).End(xlUp))
Jetzt würde ich diese Liste gerne noch um die Spalte C erweitern, also
Set arr = .Range("C3", .Cells(.Rows.Count, 3).End(xlUp))
aber ich schaffe es nur, mir eine Spalte zu holen... Ich habe es schon versucht mit "&" und "+" zu verbinden, aber ich kriege nur Fehlermeldungen.
Gibt es eine Möglichkeit, beide Listen untereinander anzeigen zu lassen?
Und noch eine Kleinigkeit, die nicht sooo wichtig ist, gibt es auch eine Möglichkeit in der Liste zu scrollen, anstatt sich mit den kleinen Pfeilen hoch und runter zu bewegen?

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Dropdownliste aus 2 Spalten
11.02.2015 10:06:04
Klaus
Hi Hans-Joachim,
Set arr = Union(.Range("A3", .Cells(.Rows.Count, 1).End(xlUp)), .Range("C3", .Cells(.Rows.Count, 3).End(xlUp)))
Grüße,
Klaus M.vdT.

AW: Dropdownliste aus 2 Spalten
11.02.2015 10:50:26
Hans
Danke nochmal für deine Hilfe Klaus, allerdings funktioniert das irgendwie nicht...
Wenn ich das so mache, wird mir immer nur die erste Spalte angezeigt, also bei:
Set arr = Union(.Range("A3", .Cells(.Rows.Count, 1).End(xlUp)), .Range("C3", .Cells(.Rows.Count, 3).End(xlUp)))
zeigt er mir nur die aus Spalte A an und wenn ich es vertausche:
Set arr = Union(.Range("C3", .Cells(.Rows.Count, 3).End(xlUp)), .Range("A3", .Cells(.Rows.Count, 1).End(xlUp)))
zeigt er mir nur die aus Spalte C an.
Leider finde ich den Fehler nicht :(

Anzeige
AW: Dropdownliste aus 2 Spalten
11.02.2015 12:41:47
Klaus
Hallo Hans-Joachim,
ohne Musterdatei etwas zu basteln ist natürlich schwierig. Aus dem Ärmel geschüttelt hätte ich eine Idee zu einem Workaround. Mach eine neue Tabelle Namens "XX", die ist komplett Leer. Mit xlveryhidden blendest du die aus, so dass der User sie nicht sieht. Im Makro füllst du diese Tabelle mit dem Inhalt des Range, aber untereinander. Dann schreibst du es wieder in das Range zurück. Das könnte etwas so aussehen:

Sub test()
Dim arr As Range
Dim r As Range
Dim myRow As Long
With Sheets("Tabelle1")
Set arr = Union(.Range("A3", .Cells(.Rows.Count, 1).End(xlUp)), .Range("C3", .Cells(.Rows. _
Count, 3).End(xlUp)))
End With
With Sheets("XX")
.Cells.ClearContents
myRow = 1
For Each r In arr
.Cells(myRow, 1).Value = r.Value
myRow = myRow + 1
Next r
Set arr = .Range("A1:A" & myRow)
End With
End Sub
Disclaimer: Heiße Nadel. Man kann ein arr auch direkt in eine Tabelle schreiben, und es geht bestimmt auch ohne den Umweg über eine Tabelle. Aber ich hab den Code dafür nicht im Kopf, der gezeigte Code funktioniert zumindest schonmal.
Grüße,
Klaus M.vdT.

Anzeige
AW: Dropdownliste aus 2 Spalten
11.02.2015 13:40:44
Hans
ja, ich glaube ich hätte dafür ein bisschen mehr erläutern sollen:
Also es soll eine Bestandsliste und -pflege werden. Auf dem ersten Blatt habe ich zwei Userforms; eine für Zugänge, eine für Abgänge, danach kommen 13 Tabellenblätter mit Artikelgruppen und in jedem einzelnen Blatt sind dann alle zum entsprechenden Tabellenblatt gehörenden Artikel aufgelistet.
In der Userform kann ich nun zunächst per Dropdown, das Tabellenblatt auswählen und dann kann ich in der zweiten Combobox die Artikel auswählen. Die Artikelbezeichnungen sind immer in Spalte A und C und in den Spalten B und D die jeweiligen Mengen. Klar, jetzt könnte man natürlich alle Artikel aus Spalte C unter A kopieren, das ist aber so gewollt, weil es zu (fast) jedem Artikel zwei Sorten gibt und so eine gute Übersicht bleibt.
Wenn ich nun die Artikelgruppe und den Artikel ausgewählt habe, wird mir in einer Textbox der aktuelle Warenbestand gezeigt und einer einer zweiten Textbox gebe ich die Anzahl der ab- oder zugehenden Mengen ein, welche nach dem Klick auf OK zum aktuellen Stand hinzugefügt bzw. abgezogen werden.
Den Code dafür habe ich mir aus dem Internet zusammengebastelt (mein VBA ist einfach noch zu schlecht), wird also noch mit einigen Fehlern behaftet sein, also nicht wundern :)
Hier mein bisheriger Code für die Userform "Abgänge":
Dim arr As Range
Dim ws As Worksheet
Dim LookupValue As String
Dim UpdateRow As Long
Private Sub ComboBox1_Change()
Set ws = Sheets(ComboBox1.Value)
With ws
Set arr = Union(.Range("A3", .Cells(.Rows.Count, 1).End(xlUp)), .Range("C3", .Cells(.Rows. _
Count, 3).End(xlUp)))
End With
ComboBox2.List = arr.Value
End Sub

Private Sub ComboBox2_Change()
Set ws = Sheets(ComboBox1.Value)
LookupValue = ComboBox2.Value
TextBox2.Value = Application.Index(ws.Range("B:B"), Application.Match(LookupValue, ws.Range("A: _
A"), 0))
UpdateRow = ws.Range("A:A").Find(what:=LookupValue, lookat:=xlWhole).Row
End Sub

Private Sub CommandButton1_Click()
Set ws = Sheets(ComboBox1.Value)
ws.Cells(UpdateRow, 2).Value = (ws.Cells(UpdateRow, 2).Value) - (TextBox1.Value)
MsgBox "Bestand von " & TextBox2.Value & " um " & (TextBox1.Value) & " auf " & ws.Cells( _
UpdateRow, 2).Value & " verringert"
End Sub
Private Sub UserForm_Initialize()
For i = 2 To ActiveWorkbook.Sheets.Count
ComboBox1.AddItem ActiveWorkbook.Sheets(i).Name
Next i
End Sub

Private Sub UserForm_Click()
End Sub
Was mir aber sehr wichtig ist, dass ich die Hilfsbereitschaft in diesem Forum nicht überstrapazieren will und wenn das hier zu weit gehen sollte, habe ich dafür vollstes Verständnis!

Anzeige
Musterdatei bitte :-)
11.02.2015 16:49:42
Klaus
Hi Hans Jürgen,
ich bau doch jetzt keine Testdatei mit Userform nach :-) Bitte hochladen was du bereits hast!
Grüße,
Klaus M.vdT.

AW: Musterdatei bitte :-)
12.02.2015 10:33:26
Klaus
Hi Jürgen,
das sollte gehen. Achtung, ich hab auch in ARR die 12 in eine 3 geändert, das war nämlich falsch :-)
Private Sub ComboBox1_Change()
Set ws = Sheets(ComboBox1.Value)
With ws
Set arr = Union(.Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)), .Range(.Cells(3,  _
3), .Cells(.Rows.Count, 3).End(xlUp)))
End With
With ComboBox2
.Clear
For Each r In arr
.AddItem r.Value
Next r
End With
End Sub
Randbemerkung: Gewöhn dir an, deine Comboboxen (und alles andere) sprechend zu benennen! Was "ComboBox2" genau ist weisst du in 3 Monaten nicht mehr aus dem Kopf, nennst du das Ding von Anfang an "CBox_ArtikelListe" dann verstehst du deinen Code auch in 3 Jahren noch auf anhieb.
Grüße,
Klaus M.vdT.

Anzeige
AW: Musterdatei bitte :-)
12.02.2015 11:04:27
Hans
Jaaa, genauso wollte ich das haben!!! Vielen Dank. Klasse Nummer, dass du dich damit schon so intensiv beschäftigt hast :) Das mit den Benennungen werde ich mir mal zu Gemüte führen, vor allem bei größeren Sachen wird das wohl sehr wichtig sein.
Als letzten Schliff würde ich noch gerne die leeren Zellen und "Zwischenüberschriften" wie z.B. "mit Schrauben und Scheiben" ausblenden wollen.
Ich denke da an eine If-Schleife.. So ungefähr:
if (cell.Value = null ODER cell.Value = "mit Schrauben und Scheiben")
dann überspringe diese Zelle
Aber wie gesagt, wäre nur für den optischen Feinschliff, daher nicht notwendig und vor allem dann nicht, wenn der Code doch zu komplex werden würde. Aber bei so vielen Gedanken, die ich mir schon gemacht habe, habe ich immer mehr den Drang, die Tabelle aufs Detail zu perfektionieren :)

Anzeige
AW: Musterdatei bitte :-)
12.02.2015 11:48:45
Hans
Ach, ich habe da schon wieder Probleme... Ich kriege jetzt zwar alle so angezeigt, wie ich es wollte, aber der Rest des Codes muss ja auch so angepasst werden, dass ich alle Bestände auch ändern kann. So wie es bisher ist, kann ich nur die in Spalte B ODER die in Spalte D ändern... habe das auch nochmal mit Union versucht, aber so einfach ist das ja nicht.
Ein Problem gelöst, und mehrere neue Probleme tauchen auf - sehr demotivierend :(

AW: Musterdatei bitte :-)
12.02.2015 13:03:25
Klaus
Hallo Hans Joachim,
zum ersten, das ist ja relativ einfach. Brauchst ja nur eine IF-Zeile
 With ComboBox2
.Clear
For Each r In arr
if r.value = "" or r.value = "mit Schrauben und Scheiben" then
'mache nix
else
.AddItem r.Value
end if
Next r
End With
End Sub
zum zweiten:
Dein Code sucht ja fix in einer Spalte. Du kannst einen Wert auch Spaltenunabhängig suchen (unter Excel mit STRG+F, unter VBA mit Range.Find(Wert).
Die Zahl willst du immer eins rechts von der gefundenen Zelle eintragen, dafür gibt es Offset.
Private Sub CommandButton1_Click()
Set ws = Sheets(ComboBox1.Value)
Dim MyFindRng As Range
With ws
Set MyFindRng = .Cells.Find(ComboBox2.Value)
MyFindRng.Offset(0, 1).Value = MyFindRng.Offset(0, 1).Value - (TextBox1.Value)
MsgBox "Bestand von " & TextBox2.Value & " um " & (TextBox1.Value) & " auf " & MyFindRng. _
Offset(0, 1).Value - (TextBox1.Value) & " verringert"
End With
End Sub
MyFindRng ist die Zellenadresse die du bekommst, wenn du im Blatt ws STRG+F (suchen) ausführst und den Wert aus Combobox2 suchts. Offset 0,1 ist eine Zelle rechts davon. Der Rest ist ersichtlich, oder?
In der Art kannst du auch den anderen Code korrigieren, der jetzt noch auf eine fixe Spalte zeigt.
Viel wichtiger fände ich: Warum hast du ÜBERHAUPT eine Darstellung in zwei verschiedenen Spalten? Das bringt doch nur Probleme ... wie du selbst merkst. Und mein gezeigter Workaround ist alles andere als guter Stil :-)
Grüße,
Klaus M.vdT.

Anzeige
Nachtrag
12.02.2015 13:18:17
Klaus
Hallo Hans-Jürgen,
da du eh danach fragen wirst :-) hier der optimierte Code für deine "Abgang"-Userform.
Dim arr As Range
Dim ws As Worksheet
Dim myFindRng As Range
Dim UpdateRow As Long
Private Sub ComboBox1_Change()
Set ws = Sheets(ComboBox1.Value)
With ws
Set arr = Union(.Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)), .Range(.Cells(3, 3) _
, .Cells(.Rows.Count, 3).End(xlUp)))
End With
With ComboBox2
.Clear
For Each r In arr
Select Case r.Value
Case "Artikelbezeichnung"
Case "hart,versilbert"
Case "weich, versilbert"
Case ""
Case "mit Schrauben u. Scheiben"
Case Else
.AddItem r.Value
End Select
Next r
End With
End Sub
Private Sub ComboBox2_Change()
On Error GoTo hell
Set ws = Sheets(ComboBox1.Value)
With ws
Set myFindRng = .Cells.Find(ComboBox2.Value)
TextBox2.Value = myFindRng.Offset(0, 1).Value
End With
GoTo heaven
hell:
ws.Activate
MsgBox ("Artikel nicht gefunden!")
heaven:
End Sub
Private Sub CommandButton1_Click()
On Error GoTo hell
Set ws = Sheets(ComboBox1.Value)
Dim myFindRng As Range
With ws
Set myFindRng = .Cells.Find(ComboBox2.Value)
myFindRng.Offset(0, 1).Value = myFindRng.Offset(0, 1).Value - (TextBox1.Value)
MsgBox "Bestand von " & TextBox2.Value & " um " & (TextBox1.Value) & " auf " & myFindRng. _
Offset(0, 1).Value - (TextBox1.Value) & " verringert"
End With
GoTo heaven
hell:
MsgBox ("In der Zelle steht ein ungültiger Wert!")
ws.Activate
myFindRng.Offset(0, 1).Select
heaven:
End Sub
Private Sub UserForm_Initialize()
For i = 2 To ActiveWorkbook.Sheets.Count
ComboBox1.AddItem ActiveWorkbook.Sheets(i).Name
Next i
End Sub
"Zugang" schreibst du dann bitte selber um :-)
Ich würd statt Comboboxen lieber Listboxen nutzen, wenn es viele Artikel zur Auswahl gibt. Das ist komfortabler für den Nutzer.
Grüße,
Klaus M.vdT.

Anzeige
AW: Nachtrag
12.02.2015 15:21:33
Hans
Nochmals vielen Dank, Klaus :)
Also, in 2 bzw. 4 Spalten habe ich es, weil es immer die baugleichen Teile sind, allerdings unterschiedliche Materialien, es soll also im Wesentlichen der Übersicht dienen.
Außerdem finde ich, dass das Lernen von VBA mit solchen (selbst überlegten) Herausforderungen viel besser voran geht, als wenn ich Tutorials mit Übungsaufgaben durchgehe, aber ich denke, die Art zu Lernen unterscheidet sich von Mensch zu Mensch. Aber wenn ich mir selbst das Problem überlege und dann auf eine Lösung zu arbeite, bleibt viel mehr hängen als wenn ich das Problem in einer Übungsaufgabe schon vorher vorgegeben ist.
Bei deinem Code habe ich einen Fehler entdeckt, was mich paradoxerweise erfreut, aber nicht, weil es ein Fehler ist, sondern weil ICH ihn entdeckt habe :)
MyFindRng.Offset(0, 1).Value = MyFindRng.Offset(0, 1).Value - (TextBox1.Value)
MsgBox "Bestand von " & TextBox2.Value & " um " & (TextBox1.Value) & " auf " & MyFindRng. _
Offset(0, 1).Value - (TextBox1.Value) & " verringert"
In der ersten Zeile verringert sich der MyFindRng.Offset-Wert um den der TextBox1, was ja auch richtig ist, aber in der MsgBox passiert das nochmal, so dass immer der doppelte Wert abgezogen wird.
Aber nach der Freude über den gefundenen Fehler und der Tatsache, dass ich es komplett nachvollziehen kann, kam schnell die Ernüchterung, weil ich nicht den geringsten Plan habe, wie ich den Code auch auf "Private Sub ComboBox2_Change()" anwenden kann, damit der Wert auch wieder in der richtigen Zelle eingetragen wird.
Also ich brauche wohl diesen aktualisierten "MyFindRng.Offset(0, 1).Value" Wert und muss ihn dann wieder in die Zelle schreiben, aus der er ursprünglich kommt... Aber wie ich dahin komme...

Anzeige
AW: Nachtrag
12.02.2015 15:22:34
Hans
Oh, zu spät gesehen, dass du nochmal geschrieben hast, werde es gleich mal ausprobieren :)

AW: Nachtrag
12.02.2015 15:36:06
Klaus
Hi Hans Joachim,
mich freut dass du dich über den Fehler freust. Das zeigt mir nämlich, dass du dich mit meinem Code tatsächlich beschäftigst und nicht blind die Komplettlösung abfragst wie so viele hier :-)
Aus dem Bauch raus, wenn du die korrekte MsgBox Meldung nur VOR der Änderung bekommst, dreh die beiden Zeilen doch einfach um.
 MsgBox "Bestand von " & TextBox2.Value & " um " & (TextBox1.Value) & " auf " & MyFindRng. _
Offset(0, 1).Value - (TextBox1.Value) & " verringert"
MyFindRng.Offset(0, 1).Value = MyFindRng.Offset(0, 1).Value - (TextBox1.Value)
oder korrigiere einfach die Formel in der Textbox:
MyFindRng.Offset(0, 1).Value = MyFindRng.Offset(0, 1).Value - (TextBox1.Value)
MsgBox "Bestand von " & TextBox2.Value & " um " & (TextBox1.Value) & " auf " & MyFindRng. _
Offset(0, 1).Value & " verringert"
statt dir den Wert aufwendig zu merken.
Viel Spaß beim VBA-Lernen!
Grüße,
Klaus M.vdT.

Anzeige
AW: Nachtrag
12.02.2015 15:36:56
Hans
ja, wunderbar.. jetzt funktioniert alles wunderbar :)
Vielen Dank an dieser Stelle nochmal an das Forum, insbesondere an dich Klaus!!!

Danke für die Rückmeldung! owT.
12.02.2015 16:23:30
Klaus
.

AW: Dropdownliste aus 2 Spalten
11.02.2015 11:26:08
fcs
Hallo Hans,
wenn du Daten aus mehreren Zellbereichen in der Auswahlliste einer Datengültigkeits-Liste haben mochtest, dann müssen man die Zellinhalte in einem Textstring zu einer Liste zusammengesetzt werden..
Nachfolgend Code-Beispiel
Scrollen kannst du in der Liste auch mit dem Schieberegler oder indem du nicht aucf die Pfeile klicks, sondern auf den Balkenbereich zwischen Pfeil und Schieberegler.
Gruß
Franz
Sub AW_DatenGueltigkeit()
' AW_DatenGueltigkeit Makro
Dim Zelle As Range
Dim strAW As String
With Worksheets("Tabelle1")
For Each Zelle In Application.Union( _
.Range("A3", .Cells(.Rows.Count, 1).End(xlUp)), _
.Range("C3", .Cells(.Rows.Count, 3).End(xlUp)))
If strAW = "" Then
strAW = Zelle.Value
Else
strAW = strAW & "," & Zelle.Value
End If
Next
End With
With Tabelle2
With .Range(.Cells(3, 3), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 3)).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strAW
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End With
End Sub

AW: Dropdownliste aus 2 Spalten
11.02.2015 11:59:20
Hans
Moin Franz,
danke für die Mühe, aber leider steige ich da nicht so ganz durch. Selbst wenn ich den Code blind kopiere und anpasse (Namen der Tabellen), geht es nicht, aber auch wenn es ginge, wüsste ich nicht wieso, und das sollte ja eigentlich nicht Sinn der Sache sein.
Die Idee von Klaus hat mir schon gut gefallen (wahrscheinlich, weil es nur eine Zeile ist, die ich auch noch verstehe), aber leider geht es damit ja irgendwie nicht :(

334 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige