Problem with Blocking Dynamically generated UserForm Call

[vmegha]

Hi,

I have a dynamically generated User Form , that returns me a values,
which I use to Make Calls to a Tibco RV service. For some reason, The
return from the form is blocking further execution...

Here's the code further below and and here's the call just below

Selected = PgmListForm.GetProgramCheckList(PgmList)
'Selected = "FRED_1216_A,TEST_PAN_1216,"
MsgBox Selected

The commented code, which has exactly the same value as the return
value for a particular case and works fine, but the Call the Form
Function blocks the rest of the execution....
I dont think the UserForm has problems except the formatting.

Any help will be greately Appreciated,

Thanks,
Megha

Form :

Function GetProgramCheckList(ByVal ItemList As Collection) As String

Dim X As Integer, i As Integer, TopPos As Integer
Dim MaxWidth As Long
Dim LeftPos As Long
Dim TempForm 'As VBComponent
Dim NewListBox1 As MSForms.ListBox
Dim NewListBox2 As MSForms.ListBox

Dim NewCommandButton1 As MSForms.CommandButton
Dim NewCommandButton2 As MSForms.CommandButton
Dim NewCommandButton3 As MSForms.CommandButton
Dim NewCommandButton4 As MSForms.CommandButton

Set SharedItemList = ItemList
'Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False

'Create the UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
TempForm.Properties("Width") = 430
TempForm.Properties("Height") = 150
'Add the OptionButtons
TopPos = 4
MaxWidth = 0 'Stores width of widest OptionButton
LeftPos = 8


Set NewListBox1 =
TempForm.Designer.Controls.Add("forms.ListBox.1")
With NewListBox1
.Width = 100
.MultiSelect = fmMultiSelectExtended
.Height = 164
.Left = 36
.Top = 25
.Tag = "From"

End With


Set NewListBox2 =
TempForm.Designer.Controls.Add("forms.ListBox.1")
With NewListBox2
.Width = 100
'.MultiSelect = fmMultiSelectExtended
.Height = 40
.Left = 192
.Top = 25
.Tag = "To"

End With

'Add the Program
Set NewCommandButton1 =
TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton1
.Caption = ">>"
.Height = 18
.Width = 25
.Left = 150
.Top = 40
End With

'Remove the Program
Set NewCommandButton2 =
TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton2
.Caption = "<<"
.Height = 18
.Width = 25
.Left = 150
.Top = 60
End With

'Add the Cancel button
Set NewCommandButton3 =
TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton3
.Caption = "Cancel"
.Height = 18
.Width = 44
.Left = 312
.Top = 25

End With

'Add the OK button
Set NewCommandButton4 =
TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton4
.Caption = "OK"
.Height = 18
.Width = 44
.Left = 312
.Top = 45
End With
'Add event-hander subs for the CommandButtons
With TempForm.CodeModule
X = .CountOfLines
.InsertLines X + 1, "Sub CommandButton3_Click()"
.InsertLines X + 2, " Dim optionList As String "
.InsertLines X + 3, " Unload Me"
.InsertLines X + 4, "End Sub"

.InsertLines X + 5, "Private Sub UserForm_Initialize()"
.InsertLines X + 6, " Dim Ctl"
.InsertLines X + 7, " Dim i As Integer "
.InsertLines X + 8, " For Each Ctl In
Me.Controls"
.InsertLines X + 9, " If Ctl.Tag =
""From"" Then "
.InsertLines X + 10, " For i = 1 To
SharedItemList.Count"
.InsertLines X + 11, " If
(SharedItemList.item(i).Name <> """") Then"
.InsertLines X + 12, "
Ctl.AddItem SharedItemList.item(i).Name"
.InsertLines X + 13, " End If"
.InsertLines X + 14, " Next i"
.InsertLines X + 15, " End If"
.InsertLines X + 16, " Next Ctl"
.InsertLines X + 17, "End Sub"

.InsertLines X + 18, "Sub CommandButton1_Click()"
.InsertLines X + 19, " Dim i As Integer "
.InsertLines X + 20, " If ListBox1.ListIndex = -1 Then
Exit Sub "
.InsertLines X + 21, " For i = ListBox1.ListCount - 1
To 0 Step -1"
.InsertLines X + 22, " If ListBox1.Selected(i) = True
Then"
.InsertLines X + 23, " ListBox2.AddItem
ListBox1.list(i)"
.InsertLines X + 24, " End If"
.InsertLines X + 25, " Next i"
.InsertLines X + 26, "End Sub"

.InsertLines X + 27, "Sub CommandButton2_Click()"
.InsertLines X + 28, " Dim i As Integer "
.InsertLines X + 29, " If ListBox2.ListIndex = -1 Then
Exit Sub "
.InsertLines X + 30, " For i = ListBox2.ListCount - 1
To 0 Step -1"
.InsertLines X + 31, " If ListBox2.Selected(i) = True
Then"
.InsertLines X + 32, " ListBox2.RemoveITem i"
.InsertLines X + 33, " End If"
.InsertLines X + 34, " Next i"
.InsertLines X + 35, "End Sub"


.InsertLines X + 36, "Sub CommandButton4_Click()"
.InsertLines X + 37, " Dim OptionList As String, i As
Integer "
.InsertLines X + 38, " For i = ListBox2.ListCount - 1
To 0 Step -1"
.InsertLines X + 39, " OptionList = OptionList +
ListBox2.list(i) + "","" "
.InsertLines X + 40, " Next i"
.InsertLines X + 41, " 'MsgBox optionList "
.InsertLines X + 42, " ReturnedValue = OptionList "
.InsertLines X + 43, " Unload Me"
.InsertLines X + 44, "End Sub"

End With
'Adjust the form
With TempForm
.Properties("Caption") = Constants.PROGRAM_LIST_TITLE
' .Properties("Width") = NewCommandButton1.Left +
NewCommandButton1.Width + 300
' If .Properties("Width") < 160 Then
' .Properties("Width") = 160
' NewCommandButton1.Left = 106
' NewCommandButton2.Left = 106
' End If
' .Properties("Height") = TopPos + 300
End With

'Show the form
VBA.UserForms.Add(TempForm.Name).Show

'Delete the form
ThisWorkbook.VBProject.VBComponents.Remove
VBComponent:=TempForm

GetProgramCheckList = ReturnedValue
End Function

 

[DM Unseen]

You need to hide the form before the execution continues.
so some code like: Me.Hide is needed

this will hide the form and the caller routine will continue where it
left. You can only destroy/unload the form *after* you received the
return value from the form.

Normally I use the VBAIDE library for development tools, not for end
user applications. There are also several security issues as well, as
well as project bloat/unstability etc. I also cannot see your code
actually needs this feature. Just create the form in the normal way,
and change its desired appearance at run-time.

If you just want to have several form *instances* of your userform you
can use:
(e.g. there is a dummy from frmDummy)
Dim frmNew as New frmDummy to create a new instance of this form. Using
arrays or collections you can create as much instances as you like.

Dm Unseen

 

[vmegha]

You can see I'm deleteing the form here

'Delete the form
ThisWorkbook.VBProject.VBComponents.Remove
VBComponent:=TempForm

And i'm unloading the form after the selection is made and the OK is
hit

InsertLines X + 43, " Unload Me"

Is it still a problem?

-Megha

 

[DM Unseen]

this is not going to work. the scope of the variable ReturnedValue is
within the form (Maybe turn on Option Explicit?). Standard practice of
returning values from a userform is:

Define a property proc inside the form:

Property Get MyVal() as variant


Sub getvalue() as variant
frmtest.show
'press OK button this hides the form and assignes the Proerty Myval the
correct value
getvalue = frmtest.Myval
unload frmtest
end sub

You miss the essential parts to actually read the forms return value.

DM Unseen

 

[vmegha]

I tried this ( In the form I'm not allowed to Get and Let Variables)...
I tried something like this
.InsertLines X + 2, " Dim TheReturnValue As String "
.InsertLines X + 3, " Property Get ReturnValue() As
String"
.InsertLines X + 4, " ReturnValue= ReturnValue"
.InsertLines X + 5, " End Property"
.InsertLines X + 6, " Property Let
ReturnValue(ReturnValue As String)"
.InsertLines X + 7, " ReturnValue = ReturnValue"
.InsertLines X + 8, " End Property"
And incidentally this is one of my 3 forms, and the other two forms
dont misbehave...
ReturnedValue is a global Variable that is set before the form unloads


I tried Hiding it, Nothing seems to work

-Megha