Dynamic control arrays (arrays of visual objects) 5

[krinid]

Does anyone know how to create control arrays?
I'm interested in creating these at both design and run-time. It's easy enough in VB, but I haven't found a way to do it in VBA yet. In particular, I'm trying to create an array of buttons, checkboxes and radio buttons.

 

[xlbo]

Not sure on this but I believe you need to create your own Class to do this
I've never done it personally but I seem to remember some discussion in this area on one of my XL distribution lists

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[krinid]

I've figured out a way to do it:

Dim Q(5), counter As Integer
For counter = 1 To 5
Set Q(counter) = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", top:=counter * 100, left:=counter * 100, Width:=100, Height:=100)
Next

 

[krinid]

One further question: How can I create events for these dynamic objects?

Not much point in making a button if I can't catch the _click event.

 

[RogADog]

Going back to your original question, there's a method I have used successfully in the past but its more long winded than in VB.

If you want an array of 10 CommandButtons, you name them cmd01 to cmd10. You can then loop through them as though they were an array:

For Cntr = 1 to 10
Me.Controls("cmd" & Format(Cntr,"00")).Caption = "Hi"
Next Cntr

You then have to have one click event for each control but you can obviously keep the amount of coding down by the 10 event handlers all calling the one main routine.

Hope this helps.

Rog

 

[krinid]

Rog,
Thanks for the reply. Do you mean create static command buttons? My problem is that I don't know how many objects are required - could be none, 10 or 500 or more, depending on the data at execution time. I need them to be dynamic.

 

[krinid]

Anyone know of any good reference sites for VBA regarding dynamic objects? Surely someone out has figured it out and put it on a website for all to benefit Or am I just being optimistic?

 

[krinid]

Anyone,
Are there any form level general event catchers? Something that may not tell me exactly what object was changed, but at least that an event has happened?

 

[RogADog]

Unfortunately I can't find the now obsolete system I used it on but if I remember correctly I overcame the click event hurdle by designing a form full of static controls and then hid those that weren't needed. If the number of controls needed was more than on the form I had some scroll type buttons to give the user the impression of scrolling. The relationship between the data and the controls was managed using a dynamic array as the key to which record was currently displayed in which control.

These days I would use VB6 for it but I appreciate it's not always possible to do so.

Good luck!

Rog

 

[xlbo]

Check out this mailing list:

http://peach.ease.lsoft.com/archives/excel-l.html#SUB

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[xlbo]

Sorry - should probably explain.....
There are at least 3 MS Excel MVPs on that email distribution list. I'm sure I've seen them discussing control arrays before. You should be able to subscribe and shoot off a question pretty easily

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[krinid]

RogADog,
Yea, in VB it would be no problem - done it before, which is why I figured I could do it in VBA, but unfortunately things don't work that way. Static controls aren't an option for me since I have no idea of knowing how many controls are required.

Geoff,
Took a look at that list - looks like it could have some good info. I'll check it out, and if I find an answer, I'll post it here. Thanks for the tip.

 

[krinid]

I'm 90% of the way there... I can make controls and event handlers on the fly. However, after the procedure which adds code to a UserForm ends, all forms unload, including the UserForm being edited, but even other irrelevant forms in the same project unload. (Forms in other projects don't unload, thankfully).

Check out the following code - any idea how to stop the forms from unloading?

    Dim frm As VBComponent
    Dim NewButton As MSForms.CommandButton
    Dim strFrmName As String, line As Long
    
    Set frm = ThisWorkbook.VBProject.VBComponents("FormName")

    'Add a CommandButton
    Dim strButtonName As String
    strButtonName = "MegaButton"
    Set NewButton = frm.Designer.Controls.Add("forms.CommandButton.1", strButtonName, True)

    ' change button properties
    With NewButton
        .Caption = strButtonName & " - Click Me"
        .left = 60
        .top = 40
    End With

    'Add an event-hander sub for the CommandButton
    With frm.CodeModule
        line = .CountOfLines
        .InsertLines line + 1, "Sub " & NewButton.name & "_Click()" 'CommandButton1_Click()"
        .InsertLines line + 2, "MsgBox ""You clicked " & strButtonName & "."""
        .InsertLines line + 3, "End Sub"
    End With

    'Show the form
    VBA.UserForms.Add(frm.name).Show

Starting from the first .InsertLines line of code, code can no longer be run in break mode. After the VBA.UserForms.Add(frm.name).Show line, the form is actually visible - it just unloads when the sub ends.

Executing the line from the immediate window after this sub runs causes the form to load as usual, and the button and it's event work as expected. However, this doesn't help if the form can't be used while the actual code is running.

Now here's an interesting facet - adding the following code

    Do
        DoEvents
    Loop

to the end of the sub that changes the code causes all forms to be visible, usable, and clicking on "MegaButton" indeed calls the event handler apropriately. But this isn't very efficient code is it? (infinite loops... I suppose I could make it a do while frm.visible = true [if only VBcomponents had a visible property... but anyhow, somehow make it rely on the form itself - but that's not very good b/c recursion will occur and as time goes by there will be several semi-infinite loops with DoEvents occurring]).

Any ideas?

 

[combo]

Hi,
what about the following way to handling an array of controls with one (nearly) procedure? It does not add any code, but dynamic set of buttons can work:

a class module named 'clsBtnClick':

Public WithEvents cmdB As MSForms.CommandButton

Private Sub cmdB_Click()
Dim i As Integer
sName = cmdB.Name
i = CInt(Val(Mid(sName, 5, 1)))
Call FormName.btnProc(i)
End Sub

an empty userform named 'FormName', with code:

Dim cmdBArray() As New clsBtnClick

Private Sub UserForm_Initialize()
For i = 1 To 5
ReDim Preserve cmdBArray(i)
Set cmdBArray(i).cmdB = Me.Controls.Add("Forms.CommandButton.1", "cmd_" & i)
    With cmdBArray(i).cmdB
        .Caption = "Button added " & i
        .Width = 80
        .Left = 10
        .Height = 18
        .Top = -10 + i * 20
    End With
Next i
End Sub

Public Sub btnProc(i As Integer)
MsgBox "Button clicked: " & i
End Sub

combo

 

[krinid]

combo,
You rule! I've been fighting this problem for a couple weeks straight now. I've implemented your solution and it's up and running flawlessly. Thanks!

 

[krinid]

Btw, there have been several inquiries regarding control arrays - I bet this would help a lot of people as an FAQ! There is an FAQ on faq707-4085 "Create Control Arrays in VBA UserForms" but it only deals with creating and referencing them, and falls short of event handlers.

 

[krinid]

Combo,
Can this be used for forms as well? I tried to adapt it to forms but couldn't find a matching configuration for the form variable declaration (ie: Public WithEvents frm as MSForms.UserForm) and the actual assignment of the dynamically created form. I use set frm = ThisWorkbook.VBProject.VBComponents.Add(3) to create a form, which generates an error b/c the form's type is vbext_ct_MSForm which appears to incompatible with MSForms.UserForm.

I don't really care what specific type of form is created so long as I can declare it withevents. I've tried using MSforms.UserForm, and plain old UserForm as well - both generate the same error. Only declaring it as VBComponent seems to get me past the error, but VBComponent can't be declared using withevents.

Here's the code I'm using (adaptation of your code).

(this is a class module)

Public WithEvents frm As MSForms.UserForm
Public collControls As Collection

Private Sub Class_Initialize()
    Set frm = ThisWorkbook.VBProject.VBComponents.Add(3)  'vbext_ct_MSForm
    With frm
        .Properties("Caption") = "Default form"
        .Properties("Width") = 200
        .Properties("Height") = 100
        .Properties("ShowModal") = False
    End With
    VBA.UserForms.Add(frm.name).Show
End Sub

 

[combo]

Hi,
the UserForms collection is dynamic collection of loaded userforms and it has to be different from userforms existing within a VBA project. I wonder if it is possible to mix them.
Instead, I would use an empty form as a template (UserForm1 below) existing in the project.

Having in a 'clsF' class module:

Public WithEvents frm As MSForms.UserForm

Private Sub frm_Click()
frm.Caption = Format(Now, "dd/mm/yyyy hh:mm:ss")
End Sub

and a standard module with:

Dim f As New clsF
Dim oFrm As UserForm1

Sub DisplayForm()
Set oFrm = New UserForm1
Set f.frm = oFrm
With oFrm
    .Caption = "Default form"
    .Width = 200
    .Height = 100
    .Show vbModeless
End With
End Sub

I create a new instance of a userform, modify its properties and assign form events defined in a class module (the problem I have is that the text from event appears under the form's caption).
I have not tried to add controls and their events from a separate class to such form.

combo

 

[krinid]

combo,
Seems to work fine! You've done it again! Thanks.

However, one thing that seems to perplex me is that from within the class, the .width, .height properties and .show method of frm do not exist. Outside the class, however, these properties/methods can be accessed no problem. That is to say, a variable of type MSforms.UserForm inside a class seems to lack .width, .height, .show! I was considering automatically creating the form inside the _initialize event, but I guess that's out the window!

Nevertheless, I can now accomplish my task. And with an added bonus, too - since this is based on an existing form, I can embed code in it (like the stuff that shows the minimize/maximum buttons on a form).

 

[krinid]

Correction ... what I just wrote isn't true. I just realized that in my code the variable outside the class wasn't defined as MSforms.UserForm but rather as Userform1!

Inside a class, one can't declare a variable based on a static type [gives an automation error] - that explains it. So it looks like I'll have to settle for the 99.9% solution (the 0.1% being actually creating a default form inside the class).

thanks for your help combo!