Dynamic Controls

[dfenton21]

I've been using VBA for about two years, but I've just started using dynamic controls today.

I know how to add a dynamic control to a form at a position relative to an existing contol, i.e.,
<code>
Private Sub CommandButton1_Click()

Set TextBox1 = UserForm1.Controls.Add("Forms.TextBox.1", "Test", True)
With TextBox1
.Text = "Test TextBox"
.FontSize = 8
.Top = cmdTest.top + </code><code>cmdTest.height + 30</code>
<code> .Width = 150
.Left = 12
.Height = 18

End With

End Sub

</code>I need to know:
(1) How can I use the same command button to add multiple textboxes to the form, with their positions relative to the last control added i.e. the form loads with only a command button. The user clicks that button and a textbox is added. The user clicks the same button and another textbox is added below the first one.

(2) How can I reference the controls that were dynamically added, i.e. I need a routine to get the sum of the values in each dynamically added textbox. There could be one textbox or 10, depending on how many times the user has added a textbox. For controls added at design time, I'd just have, for example

<code>iTotal = textbox1.text + textbox2.text + textbox3.text....</code>

I don't know how to do this with dynamic control where the number of controls added can vary.

I hope this is clear, and thanks in advance for your help

 

[shg]

Instread of adding controls at runtime, why not create them all at design time and just make them visible as needed?

 

[dfenton21]

Thanks for your reply.

I could do that (and have with previous projects). However, if what I describe in my OP can be done, I'd rather do that because I don't want the user to be limited to the amount of controls I add at design time.

 

[shg]

Then at some point, you'll need to make the form larger, will you not?

You could put each added control in a collection declared at the module level, with the control name as the Key and its Top property as the Item. The first entry in the collection would be the commman button and its top position.

Then you can loop from collection(2) through collection(collection.count) to sum the values.

 

[dfenton21]

Thanks for that.

Regarding the size of the form. The controls will be in a frame (with no caption). The frame will be big enough to hold the usual number of control I would expect the user to add. If an unusual number of control are added, the user can scroll the frame.

 

[Norie]

You can loop through all the controls on the form using the Controls collection.

In your case you could check for textboxes using TypeName or TypeOf, and perhaps name if they will have some sort of simple naming convention.

One thing though having the textboxes in a frame is that a frame is a control and it has it's own Controls collection.

I'm not sure if that will help or hinder but personally I try to avoid using frames if I possibly can.

Mind you, have you considered not using multiple, dynamic textboxes?

For example you could use a listbox that the user can add the values to, and also delete/amend existing items.

The adding could be done using 1 textbox and a command button.

Another couple of buttons could be added for editing/deleting and a final button to do the sum, or you could keep a running total somewhere, eg a label.

 

[dfenton21]

Norie,

Thanks for your reply. I've used scrollable frames for this purpose before and it worked fine. As I said, the user only needs to scroll if the number of controls is unusually high.

I got the idea from our company's time and attendence system, which was developed using the Delphi programming language. That programme also uses dynamic controls in a scrollable frame (or whatever the Delphi equivalent of a frame is (possibly a Panel)).

 

[Norie]

I think a frame would be fine for this, they can act a bit funny sometimes.

That seems to happen mostly when you are trying to work with the events of the controls in the form, eg certain events don't fire as they 'should' do.