Events for a dynamic array of controls on a userform

[maxxia]

I'm trying to create an array of controls on a userform which are created at runtime, which I can then reference using an index number to define events.

My problem is that I'm declaring a dynamic array of controls using:

Dim textbox() As Object

and then in the Initialization event for the userform, I have the line:

Set textbox(c) = userform.Controls.Add("Forms.Textbox.1&quot

to add new textbox controls.


But now, once I have the array of textbox controls, I can't define an event for each of the controls in the array. I.e. the syntax:

Private Sub textbox(c)_Change()
gettext = textbox(c).Text
End Sub

doesn't work because VBA doesn't recognize textbox(c)_Change() syntax.

It has been suggested that I use:
Private Sub textbox_Change(Index as Integer)
gettext = textbox(c).Text
End Sub

but this doesn't work either because VBA doesn't recognize textbox_Change(Index as Integer) as referring to the entire array of textbox controls and the event never fires, at least not the way I've declared the array.

I have to add controls to the array at runtime and not at designtime because the number of textbox controls in the array depends on an integer variable.

Any advice would be much appreciated.

 

[DanJR]

Unlike VB, VBA doesn't support control arrays - but you could simply use VBA to add code to the Change Event for each textbox control that is created. e.g.
[tt]
....
Dim txt As TextBox
Set txt = CreateControl(FormName, acTextBox, acDetail, , , 0, 0, 1000, 100)
txt.name = "txtArray" & lngCounter
txt.OnChange = "=TextboxChange(lngCounter)"

And of course, the private function:
Private Function TextboxChange(Index and long)
gettext = "txtArray" & index
end Function
[/tt]

or something similar
Cheers,
Dan

 

[maxxia]

Thanks for the input...

My problem is that the txt.OnChange property isn't recognized that way. The only way I know of to define an event for a given control is
Sub ControlName_Change()
'code
End Sub

I have created a dynamic array of controls by creating an array of objects and then filling it with controls in a For loop. I am able to reference each control and reference properties, e.g.:

text = textboxcontrol(i).Text

but I can't use this syntax to references the events for each control. I need to use this dynamic array because the number of controls on my form is variable and they need to be created at runtime.

 

[DanJR]

I understand your question, but perhaps we are on different paths (it works for me)....

Assume that you already have a form named "frmTest" which already as the code:
[tt]
Private Function TextboxChange(Index As Long)
MsgBox "txtArray" & Index
End Function
[/tt]

Then the following code adds 10 textboxes and sets the OnChange event for each textbox:
[tt]
Public Function CreateTextboxes(FormName As String)
Dim txt As TextBox
Dim i As Long

Const TEXTBOX_WIDTH As Long = 2000
Const TEXTBOX_HEIGHT As Long = 250
Const TEXTBOX_GAP As Long = 100

DoCmd.OpenForm FormName, acDesign, , , , acHidden

For i = 1 To 10
Set txt = CreateControl(FormName, acTextBox, acDetail, , , 0, (TEXTBOX_HEIGHT + TEXTBOX_GAP) * (i - 1), TEXTBOX_WIDTH, TEXTBOX_HEIGHT)
txt.Name = "txtArray" & i
txt.OnChange = "=TextboxChange(" & i & &quot"
Next i

DoCmd.OpenForm FormName, acNormal, , , , acWindowNormal

End Function
[/tt]


cheers,
dan

 

[maxxia]

Sorry, I think the problem is that I'm using VBA and I'm coding a macro in Excel. I tried the code you suggested, but the compiler doesn't recognize the property "txt.OnChange"; otherwise, it would be a very good solution to my problem.

I have to say, I'm a little surprised at this . . . I would have expected this language to be a little more straightforward with something as basic as defining events for an array of controls. I've looked through a number of forums and it sounds like this is a ubiquitous problem for userforms in VBA.

Thanks a lot for the help though, Dan. Makes me think I should just dump Excel and start from scratch in VB.

Max

 

[DanJR]

yep, I can understand your frustration... Sorry i can't help as i don't know anything about UserForms. However, there is a "VBA Visual Basic for Applications (Microsoft)" forum within Tek-Tips which may be more helpful. Have you tried MSDN at microsoft.com?

good luck,
cheers,
dan

 

[krinid]

maxxia,
Had the same problem and combo solved it for me - check it out if you're still struggling with it:
thread707-572718