ericc
04-09-2009, 05:26 AM
Hello all
I'm completely blocked :( so I come here for a little help ....
I have a macro which dynamically create a userform with some ToggleButton
Here is the code :
Function MakeUserForm(ByRef scenario() As String) As Object
Dim TempForm As Object
Dim NewFrame As MSForms.Frame
Dim NewToggleButton As MSForms.ToggleButton
Dim NewButton As MSForms.CommandButton
Dim X As Integer
Dim Line As Integer
Dim MyScript(4) As String
'This is to stop screen flashing while creating form
'Application.VBE.MainWindow.Visible = False
'Create the User Form
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
With TempForm
.Properties("Caption") = "Export Scenario"
.Properties("Width") = 240
.Properties("Height") = 300
End With
' Create a nice frame
Set NewFrame = TempForm.designer.Controls.Add("Forms.frame.1")
With NewFrame
.Name = "cadre"
.Caption = "Select scenario"
.Width = 200
.Height = 230
.TOP = 10
.Left = 20
.SpecialEffect = fmSpecialEffectEtched
End With
' Create a togglebutton per scenario
For X = 0 To 9
Set NewToggleButton = NewFrame.Controls.Add("Forms.togglebutton.1")
With NewToggleButton
.Name = "MyTogg" & X + 1
'.Caption = "Scenario " & X + 1
.Caption = scenario(X)
.Width = 100
.Height = 18
.TOP = 10 + (20 * X)
.Left = (200 - 100) / 2
.Font.Size = 7
.Font.Name = "Verdana"
End With
Next
Set NewButton = TempForm.designer.Controls.Add("Forms.commandbutton.1")
With NewButton
.Name = "btnOK"
.Caption = "OK"
.Width = 50
.Height = 20
.Font.Size = 7
.Font.Name = "Verdana"
.TOP = 250
.Left = (240 - 50) / 2
End With
'Create Event Handler Code For Each Check Box
With TempForm.codemodule
Line = .countoflines
.insertlines Line + 1, "Sub btnOK_Click()"
.insertlines Line + 2, "msgbox( MyTogg1.Value)"
.insertlines Line + 3, "Me.Hide"
.insertlines Line + 4, "End Sub"
End With
'Show the form
VBA.UserForms.Add(TempForm.Name).Show
'MsgBox (VBA.UserForms.TempForm.cadre.MyTogg1.Value)
MsgBox (TempForm.cadre.MyTogg1.Value)
Set MakeUserForm = TempForm
'Delete the form (Optional)
'ThisWorkbook.VBProject.VBComponents.Remove TempForm
End Function
This function is called by the following procedure :
Sub testform()
Dim sce(9) As String
Dim rng As Range
Dim c As Range
Dim idx As Integer
Dim formreturn As Object
Dim choose(9) As String
Set rng = Sheets("Scenarios").Range("A1:A10")
idx = 0
For Each c In rng.Cells
sce(idx) = c.Value
idx = idx + 1
Next
'MakeUserForm scenario:=sce
Set formreturn = MakeUserForm(scenario:=sce)
MsgBox (formreturn.Name)
'MsgBox (formreturn.MyTogg1.Value)
End Sub
When I call "testform()" , the userform is created with all buttons as expected, and close (hide) when I press "Ok" button
My problem now is to read which ToggleButton the user select.
If I read it from the form itself it work, but How can I pass the values to the procedure (testform) ?
Nota: all this macro are in a sheet and can't be in a module for some reasons too long to explain, so apparently I can't declare a Public Array from this place
If I try to read it from the procedure (or the function) I receive an error :
"Object doesn't support this property or method" :banghead:
I think I don't use the correct syntax !!
Can someone help me on this tricky problem ?
I attach a workbook with the code inside
ericc
I'm completely blocked :( so I come here for a little help ....
I have a macro which dynamically create a userform with some ToggleButton
Here is the code :
Function MakeUserForm(ByRef scenario() As String) As Object
Dim TempForm As Object
Dim NewFrame As MSForms.Frame
Dim NewToggleButton As MSForms.ToggleButton
Dim NewButton As MSForms.CommandButton
Dim X As Integer
Dim Line As Integer
Dim MyScript(4) As String
'This is to stop screen flashing while creating form
'Application.VBE.MainWindow.Visible = False
'Create the User Form
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
With TempForm
.Properties("Caption") = "Export Scenario"
.Properties("Width") = 240
.Properties("Height") = 300
End With
' Create a nice frame
Set NewFrame = TempForm.designer.Controls.Add("Forms.frame.1")
With NewFrame
.Name = "cadre"
.Caption = "Select scenario"
.Width = 200
.Height = 230
.TOP = 10
.Left = 20
.SpecialEffect = fmSpecialEffectEtched
End With
' Create a togglebutton per scenario
For X = 0 To 9
Set NewToggleButton = NewFrame.Controls.Add("Forms.togglebutton.1")
With NewToggleButton
.Name = "MyTogg" & X + 1
'.Caption = "Scenario " & X + 1
.Caption = scenario(X)
.Width = 100
.Height = 18
.TOP = 10 + (20 * X)
.Left = (200 - 100) / 2
.Font.Size = 7
.Font.Name = "Verdana"
End With
Next
Set NewButton = TempForm.designer.Controls.Add("Forms.commandbutton.1")
With NewButton
.Name = "btnOK"
.Caption = "OK"
.Width = 50
.Height = 20
.Font.Size = 7
.Font.Name = "Verdana"
.TOP = 250
.Left = (240 - 50) / 2
End With
'Create Event Handler Code For Each Check Box
With TempForm.codemodule
Line = .countoflines
.insertlines Line + 1, "Sub btnOK_Click()"
.insertlines Line + 2, "msgbox( MyTogg1.Value)"
.insertlines Line + 3, "Me.Hide"
.insertlines Line + 4, "End Sub"
End With
'Show the form
VBA.UserForms.Add(TempForm.Name).Show
'MsgBox (VBA.UserForms.TempForm.cadre.MyTogg1.Value)
MsgBox (TempForm.cadre.MyTogg1.Value)
Set MakeUserForm = TempForm
'Delete the form (Optional)
'ThisWorkbook.VBProject.VBComponents.Remove TempForm
End Function
This function is called by the following procedure :
Sub testform()
Dim sce(9) As String
Dim rng As Range
Dim c As Range
Dim idx As Integer
Dim formreturn As Object
Dim choose(9) As String
Set rng = Sheets("Scenarios").Range("A1:A10")
idx = 0
For Each c In rng.Cells
sce(idx) = c.Value
idx = idx + 1
Next
'MakeUserForm scenario:=sce
Set formreturn = MakeUserForm(scenario:=sce)
MsgBox (formreturn.Name)
'MsgBox (formreturn.MyTogg1.Value)
End Sub
When I call "testform()" , the userform is created with all buttons as expected, and close (hide) when I press "Ok" button
My problem now is to read which ToggleButton the user select.
If I read it from the form itself it work, but How can I pass the values to the procedure (testform) ?
Nota: all this macro are in a sheet and can't be in a module for some reasons too long to explain, so apparently I can't declare a Public Array from this place
If I try to read it from the procedure (or the function) I receive an error :
"Object doesn't support this property or method" :banghead:
I think I don't use the correct syntax !!
Can someone help me on this tricky problem ?
I attach a workbook with the code inside
ericc