ComboBox

Private Sub ComboBox1_DropButtonClick() If Range("A1") = 1 Then ActiveSheet.ComboBox1.ListFillRange = "Muster!I4:I20" ActiveSheet.Range("D5") = ActiveSheet.ComboBox1 'ausgewählte in D5 setzen ElseIf Range("A1") = 2 Then ActiveSheet.ComboBox1.ListFillRange = "Muster!M4:M20" ActiveSheet.Range("D5") = ActiveSheet.ComboBox1 Else ActiveSheet.ComboBox1.ListFillRange = "" End If End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Ooooooops,
die Ausgabe des Wertes aus Spalte 2 hab ich vergessen.

Private Sub ComboBox1_DropButtonClick()
    If Range("A1").Value = 1 Then
        With Worksheets("Muster")
            ComboBox1.ListFillRange = .Range(.Cells(4, 9), _
                .Cells(.Rows.Count, 9).End(xlUp)).Resize(, 2).Address(External:=True)
        End With
        With ComboBox1
            .ColumnCount = 2
            .TextColumn = 2
            .BoundColumn = 2
            Range("D5").Value = .Text 'ausgewählte in D5 setzen
        End With
    ElseIf Range("A1") = 2 Then
        With ComboBox1
            .ListFillRange = "Muster!M4:M20"
            .ColumnCount = 1
            .TextColumn = -1
            .BoundColumn = 1
            Range("D5").Value = .Text
        End With
    Else
        ComboBox1.ListFillRange = ""
    End If
End Sub

Gruß
Nepumuk

Hallo sigrid,
ich war so frei das Ganze noch etwas umzubauen:

Option Explicit

Private mblnNoChange As Boolean

Private Sub ComboBox1_Change()
    If Not NoChange Then
        With ComboBox1
            If .ListFillRange <> vbNullString Then If Cells(1, 1).Value = 1 Then _
                Cells(5, 4).Value = .Value Else Cells(5, 5).Value = .Value
        End With
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        NoChange = True
        If Target.Value = 1 Then
            With Worksheets("Muster")
                ComboBox1.ListFillRange = .Range(.Cells(4, 9), _
                    .Cells(.Rows.Count, 9).End(xlUp)).Resize(, 2).Address(External:=True)
            End With
            With ComboBox1
                .ListIndex = -1
                .ColumnWidths = "50;50"
                .ColumnCount = 2
                .TextColumn = 1
                .BoundColumn = 2
                .Text = Cells(5, 4).Value
            End With
        ElseIf Target.Value = 2 Then
            With ComboBox1
                .ListFillRange = "Muster!L4:M20"
                .ListIndex = -1
                .ColumnWidths = "0;50"
                .ColumnCount = 2
                .TextColumn = 2
                .BoundColumn = 2
                .Text = Cells(5, 5).Value
            End With
        Else
            ComboBox1.ListFillRange = ""
        End If
        NoChange = False
    End If
End Sub

Private Property Get NoChange() As Boolean
    NoChange = mblnNoChange
End Property

Private Property Let NoChange(ByVal pvblnNoChange As Boolean)
    mblnNoChange = pvblnNoChange
End Property

Gruß
Nepumuk

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

ComboBox

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread