AW: 3 voneinander abhängige Floatboxen.
18.04.2008 09:38:00
Renee
Hi nochmals,
Wirf den Code für das Worksheet_Activate Event weg.
Dieser Code im SelectionChange Event genügt:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objDic As Object
Dim lRow As Long
Application.EnableEvents = False
Set objDic = CreateObject("Scripting.Dictionary")
lRow = 2
On Error Resume Next
If Target.Column = 2 And Target.Row > 1 And Target.Cells.Count = 1 Then
Do
objDic.Add Sheets("Daten").Cells(lRow, 1).Value, Cells(lRow, 1).Value
lRow = lRow + 1
Loop While Not (IsEmpty(Sheets("Daten").Cells(lRow, 1)))
ComboBox1.List = objDic.Keys
ComboBox1.Left = Target.Left
ComboBox1.Top = Target.Top
ComboBox1.LinkedCell = Target.Address
If Not (ComboBox1.Visible) Then ComboBox1.Visible = Not (ComboBox1.Visible)
Else
If ComboBox1.Visible Then ComboBox1.Visible = Not (ComboBox1.Visible)
End If
If Target.Column = 4 And Target.Row > 1 And Target.Cells.Count = 1 Then
Do
If Target.Offset(0, -2).Value = Sheets("Daten").Cells(lRow, 1).Value Then _
objDic.Add Sheets("Daten").Cells(lRow, 2).Value, Cells(lRow, 2).Value
lRow = lRow + 1
Loop While Not (IsEmpty(Sheets("Daten").Cells(lRow, 1)))
ComboBox2.List = objDic.Keys
ComboBox2.Left = Target.Left
ComboBox2.Top = Target.Top
ComboBox2.LinkedCell = Target.Address
If Not (ComboBox2.Visible) Then ComboBox2.Visible = Not (ComboBox2.Visible)
Else
If ComboBox2.Visible Then ComboBox2.Visible = Not (ComboBox2.Visible)
End If
If Target.Column = 6 And Target.Row > 1 And Target.Cells.Count = 1 Then
Do
If Target.Offset(0, -2).Value = Sheets("Daten").Cells(lRow, 2).Value Then _
objDic.Add Sheets("Daten").Cells(lRow, 3).Value, Cells(lRow, 3).Value
lRow = lRow + 1
Loop While Not (IsEmpty(Sheets("Daten").Cells(lRow, 1)))
ComboBox3.List = objDic.Keys
ComboBox3.Left = Target.Left
ComboBox3.Top = Target.Top
ComboBox3.LinkedCell = Target.Address
If Not (ComboBox3.Visible) Then ComboBox3.Visible = Not (ComboBox3.Visible)
Else
If ComboBox3.Visible Then ComboBox3.Visible = Not (ComboBox3.Visible)
End If
On Error GoTo 0
Set objDic = Nothing
Application.EnableEvents = True
End Sub
GreetZ Renée