Dynamische Gültigkeitsliste

Home Zurück Vor

Download der Beispiel-Arbeitsmappe

Gruppe

Ereignis

Bereich

SelectionChange

Thema

Dynamische Gültigkeitsliste

Problem

In Zelle B2 soll eine Gültigkeitsliste angezeigt werden, deren Werte in Abhängigkeit zu dem in Zelle A2 eingetragenen Wert stehen.

Lösung

Darstellung nur anhand einer Beispielarbeitsmappe möglich.




StandardModule: Modul1

Sub SplitArray()
   Dim mdl As Object
   Dim arr(1 To 2, 1 To 12)
   Dim iCounter As Integer, iAct As Integer
   Dim sCode As String
   Application.VBE.MainWindow.Visible = False
   For iCounter = 1 To 12
      arr(1, iCounter) = iCounter
      arr(2, iCounter) = Format(DateSerial(1, iCounter, 1), "mmmm")
   Next iCounter
   sCode = "Sub NewArrays(arrWhole)" & vbLf
   sCode = sCode & "   Dim iAct as Integer" & vbLf
   For iCounter = 1 To UBound(arr, 1)
      sCode = sCode & "   Dim arr" & iCounter & "(1 to " & _
         UBound(arr, 2) & ")" & vbLf
      sCode = sCode & "   For iAct = 1 to " & _
         UBound(arr, 2) & vbLf
      sCode = sCode & "      arr" & iCounter & "(iAct) = arrWhole(" & _
         iCounter & ", iAct)" & vbLf
      sCode = sCode & "   Next iAct" & vbLf
      sCode = sCode & "   For iAct = 1 to " & _
         UBound(arr, 2) & vbLf
      sCode = sCode & _
         "      MsgBox ""Datenfeld "" & iAct & "" aus Array arr" & _
         iCounter & ": "" & arr" & iCounter & "(iAct)" & vbLf
      sCode = sCode & "   Next iAct" & vbLf
   Next iCounter
   sCode = sCode & "End Sub" & vbLf & vbLf
   Set mdl = ThisWorkbook.VBProject.VBComponents.Add(1)
   mdl.CodeModule.AddFromString sCode
   Call Aufruf(arr)
   ThisWorkbook.VBProject.VBComponents.Remove mdl
End Sub

Sub Aufruf(arrAct As Variant)
   Call NewArrays(arrAct)
End Sub