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