[VBA] Worksheet met Frame & separate functie controls setten

[BSeB]

Inmiddels heb ik een sheet waar in de juiste opmaak een frame gevuld wordt.
(Deze code kan verbeterd worden, maar dat doet nu niet ter zaken).

Nu wil ik als ik op een knop druk dat er een functie uitgevoerd wordt. Hier begint het probleem echter al. Dit werkt niet.
Het hele frame wordt dynamisch opgebouwd.
2 vragen zijn nu.
1. Hoe kan ik met clear een functie krijgen
2. Hoe kan ik vanuit die functie de controls die in een andere functie zijn gedefinieerd "setten", bijvoorbeeld de naam of de value?

Totale code:

Visual Basic .NET:

Global BaseFrame As Frame
Global Group_Box_01 As Frame
Global Group_Box_02 As Frame
Global Group_Box_03 As Frame
Global Label_01 As Control
Global Label_02 As Control
Global Radio_Button_01 As Control
Sub Metric_Chooser()

Dim Commandbutton_Clear As Control
Dim Commandbutton_Submit As Control

Dim Radio_Button_02 As Control
Dim Radio_Button_03 As Control
Dim Radio_Button_04 As Control
Dim Radio_Button_05 As Control
Dim Radio_Button_06 As Control
Dim Radio_Button_07 As Control
Dim Radio_Button_08 As Control

Dim Universal_Dropdown As Control

Dim Date_Chooser_01 As Control
Dim Date_Chooser_02 As Control


' Create Frame for Metric Chooser
Set BaseFrame = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Frame.1", Link:=False, _
        DisplayAsIcon:=False, Left:=10, Top:=10, _
        Width:=490, Height:=230).Object

With BaseFrame
    .Object.Caption = "Metric Requirement - Chooser"
    .Name = "BaseFrame"
    .Enabled = True
End With

Set Commandbutton_Clear = BaseFrame.Controls.Add("Forms.CommandButton.1", "Clear", True)
With Commandbutton_Clear
    .Left = 330
    .Top = 195
    .Width = 70
    .Height = 20
    .Name = "Clear"
    .Caption = "Clear"
    .Visible = True
    .Enabled = True
End With

Set Commandbutton_Submit = BaseFrame.Controls.Add("Forms.CommandButton.1", "Submit", True)
With Commandbutton_Submit
    .Left = 410
    .Top = 195
    .Width = 70
    .Height = 20
    .Name = "Submit"
    .Caption = "Submit"
End With

Set Group_Box_01 = BaseFrame.Controls.Add("Forms.Frame.1")
With Group_Box_01
    .Left = 170
    .Top = 10
    .Width = 150
    .Height = 175
    .Visible = True
    .Object.Caption = "Select Time Frame"
End With

Set Group_Box_02 = BaseFrame.Controls.Add("Forms.Frame.1")
With Group_Box_02
    .Left = 330
    .Top = 10
    .Width = 150
    .Height = 175
    .Visible = True
    .Object.Caption = "Select Metric Type"
End With

Set Group_Box_03 = BaseFrame.Controls.Add("Forms.Frame.1")
With Group_Box_03
    .Left = 10
    .Top = 10
    .Width = 150
    .Height = 175
    .Visible = True
    .Object.Caption = "Select Time Period"
End With
'Set Group_Box_01 = BaseFrame.Controls.Add("Frame")

Set Radio_Button_01 = Group_Box_03.Controls.Add("Forms.OptionButton.1")
With Radio_Button_01
    .Name = "Radio_Button"
    .Caption = "Day"
    .Left = 15
    .Top = 10
    .Width = 120
    .Height = 20
    .Value = True
    .Visible = True
End With

Set Radio_Button_02 = Group_Box_03.Controls.Add("Forms.OptionButton.1")
With Radio_Button_02
    .Left = 15
    .Top = 35
    .Caption = "Week"
   .Width = 120
    .Height = 20
    .Visible = True
End With

Set Radio_Button_03 = Group_Box_03.Controls.Add("Forms.OptionButton.1")
With Radio_Button_03
    .Left = 15
    .Top = 60
    .Caption = "Month"
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Radio_Button_04 = Group_Box_03.Controls.Add("Forms.OptionButton.1")
With Radio_Button_04
   .Left = 15
    .Top = 85
    .Caption = "Quarter"
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Radio_Button_05 = Group_Box_03.Controls.Add("Forms.OptionButton.1")
With Radio_Button_05
    .Left = 15
    .Top = 110
    .Caption = "Year"
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Radio_Button_06 = Group_Box_03.Controls.Add("Forms.OptionButton.1")
With Radio_Button_06
    .Left = 15
    .Top = 135
    .Caption = "Period"
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Radio_Button_07 = Group_Box_02.Controls.Add("Forms.OptionButton.1")
With Radio_Button_07
    .Left = 15
    .Top = 10
    .Caption = "Units per Manhour"
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Radio_Button_08 = Group_Box_02.Controls.Add("Forms.OptionButton.1")
With Radio_Button_08
    .Left = 15
    .Top = 35
    .Caption = "Costs per Unit"
    .Width = 120
    .Height = 20
    .Visible = True
End With

' Create all specific drop downs in middel group frame Group_Box_01

Set Label_01 = Group_Box_01.Controls.Add("Forms.Label.1")
With Label_01
    .Left = 15
    .Top = 20
    .Caption = "Week:" 'Week,Month,Quarter,Year, Start Date
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Label_02 = Group_Box_01.Controls.Add("Forms.Label.1")
With Label_02
    .Left = 15
    .Top = 70
    .Caption = "End Date:" 'End Date
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Universal_Dropdown = Group_Box_01.Controls.Add("Forms.ComboBox.1")
With Universal_Dropdown
    .Left = 15
    .Top = 35
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Date_Chooser_01 = Group_Box_01.Controls.Add("MSComCtl2.DTPicker.2")
With Date_Chooser_01
    .Left = 15
    .Top = 35
    .Width = 120
    .Height = 20
    .Visible = True
End With

Set Date_Chooser_02 = Group_Box_01.Controls.Add("MSComCtl2.DTPicker.2")
With Date_Chooser_02
    .Left = 15
    .Top = 85
    .Width = 120
    .Height = 20
    .Visible = True
End With

BaseFrame.Select
Selection.Verb Verb:=xlPrimary

End Sub
Function Clear_Function()
Label_01.Value = "Text"
End Function

[pedorus]

Ik snap eigenlijk net waarom dit allemaal dynamisch moet en je niet gewoon de designer gebruikt als er gewoon sprake is van vaste plaatsen enzo. Voor de events, zie een artikel over WithEvents.

[BSeB]

Withevents is volgens mij niet wat ik nodig heb.
Het is inderdaad allemaal staisch gegenereerd, maar als ik dan op een knop druk dan moet daar code achter, ik kan alleen niet vinden waar en hoe ik die moet plaatsen zodat het werkt.