 |
 |
01-04-2004, 11:21 AM
|
|
Newcomer
|
|
Join Date: Jan 2004
Posts: 3
|
|
Events for a dynamic array of controls on a userform
|
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")
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.
Lastly, using the OnChange property of a control has also been suggested, but it seems like VBA in Excel doesn't recognize it (although it's a legitimate property in VB), e.g. the syntax textbox(c).OnChange = "changesub") isn't recognized.
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. |
|
01-04-2004, 11:54 AM
|
 |
Captain TJ
Forum Leader
* Expert *
|
|
Join Date: Jun 2003
Location: England
Posts: 1,664
|
|
|
How is your userform being updated or why do you need the _Change event for each textbox to fire?
TJ
|
|
01-04-2004, 02:30 PM
|
 |
Unashamed geek
Retired Moderator
* Expert *
|
|
Join Date: Jul 2003
Location: London, England
Posts: 8,988
|
|
|
As far as I know, control arrays like the ones in VB6 don't exist in VBA.
Could you not add the controls at design time and just hide the ones you don't need?
|
|
01-05-2004, 05:40 AM
|
 |
Green-Eyed
Super Moderator
* Guru *
|
|
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
|
|
|
01-05-2004, 06:57 AM
|
 |
Microsoft Excel MVP
Forum Leader
* Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
Ok that code by Colo is really very nice... but I'm struggling with the Control Array issue... on the surface it looks like Herilane is right, but I'm not 100% sure. (Just because I can't make one, doesn't mean that it can't be done! But I'm starting to think that it can't...)
(1) For Maxia: Just to confirm, you are operating within VBA behind Excel, not from VB 6.0, right?
(2) For Everyone Else: can we confirm or deny the existance of Control Array's on VBA UserForms?
(3) Maxia, using code similar to Colo's I once knocked-up a little example of controls being created at run time. They are not Text-Boxes, but they illustrate the issue of (a) Creating a Control (b) assigning a .Tag and (c) trapping the Controls Events. I'll attach it again here, hopefully it will help...
Most of the code is within the Form1 Form Module. The important relevant code is this:
Code:
For Each TheCell In rngButtonNames
Set CmdButton = UserForm1.Controls.Add("Forms.CommandButton.1")
CmdButton.Top = Top
CmdButton.Left = Left
CmdButton.Height = ...
' Etc
' Etc
' Etc
Set BtnEvent = New clsButtonEvents ' <-- ** Key **
Set BtnEvent.Button = CmdButton ' <-- ** Key **
Call ButtonEventCollection.Add(BtnEvent) ' <-- ** Key **
Next TheCell
Hopefully by reviewing this working model and Colo's even cleaner code it should start to make sense for you. Ask again if it's not clear!
-- Mike |
|
01-05-2004, 07:38 AM
|
|
Green-Eyed
Super Moderator
* Guru *
|
|
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
|
|
|
01-05-2004, 07:41 AM
|
|
Captain TJ
Forum Leader
* Expert *
|
|
Join Date: Jun 2003
Location: England
Posts: 1,664
|
|
|
Since the first question was about TextBox controls, I have put together the following sheet. It contains a form that has TextBox controls added to it and then the updates the values on to Sheet1. However, it does not use events, apart form the click event of the button.
TJ
|
|
01-05-2004, 07:51 AM
|
|
Microsoft Excel MVP
Forum Leader
* Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
Quote:
Originally Posted by Timbo
It's obvious today, but for future reference, answer to (2) is yes:
http://www.xtremevbtalk.com/t132745.html
Well, yeah, *effectively* you can do it, so this is good to keep in mind for future reference... but you can't actually make what is called a 'ControlArray' in a VBA UserForm, can you? Creating an Array of WithEvents variables in an Array (or Collection) is a good way to simulate it though...
-- Mike |
|
01-05-2004, 07:59 AM
|
|
Green-Eyed
Super Moderator
* Guru *
|
|
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
|
|
Exactly, thanks for re-phrasing that.  |
|
01-06-2004, 11:31 AM
|
|
Newcomer
|
|
Join Date: Jan 2004
Posts: 3
|
|
Quote:
Originally Posted by tinyjack
How is your userform being updated or why do you need the _Change event for each textbox to fire?
TJ
Sorry it's taken me so long to reply everyone. Thanks so much for all the input, I'm really impressed by the response!
I'm trying to make a dynamic userform which asks the user to enter the parameters for an equation while displaying and updating the equation as it's being entered. In other words, I have a userform with textboxes in which parameters are entered and at the top, a label with the equation as it's being changed.
The number of parameters depends on another variable and thus the number of textboxes the user needs to fill out changes right before runtime. Thus, the number of controls (as well as events) absolutely needs to be dynamic. |
|
01-06-2004, 04:11 PM
|
 |
Licensed to post
* Expert *
|
|
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
|
|
OK so to implement your first post what you want is something like the following:
in a module:
Code:
dim gettext as string
dim c() as clsTextboxes
redim preserve c(1)
set c(1).Textbox=userform.Controls.Add("Forms.Textbox.1")
'etc.
in a class module (clsTextboxes):
Code:
private withevents txtBox as msforms.textbox
public property let TextBox(tb as msforms.textbox)
set txtBox=tb
end property
private sub txtBox_Click()
gettext=txtBox.text
end sub
Hope that helps!
|
|
01-07-2004, 02:56 PM
|
|
Newcomer
|
|
Join Date: Jan 2004
Posts: 3
|
|
|
Many thanks to Timbo, Mike, and Mark for the all the help with the code examples! I really appreciate it.
I think I understand what to do with the Class definition to make this work. I've read through Mike's attached example and I'm not exactly sure I understand everything, but I still have to go through it more carefully.
I'm not a programmer by profession; I'm trying to implement VBA through Excel to facilitate analysis of some scientific data, so pardon my slowness in learning.
Thanks again all.
Max
|
|
01-07-2004, 03:01 PM
|
|
Microsoft Excel MVP
Forum Leader
* Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
Make no apologies... getting one's head around the use of WithEvents stored in an Array() or Collection is a rather advanced conceptual issue. (I find it hard anyway.)
This is a tough issue to hit early on in your VBA learning... but it looks like you're doing great. 
And you know where you can go if/when you get stuck...
,
Mike |
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
|
|
 |
|
