ich zeige auf einer UserForm eine Listbox mit mehreren Spalten und Zeilen. Bei Click soll von der markierten Zeile Werte aus den Spalten der jeweiligen Variablen zugewiesen werden: Spalte1 - Variable 1, Spalte2 - Variable2, usw.
Gruß Sepp
Option Explicit
' unbound >
' Describes a control that is not related to a worksheet cell.
' In contrast, a bound control is a data source for a worksheet cell
' that provides access to display and edit the value of a control.
Private SourceRange As Range
Private Sub CommandButton1_Click()
If (Me.ListBox1.ListIndex > -1) Then
MsgBox "ListBox1.Value = " & Me.ListBox1.Value & vbCrLf & _
"ListBox1.Text = " & Me.ListBox1.Text & vbCrLf & _
"ListBox1.BoundColumn = " & ListBox1.BoundColumn & vbCrLf & _
"ListBox1.TextColumn = " & ListBox1.TextColumn
Dim v1, v2, v3, v4, v5
v1 = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
v2 = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
v3 = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
v4 = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
v5 = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
MsgBox "v1 = " & v1 & vbCrLf & _
"v2 = " & v2 & vbCrLf & _
"v3 = " & v3 & vbCrLf & _
"v4 = " & v4 & vbCrLf & _
"v5 = " & v5 & vbCrLf
Else
MsgBox "Etwas ausweahlen ..."
End If
End Sub
Private Sub CommandButton2_Click()
If (Me.ListBox2.ListIndex > -1) Then
MsgBox "ListBox2.Value = " & Me.ListBox2.Value & vbCrLf & _
"ListBox2.Text = " & Me.ListBox2.Text & vbCrLf & _
"ListBox2.BoundColumn = " & ListBox2.BoundColumn & vbCrLf & _
"ListBox2.TextColumn = " & ListBox2.TextColumn
Dim v1, v2, v3, v4, v5
v1 = Me.ListBox2.List(Me.ListBox2.ListIndex, 0)
v2 = Me.ListBox2.List(Me.ListBox2.ListIndex, 1)
v3 = Me.ListBox2.List(Me.ListBox2.ListIndex, 2)
v4 = Me.ListBox2.List(Me.ListBox2.ListIndex, 3)
v5 = Me.ListBox2.List(Me.ListBox2.ListIndex, 4)
MsgBox "v1 = " & v1 & vbCrLf & _
"v2 = " & v2 & vbCrLf & _
"v3 = " & v3 & vbCrLf & _
"v4 = " & v4 & vbCrLf & _
"v5 = " & v5 & vbCrLf
Else
MsgBox "Etwas ausweahlen ..."
End If
End Sub
Private Sub CommandButton3_Click()
If (Me.ListBox3.ListIndex > -1) Then
MsgBox "ListBox3.Value = " & Me.ListBox3.Value & vbCrLf & _
"ListBox3.Text = " & Me.ListBox3.Text & vbCrLf & _
"ListBox3.BoundColumn = " & ListBox3.BoundColumn & vbCrLf & _
"ListBox3.TextColumn = " & ListBox3.TextColumn
Dim v1, v2, v3, v4, v5
v1 = Me.ListBox3.List(Me.ListBox3.ListIndex, 0)
v2 = Me.ListBox3.List(Me.ListBox3.ListIndex, 1)
v3 = Me.ListBox3.List(Me.ListBox3.ListIndex, 2)
v4 = Me.ListBox3.List(Me.ListBox3.ListIndex, 3)
v5 = Me.ListBox3.List(Me.ListBox3.ListIndex, 4)
MsgBox "v1 = " & v1 & vbCrLf & _
"v2 = " & v2 & vbCrLf & _
"v3 = " & v3 & vbCrLf & _
"v4 = " & v4 & vbCrLf & _
"v5 = " & v5 & vbCrLf
Else
MsgBox "Etwas ausweahlen ..."
End If
End Sub
Private Sub CommandButton4_Click()
SourceRange.Value = "Hallo"
End Sub
Private Sub UserForm_Initialize()
' UN-BOUND ListBox
With Me.ListBox1
.ColumnCount = 5 ' ' For an unbound data source, there is a 10-column limit (0 to 9).
.MultiSelect = fmMultiSelectSingle
.BoundColumn = 1
.TextColumn = 2
' For a multicolumn ListBox or ComboBox, AddItem inserts an entire row,
' that is, it inserts an item for each column of the control.
' To assign values to an item beyond the first column, use the
' List or Column property and specify the row and column of the item.
.AddItem
.List(0, 0) = "Europe"
.List(0, 1) = "Germany"
.List(0, 2) = "Berlin"
.List(0, 3) = "12°C"
.List(0, 4) = "4°C"
.AddItem
.List(1, 0) = "United States"
.List(1, 1) = "Texas"
.List(1, 2) = "Dallas"
.List(1, 3) = "26°C"
.List(1, 4) = "14°C"
' Column Property >
' Specifies one or more items in a ListBox or ComboBox.
' object.Column( column, row ) [= Variant]
.AddItem
.Column(0, 2) = "Africa"
.Column(1, 2) = "Egypt"
.Column(2, 2) = "Luxor"
.Column(3, 2) = "36°C"
.Column(4, 2) = "18°C"
End With
Dim MeineListe(0 To 2, 0 To 4)
' ----------------------------------------------------
' oder einfacher, wenn man Daten im Excel hat, kann man
' eine Liste mit Excel Range fuellen :
' Dim MeineExcelListe As Variant
' MeineExcelListe = ActiveSheet.Range("a1:e3")
' Me.ListBox2.ColumnCount = ActiveSheet.Range("a1:e3").Columns.Count
' Me.ListBox2.List = MeineExcelListe
' -----------------------------------------------------
MeineListe(0, 0) = "Europe"
MeineListe(0, 1) = "Germany"
MeineListe(0, 2) = "Berlin"
MeineListe(0, 3) = "12°C"
MeineListe(0, 4) = "4°C"
MeineListe(1, 0) = "United States"
MeineListe(1, 1) = "Texas"
MeineListe(1, 2) = "Dallas"
MeineListe(1, 3) = "26°C"
MeineListe(1, 4) = "14°C"
MeineListe(2, 0) = "Africa"
MeineListe(2, 1) = "Egypt"
MeineListe(2, 2) = "Luxor"
MeineListe(2, 3) = "36°C"
MeineListe(2, 4) = "18°C"
With Me.ListBox2
.ColumnCount = 5
.BoundColumn = 4
.TextColumn = 5
.MultiSelect = fmMultiSelectSingle
.List = MeineListe
End With
' BOUND ListBox
With Me.ListBox3
' The RowSource property accepts worksheet ranges from Microsoft Excel.
Set SourceRange = ActiveSheet.Range("a1:e3")
.RowSource = SourceRange.Parent.Name & "!" & SourceRange.Address(False, False)
.ColumnCount = SourceRange.Columns.Count
.BoundColumn = 2
.TextColumn = 3
End With
End Sub