Reach a form created at runtime within a Function

[eros]

Dear all,

I have the following code:

Function ShowMyForm(x As Integer, str As String, dbl As Double)
   Dim Form As New myUserForm
   Load Form
   Form.Label1.Caption = "Test"
   Form.Label2.Caption = "Test"
   Form.Label3.Caption = "Test"
   Form.Show VbModeless
End Function

The function builds a form and loads it. It stays in memory and allows other code execution. There are several forms created in this way during my program execution.

I want to reach out these forms and change some of the controls such as Label2 and Label3.

How can I do this from within another function - not from the function which created the actual form.

Is there a way to sequentially loop the controls in the form -in our case, 3 different label controls?

Many thanks

 

[tweedle]

Some quick code to illustrate the declaration of the form to make it available across more than the instanciating function; and looping controls...

Dim Form As MyUserForm 'Dim the Form in Declarations to make it available across multiple functions within the module
'Public Form As MyUserForm 'use Public to make it available throughout all code modules
 
Sub Test()
  'Test calls to functions
  ShowMyForm 0, "Tweedle", 1001
  TweakForm
End Sub

Function ShowMyForm(x As Integer, str As String, dbl As Double)
   'Create the Form object
   Set Form = New MyUserForm
   Load Form
   Form.Label1.Caption = "Test"
   Form.Label2.Caption = "Test"
   Form.Label3.Caption = "Test"
   Form.Show vbModeless
End Function
 
Function TweakForm()
Dim ctrl As MSForms.Control
If Not Form Is Nothing Then 'make sure we have a Form loaded/set
    With Form
        For Each ctrl In Form.Controls 'Loop Controls
            If TypeOf ctrl Is MSForms.Label Then ' if its a label then do something w/it
                ctrl.ControlTipText = "Help!"
            End If
        
            Select Case Left(ctrl.Name, 4)
                 Case Is = "Labe"
                    Debug.Print "Label: "; ctrl.Name
                 Case Is = "Text"
                 Case Is = "List"
                 Case Is = "Comb"
                 Case Is = "etc."
            End Select
        Next
    End With
Else
    Debug.Print "Err in TweakForm: No Form Available"
End If
End Function

 

[eros]

That's fantastic. Thank you for the code, quite enlightening. Just 2 questions:
1-Your code assumes there is only one form loaded in memory. In fact, I am keen to "instanciate" several forms at a particular time from within different functions. How can I loop between these forms (very much like as with Label controls) and reach them out individually to make changes in their controls?
2-On a different but related matter, I am trying to use a UserForm to keep track of numerous variables calculated on-the-fly. Functions do not allow me to save variables in the Excel cells. Therefore, I decided to use forms which can be accessed and modified during program execution. Each record is to consist of an integer, a string and a double. What I plan to do is to create a line of 3 textboxes for each record and keep them updated throuhgout program execution. What would be the best and simplest way to achieve this? I expect to have max 10 records (i.e. 10 lines of 3 textboxes on the form). I will need to delete, say, record no.2 which means overwriting the values of the subsequent textboxes onto the previous record elements. Record data is kept in an array. I hope I am not reinventing the wheel.

Many thanks in advance.

 

[tweedle]

How can I loop between these forms (very much like as with Label controls) and reach them out individually to make changes in their controls?

'declarations
Public gForm As UserForm

Function EnumUserForms()
For Each gForm In UserForms
    Debug.Print gForm.Controls.Count
Next
End Function

As for Part 2; that's a bit more detailed....
Here's some sample that may help:
{should be able to dump this into a new workbook and let it fly.}

'Declarations, creating a public array that can be sized using a constant
'The constant is used later to determine the controls to create 
Option Base 0 'Forcing Arrays to Index @ zero, s/b default
Const ArrWidth = 3
Public YourArrayHere(9, ArrWidth)

I'm using a controller routine as follows:

Sub Main2()
BuildForm 'Create the form based on array dimensions
FloodArray 'Flooding my sample array
FloodArrayToForm UserForms(UserForms.Count - 1) 'Push  Array vals to form controls
UserForms(UserForms.Count - 1).Repaint ' Probably not necessary, but eh
End Sub

Supporting Functions:

Function BuildForm()
'Builds a form of Labels based on known array "YourArrayHere"
 
Dim aCtrl As Control
   'Create the Form object
   Set Form = New MyUserForm
   Load Form
   Form.Show vbModeless 'Debugging
   Form.Move 10, 10 'Debugging
 
   For x = LBound(YourArrayHere) To ((UBound(YourArrayHere)) * ArrWidth) + ArrWidth - 1
 
    Set aCtrl = Form.Controls.Add("Forms.Label.1")
        aCtrl.BorderStyle = 1
        aCtrl.Visible = True 's/b the default
 
'Calc Control placement based on where its value in the array comes from 
    If x = LBound(YourArrayHere) Then
        aCtrl.Left = 10
        aCtrl.Top = 10
    Else
 
    If x Mod ArrWidth <> 0 Then
        aCtrl.Left = Form.Controls(x - 1).Left + Form.Controls(x - 1).Width + 10
        aCtrl.Top = Form.Controls(x - 1).Top
    Else
        aCtrl.Left = Form.Controls(0).Left
        aCtrl.Top = Form.Controls(x - 1).Top + Form.Controls(x - 1).Height + 5
    End If
 
    End If
    Next x
    Form.Height = Form.Controls(Form.Controls.Count - 1).Top + (Form.Controls(Form.Controls.Count - 1).Height * 3)
    Form.Width = Form.Controls(Form.Controls.Count - 1).Left + (Form.Controls(Form.Controls.Count - 1).Width * 1.5)
    Form.Show vbModeless
    Form.Move 10, 10
End Function

Function FloodArray()
For ArrIdx = LBound(YourArrayHere) To UBound(YourArrayHere)
    For ElIdx = 0 To ArrWidth - 1
        YourArrayHere(ArrIdx, ElIdx) = Application.WorksheetFunction.RandBetween(1, 10000)
    Next ElIdx
Next ArrIdx
End Function

Function FloodArrayToForm(frm As UserForm)
'Pushes array values to the form controls. 
'When Array values change, call this routing to completely refill the
'controls, not really caring if Array Row 3 moves to Array Row 2 
 
If Not frm Is Nothing Then 'make sure we have a Form loaded/set
ctrlIdx = 0
For ArrIdx = LBound(YourArrayHere) To UBound(YourArrayHere)
 
For ElIdx = 0 To ArrWidth - 1
Set ctrl = Form.Controls(ctrlIdx)
If TypeOf ctrl Is MSForms.Label Then ' if its a label then do something w/it
ctrl.Caption = YourArrayHere(ArrIdx, ElIdx)
End If
ctrlIdx = ctrlIdx + 1
Next ElIdx
Next ArrIdx
Else
'No Form
End If
End Function

 

[Norie]

Instead of mutliple dynamically created forms, why not create one form with a 3 - column listbox on it.

Make it modeless and it can be shown but still allow the user to use the worksheets.

When you want to delete a row/set of values just use the RemoveItem method of the listbox.

All the other items will move up.

If you want to add an item simply use AddItem and List/Column.

That will put the new item at the bottom of the 'list' but of you want you can specify where the position it should go in the list.

 

[eros]

Wow! Great hints and code indeed. Many many thanks.

I will need some time to digest all these and tailor for my needs.

I will revert should I need more of your kind assistance.

Many thanks again.

 

[eros]

Norie,

A Listbox apparently has some advantages in adding/removing items easily. Thanks for the hint indeed. However, I wonder if I used the listbox option, whether I could make all listbox lines visible on the form? I thought a listbox is good to make a selection out of several lines of options. But, I won't need to select any particular line of the listbox, I just need all 10 lines visible and updated on the form all the time during program execution.
I also understand a listbox option also provides direct links to Excel cells which cannot be accessed from within functions. If I understand this correctly, this is great and give me a lot of different ways to approach to my problem.
Many thanks

 

[Norie]

I'm not quite sure what you are asking exactly.

I just think it might be worth looking into other options than creating multiple forms/controls dynamically.

That might get a bit messy.

 

[eros]

Norie,

I hardly think I have options other than UserForms. I have to run a code, checking/verifying several variables on-the-fly. Therefore, I have to use Functions as opposed to Subs. During code exection I need to see the instant values which the global variables and arrays hold. And now, I am trying to hold all these variables in a listbox-looking table which is updated as the vba code runs within functions. Well, I have several public functions which contribute evaluation of variables and access the form to update relevant variables.

A-listbox-looking table means 10 rows (i.e. items) each having 3 columns of data. I just need this dynamic table to be shown on the form as my output window of the vba code. Number of rows may reach up to 10 during execution, but it may as well be less than 10. Therefore, when specific conditions are met, I need to add or remove some rows from the table. Using a listbox appeared to be a good hint as it would allow me add/remove items easily; however, in my trials, I could not reach listbox items across functions. It simply generates #VALUE. However, it works well with sub, but I rely on the code within my functions to evaluate listbox variables. Therefore, I think I will have to give up the listbox option.

Other than the listbbox option, I have another one -as you call it, a "messy option". Do you really think there are other ways to achieve the same? So far, Tweedle's code seems to be the most sensible way forward. I have already adapted partial code into my functions and it worked well.
Unless there is a better suggestion, I will proceed with the messy option. It feels like I am reinventing the wheel. Therefore, before I go further I just wanted to consult you professionals for the way to follow.

Kind regards,

 

[RoryA]

Just for the record, the only real difference between a function and a sub is that a function returns a value and can (with limitations) be called from worksheet cell. Hence I do not follow your statement "I could not reach listbox items across functions" - if you can do it with a sub, you can do it with a function.