Solved: Userforms created with macro

[ericc]

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

[ericc]

Ok

I solve the issue, by writing the values to the sheet and read it from here
To do so, I modified the codemodule part:
With TempForm.codemodule
Line = .countoflines
.insertlines Line + 1, "Sub btnOK_Click()"
.insertlines Line + 2, "Dim i As Integer"
.insertlines Line + 3, "i = 1"
.insertlines Line + 4, "Dim cCont As Control"
.insertlines Line + 5, "For Each cCont In Me.Controls"
.insertlines Line + 6, "If TypeName(cCont) = " & Chr(34) & "ToggleButton" & Chr(34) & " Then"
.insertlines Line + 7, "If cCont.Value Then"
.insertlines Line + 8, "Cells(i, 256).Value = cCont.Caption"
.insertlines Line + 9, "i = i + 1"
.insertlines Line + 10, "End If"
.insertlines Line + 11, "End If"
.insertlines Line + 12, "Next"
.insertlines Line + 13, "Unload Me"
.insertlines Line + 14, "End Sub"
End With

I usually don't like to write temporally values to the sheet, but seems the easiest solution in this configuration

ericc

[Benzadeus]

Offtopic:
The last code you posted I'd do this way:
With TempForm.codemodule
Line = .countoflines
k = k + 1: .insertlines Line + k, "Sub btnOK_Click()"
k = k + 1: .insertlines Line + k, "Dim i As Integer"
k = k + 1: .insertlines Line + k, "i = 1"
k = k + 1: .insertlines Line + k, "Dim cCont As Control"
k = k + 1: .insertlines Line + k, "For Each cCont In Me.Controls"
k = k + 1: .insertlines Line + k, "If TypeName(cCont) = " & Chr(34) & "ToggleButton" & Chr(34) & " Then"
k = k + 1: .insertlines Line + k, "If cCont.Value Then"
k = k + 1: .insertlines Line + k, "Cells(i, 256).Value = cCont.Caption"
k = k + 1: .insertlines Line + k, "i = i + 1"
k = k + 1: .insertlines Line + k, "End If"
k = k + 1: .insertlines Line + k, "End If"
k = k + 1: .insertlines Line + k, "Next"
k = k + 1: .insertlines Line + k, "Unload Me"
k = k + 1: .insertlines Line + k, "End Sub"
End With

For maintance purposes, I believe it is easier this way...

[ericc]

Yes, you right: It's easier in this way :thumb
I will use it

Thanks Benzadeus

ericc

[mikerickson]

Creating Userforms on the fly has (at least) one problem, file bloat.
When the userform is deleted after use, the garbage collection is not complete and as time goes on, the file gets larger and larger without any increase in the amount of data in the file.

(Or at least that's what I've seen in Excel 2004.)

One way around this is to (at design time) create a blank(ish) userform and create the controls at run time. This avoids the repeated incomplete deletion of VBComponents (userforms).

[ericc]

Thanks for the advice ...
However I don't have the choice !!

I try to create a kind of "add-on" for an existing workbook: Just one sheet that I import with all the macro I need to improve/speed up my work

The workbook wasn't created by me and I don't want (can't) modify it.
That's why I can't use Global Public variables and certainly not create a userform with the designer (even an empty one)

In the other hand, I made some tests since 3 days and I created (and deleted) more than 100 temporary userforms and I don't notice any increase size of the file.
I use Excel 2003 SP3 ... May be the bug is now corrected ...

Anyway thanks for the information, I will keep an eye on this point

ericc