Solved

VBA: Capturing events from dynamically created textboxes in a form

Posted on 2004-04-21
11
1,564 Views
Last Modified: 2013-11-26

Hello,

This question is both urgent and difficult (for me, hopefully not for you).

I am working in VBA (under ESRI ArcMap).  I am making an editor that looks up information in a database and dynamically creates a set of text fields which allow the data to be edited.  I found the following code on the web, but had to correct it to get it working at all, and am still not getting it to work.  I do not have a lot of VBA experience and may get stuck pretty quickly if you answer the initial question, so I won't be offended if you presume I don't know the next steps.

For instance, I don't know if I need a whole bunch of Click functions, or if there is some way I can deal with them all in one function and still know which one I'm working with.

Bottom line is that I want to be able to write the same sorts of event functions as I would use for a control on a static form.  For text boxes probably Click or Changed events.  Eventually I'd also like to do ComboBoxes this way, but for now I'll settle for textboxes.

Here's the example, with some changes that I needed to get it working:

--------------------------------------------------------------------------------
Dim gettext As String                       <------ What is this for?  Does it belong here?
Private Sub UserForm_Initialize()
  Dim c() As DynamicTextboxControl
  ReDim Preserve c(1)
  Set c(1) = New DynamicTextboxControl
  Set c(1).Textbox=UserForm.Controls.Add("Forms.Textbox.1")
End Sub
--------------------------------------------------------------------------------

In a class module (DynamicTextboxControl):

VB:
--------------------------------------------------------------------------------
Private WithEvents txtBox As msforms.textbox

Public Property Set TextBox(tb As msforms.textbox)   <---- I changed Let to Set.
  Set txtBox=tb
End Property

Private Sub txtBox_Click()
  Dim getText as String           <----- I added this
  gettext=txtBox.text
  MsgBox "txtBox_Click was called with text = " & txtBox.Text    <------ I added this to see if it would come up.
End Sub

Right now, the example makes a textbox in the dialog, but nothing happens when I type in it and tab to another control.
I tried moving txtBox_Click from the class module to the form's code, but it says "Variable not defined" for txtBox when I compile.
0
Comment
Question by:jkuiper
  • 5
  • 5
11 Comments
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 10885178
Not sure about the capabilities of VBA in this area but here's an example (in VB) of containing the object's events within a class module and then raising the events of the object to the class's events so the form can receive them. Your method of containing them within the class module itself will allow for the added controls to be arrayed I believe but the form can't receive their events.

Form1:
-----------------

Private WithEvents MyText1 As Class1
Private WithEvents MyText2 As Class1
Private Sub Form_Load()
    Set MyText1 = New Class1
    Set MyText2 = New Class1
    Set MyText1.MyTextBox = Controls.Add("VB.TextBox", "MyText1")
    Set MyText2.MyTextBox = Controls.Add("VB.TextBox", "MyText2")
    With MyText1.MyTextBox
        .Visible = True
        .Left = 0
        .Top = 0
        .Text = "MyText1"
    End With
    With MyText2.MyTextBox
        .Visible = True
        .Left = 0
        .Top = 500
        .Text = "MyText2"
    End With
End Sub
Private Sub MyText1_Change()
    Call MsgBox("MyText1 Changed!")
End Sub
Private Sub MyText2_Click()
    Call MsgBox("MyText2 Clicked!")
End Sub


Class1:
---------------

Public WithEvents MyTextBox As TextBox

Public Event Change()
Public Event Click()
Private Sub MyTextBox_Change()
    RaiseEvent Change
End Sub
Private Sub MyTextBox_Click()
    RaiseEvent Click
End Sub

.............

If you want to contain the events within the class itself, here's an alternate example..


Form1:
--------------

Private MyTextBoxes(1 To 5) As Class1
Private Sub Form_Load()
    Dim iLoop As Integer
    For iLoop = 1 To 5
        Set MyTextBoxes(iLoop) = New Class1
        With MyTextBoxes(iLoop)
            Set .MyTextBox = Controls.Add("VB.TextBox", "MyTextBox" & iLoop)
            .MyTextBox.Text = "MyTextBox" & iLoop
            .MyTextBox.Top = iLoop * 400
            .MyTextBox.Visible = True
        End With
    Next iLoop
End Sub


Class1:
-------------

Public WithEvents MyTextBox As TextBox
Private Sub MyTextBox_Change()
    Call MsgBox(MyTextBox.Name & " Changed!")
End Sub
Private Sub MyTextBox_Click()
    Call MsgBox(MyTextBox.Name & " Clicked!")
End Sub


I can offer explanations so feel free to ask.
0
 
LVL 1

Expert Comment

by:mimil
ID: 10887112
I am not too sure it is relevant either ...
In VB you can add controls to an existing control array.

On a form, you can add a text box (named Text1) and change the index property from blank to 0 (This creates a control array, unfortunatly I believe it cannot be done in runtime)

You can then add as many text box as you wish in runtime and capture all events in a single function:

Private Sub Form_Load()
    Load Text1(1)
    Text1(1).Visible = True
    Text1(1).Move 1200, 2000
End Sub

Private Sub Text1_Change(Index As Integer)
    MsgBox "Text field " & Index & " has changed"
End Sub
0
 

Author Comment

by:jkuiper
ID: 10889393

Thanks for your rapid and helpful responses!

So far I've tried zzzzzooc's example by and it's working in VBA.  I need to get it working within my application with real data and an array of controls.  Please give me up to a week to keep this question open in case I hit a roadblock.  I have a bunch of stuff to code and have to present the software next Tuesday.  I may be forced to do this part after that meeting.

I'm not sure if it was the intent, but mimil's input about controls being moved/visible made me realize that I didn't necessarily have to create the controls at runtime.  I could make a bunch of textfields and move them into place and use them when needed.  It looks like the runtime approach is going to work, but it's good to know that as a workaround.

Related questions:
  1) Eventually I'd like to try making some of the controls comboboxes.  What's the "best" way to do that.  Could I make one control array and be able to tell whether a particular control was a textbox or a combobox?  Or would I make two control arrays, (one for each type of control) and keep track of them separately?
  2) It was already a workaround to make an array of controls.  What I had initially imagined was something like the properties area in VB where you have a scrolling list of properties and you change their values with a heterogeneous set of textboxes and combo boxes.  A standard listbox doesn't seem to be extenable to do those things.  Do you know of an interface for this type of approach that I can use in VBA?
  3) There is an Exit event for when control leaves a textbox.  But Exit is a keyword so "Public Event Exit()" and "RaiseEvent Exit" give compile errors.  Can you code a textbox Exit event with this approach?  I don't have to have it, but I'm curious how the unfortunate overlap in keywords works itself out.
0
 

Author Comment

by:jkuiper
ID: 10890974

Ok, I've integrated zzzzzoos's approach into my code.  Each time the text changes, it goes through the click event.  I also managed to get at the textbox properties by putting the following into the class:

Option Explicit

Public WithEvents DynTextBox As MSForms.TextBox

Public Event Change()

Private Sub DynTextBox_Change()
'    RaiseEvent Change                                                <----- This line was working, but how do I call a routine in
    RuntimeTextbox_Change                                                  the code for my form and know which textbox was changed?
End Sub                                                                              RuntimeTextbox_Change is a public sub on the form, but
                                                                                          I get a compile error that the sub is not defined.

Public Property Set TextBox(tb As MSForms.TextBox)    <----- I added this part.  Is that the right approach
  Set DynTextBox = tb                                                          so I can get at the textbox properties and methods?
End Property
0
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 10892680
>> I'm not sure if it was the intent, but mimil's input about controls being moved/visible made me realize that I didn't necessarily have to create the controls at runtime.

You didn't have to. Using control arrays created at design-time is a lot easier than catching events for dynamically created controls. WithEvents doesn't support arrays in their variables so you'd need to have a class-wrapper.

>> This line was working, but how do I call a routine in the code for my form and know which textbox was changed?

Your form has to have a variable WithEvents declared as the class module. RaiseEvent raises the event so the variable on the form can catch it. You can create a custom event to raise with a parameter that includes the name of the control.

Such as below...

Form1:
-------------

Private WithEvents MyText1 As Class1
Private WithEvents MyText2 As Class1
Private Sub Form_Load()
    Set MyText1 = New Class1
    Set MyText2 = New Class1
    Set MyText1.MyTextBox = Controls.Add("VB.TextBox", "MyText1")
    Set MyText2.MyTextBox = Controls.Add("VB.TextBox", "MyText2")
    With MyText1.MyTextBox
        .Visible = True
        .Left = 0
        .Top = 0
        .Text = "MyText1"
    End With
    With MyText2.MyTextBox
        .Visible = True
        .Left = 0
        .Top = 500
        .Text = "MyText2"
    End With
End Sub
Private Sub MyText1_Change(ByVal sName As String)
    Call MsgBox(sName & " changed!")
End Sub
Private Sub MyText2_Click(ByVal sName As String)
    Call MsgBox(sName & " clicked!")
End Sub

Class1:
------------------

Public WithEvents MyTextBox As TextBox

Public Event Change(ByVal sName As String)
Public Event Click(ByVal sName As String)
Private Sub MyTextBox_Change()
    RaiseEvent Change(MyTextBox.Name)
End Sub
Private Sub MyTextBox_Click()
    RaiseEvent Click(MyTextBox.Name)
End Sub


I mentioned previously though about having a class-wrapper to handle arrays of added controls. There's an example below.. scroll to "ObjArrays.zip":

http://www.mvps.org/vb/index2.html?samples.htm

It's a lot more complicated this way and the above sample requires a lot of modifying to get to work for your scenario. I suggest using mimil's method if you want something simple.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:jkuiper
ID: 10893275

Have you considered a career in programming?  ;)

At this point there seem to be 4 choices:
  1) The class wrapper approach which is probably more difficult than I can manage at this point.
  2) Example just above, with 50-60 declaration lines and routines for all the textboxes potentially needed.
      I think I can do this one, but it's not very elegant.
  3) mimil's approach, which seems to imply I can make more textboxes dynamically and catch the events in
      one routine.  How is not explained.  How do I create additional ones, and how do I distinguish which one
      is which when they are used?  Sorry, but with my skill level a cookbook answer might be best.
  4) Possibly there is a custom control not yet identified here that will provide a scrolling listbox-like interface,
      but with editable fields and the ability to make some of them combo-boxes.  See #2 in my reply above.

So what's the best choice?  Right now it's #2 since it's the only one I think I can code.

-- Jim


0
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 10893571
>> Have you considered a career in programming?

Young with no certifications = bad. ;-)

>> So what's the best choice?

For #2, all you do is add a TextBox (Text1) to a form at design-time and set it's index to 0. You can then load additional instances of Text1 as mentioned ( Load Text1(#) .. where # is the number of the array to load.. usually Text1.Ubound). All events for the instances of Text1 will have a regular event on the form with one exception, a return parameter of Index which identifies which one it is. I'd go for that option.

If you want actual dynamic controls, I can probably manage a class-wrapper example. It's complicated compared to the above method, but not difficult.
0
 

Author Comment

by:jkuiper
ID: 10893710

>> Young with no certifications = bad. ;-)

Young with your abilities = bad for my job security.  We're not talking under 15 are we?!

I'll go with option 2.

I'm going to have to wait with futher coding on this dialog box for a while until I get some other parts of the system working that are more noticeable to the audience I'll be speaking to Tuesday.

Would you like me to close out this thread, or is it OK if I leave it open (with potentially more questions about implementing #2) until I get the code working?  I'll do either and don't want to leave you hanging after you've helped me so much.  I can always post a new question if I get stuck.
0
 
LVL 17

Expert Comment

by:zzzzzooc
ID: 10893826
>> We're not talking under 15 are we?!

19. Started when I was about 11-12 though. :)

>> or is it OK if I leave it open

Leave it open until your issue is resolved. I'll see about the class-wrapper.
0
 
LVL 17

Accepted Solution

by:
zzzzzooc earned 500 total points
ID: 10894446
Ok.. *wipes sweat off brow*..

Form1:
--------------------------------------------------------------------

Option Explicit

Private WithEvents MyWrapper As clsWrapper
Private Sub Form_Load()
    Set MyWrapper = New clsWrapper
    Set MyWrapper.TextBoxContainer = Me
    Call MyWrapper.CreateTextBox(0, 0, 1000, 300)
    Call MyWrapper.CreateTextBox(0, 300, 1000, 300)
    Call MyWrapper.CreateTextBox(0, 600, 1000, 300)
End Sub
Private Sub MyWrapper_Change(ByVal iID As Integer)
    'ID is like an index.. it'll help identify between the controls
    '......
    'We'll just get the text of the TextBox for testing.. since the
    'text changed!
    '......
    'About the below.. The TextBox controls created are added to a class (clsTextBox)
    'and set to an object called "MyTextBox". The collection starts from 1 whereas
    'the counter starts from 0.. so iID+1 will return the current clsTextBox
    '...
    'MyWrapper.TextBoxCollection(iID + 1) <-- clsTextBox
    'MyWrapper.TextBoxCollection(iID + 1).MyTextBox <-- TextBox that was added
    'MyWrapper.TextBoxCollection(iID + 1).MyTextBox.Text <-- Text of the textbox
    '....
    'You can access all of the textbox's methods/properties using the below..
    'Another example of settign the text is when it's clicked below..
    Call MsgBox(MyWrapper.TextBoxCollection(iID + 1).MyTextBox.Text)
End Sub
Private Sub MyWrapper_Click(ByVal iID As Integer)
    'ID is like an index.. it'll help identify between the controls
    '......
    MyWrapper.TextBoxCollection(iID + 1).MyTextBox.Text = "This text is for " & iID
End Sub


clsTextBox:
--------------------------------------------------------------------
Option Explicit

Public Event Change(ByVal iID As Integer)
Public Event Click(ByVal iID As Integer)

Public TextBoxWrapper As clsWrapper

Public WithEvents MyTextBox As TextBox
Public Sub MyTextBox_Change()
    Call TextBoxWrapper.Change(MyTextBox.Tag)
End Sub
Private Sub MyTextBox_Click()
    Call TextBoxWrapper.Click(MyTextBox.Tag)
End Sub



clsWrapper:
------------------------------

Option Explicit

Private iCount As Integer

Public TextBoxContainer As Form
Public TextBoxCollection As New Collection

Public Event Change(ByVal iID As Integer)
Public Event Click(ByVal iID As Integer)
Public Sub CreateTextBox(ByVal lLeft As Long, lTop As Long, ByVal lWidth As Long, ByVal lHeight As Long)
    Dim MyTextBoxCls As New clsTextBox
    Set MyTextBoxCls.TextBoxWrapper = Me
    Set MyTextBoxCls.MyTextBox = TextBoxContainer.Controls.Add("VB.TextBox", "MyTextBox" & iCount)
    With MyTextBoxCls.MyTextBox
        .Left = lLeft
        .Top = lTop
        .Width = lWidth
        .Height = lHeight
        .Visible = True
        .Tag = iCount
    End With
    Call TextBoxCollection.Add(MyTextBoxCls)
    iCount = iCount + 1
End Sub
Public Sub Change(iID As Integer)
    RaiseEvent Change(iID)
End Sub
Public Sub Click(iID As Integer)
    RaiseEvent Click(iID)
End Sub



"cls" means it's a Class Module. The above can easily add TextBoxes at run-time and catch all of their events in one routine. A parameter returned is the index property (tag) which identifies which control is which. I tried to use Implements to implement the interface of clsTextBox but this is fine also. Now.. removing the objects is a whole different story.. :-)
0
 

Author Comment

by:jkuiper
ID: 11006073

Christopher (zzzzzooc),

Ok, I finally got back to this.  That last example has all the elements I needed and I'll accept that solution with thanks.

I had to change a few details to get it to work in VBA (within ESRI ArcMap) for me, so below is my working version of your example with the changes I made.  I added ComboBoxes (class examples are very similar to the TextBox ones).  Note that TextBoxes don't have click events, at least not in VBA, so I took that out.

A last problem is catching an Exit or AfterUpdate event.  I tried adding an Exit event but it has an extra boolean parameter and I didn't know how to accomodate it.  So I'm trying an AfterUpdate event, but it doesn't get called.  Do you see any errors in this code that would explain why the Change events work fine but the AfterUpdate ones do not?

By the way, we could use another programmer around here.  Are you looking for a summer job?

-- Jim

UserForm1:
------------------------------------------------------------

Option Explicit

Private WithEvents MyWrapper As clsWrapper
Private WithEvents MyCbWrapper As clsCbWrapper

Dim mbInit As Boolean

Private Sub UserForm_Initialize()

    mbInit = True
   
    Set MyWrapper = New clsWrapper
    Set MyWrapper.TextBoxContainer = Me
    MyWrapper.CreateTextBox 6, 6, 200, 16
    MyWrapper.CreateTextBox 6, 26, 200, 16
    MyWrapper.CreateTextBox 6, 46, 200, 16

    Set MyCbWrapper = New clsCbWrapper
    Set MyCbWrapper.ComboBoxContainer = Me
    MyCbWrapper.CreateComboBox 6, 66, 200, 16
    MyCbWrapper.ComboBoxCollection(1).MyComboBox.AddItem ("Choice 1")
    MyCbWrapper.ComboBoxCollection(1).MyComboBox.AddItem ("Choice 2")
    MyCbWrapper.ComboBoxCollection(1).MyComboBox.AddItem ("Choice 3")
    MyCbWrapper.ComboBoxCollection(1).MyComboBox.ListIndex = 0
    MyCbWrapper.CreateComboBox 6, 86, 200, 16
    MyCbWrapper.ComboBoxCollection(2).MyComboBox.AddItem ("Choice 1")
    MyCbWrapper.ComboBoxCollection(2).MyComboBox.AddItem ("Choice 2")
    MyCbWrapper.ComboBoxCollection(2).MyComboBox.AddItem ("Choice 3")
    MyCbWrapper.ComboBoxCollection(2).MyComboBox.ListIndex = 0
    MyCbWrapper.CreateComboBox 6, 106, 200, 16
    MyCbWrapper.ComboBoxCollection(3).MyComboBox.AddItem ("Choice 1")
    MyCbWrapper.ComboBoxCollection(3).MyComboBox.AddItem ("Choice 2")
    MyCbWrapper.ComboBoxCollection(3).MyComboBox.AddItem ("Choice 3")
    MyCbWrapper.ComboBoxCollection(3).MyComboBox.ListIndex = 0

    mbInit = False
End Sub
Private Sub MyCbWrapper_Change(ByVal iID As Integer)
    If (Not mbInit) Then
      MsgBox MyCbWrapper.ComboBoxCollection(iID + 1).MyComboBox.Text, _
        vbInformation, MyCbWrapper.ComboBoxContainer.ActiveControl.Name
    End If
End Sub
Private Sub MyWrapper_Change(ByVal iID As Integer)
    'ID is like an index.. it'll help identify between the controls
    '......
    'We'll just get the text of the TextBox for testing.. since the
    'text changed!
    '......
    'About the below.. The TextBox controls created are added to a class
    '(clsTextBox) and set to an object called "MyTextBox". The collection
    'starts from 1 whereas the counter starts from 0.. so iID+1 will
    'return the current clsTextBox
    '...
    'MyWrapper.TextBoxCollection(iID + 1) <-- clsTextBox
    'MyWrapper.TextBoxCollection(iID + 1).MyTextBox <-- TextBox that was added
    'MyWrapper.TextBoxCollection(iID + 1).MyTextBox.Text <-- Text of the textbox
    '....
    'You can access all of the textbox's methods/properties using the below..
    MsgBox MyWrapper.TextBoxCollection(iID + 1).MyTextBox.Text, _
      vbInformation, MyWrapper.TextBoxContainer.ActiveControl.Name
End Sub

'This example is where changes can be validated as control leaves the textbox.
Private Sub MyWrapper_AfterUpdate(ByVal iID As Integer)
    'ID is an index to identify the control
    MyWrapper.TextBoxCollection(iID + 1).MyTextBox.Text = _
      "Text changed after update"
End Sub
Private Sub CommandButton1_Click()
    'ID is an index to identify the control
    MyWrapper.TextBoxCollection(1).MyTextBox.Text = _
      "Change text in box 1"
End Sub

Private Sub TextBox1_AfterUpdate()
  MsgBox "AfterUpdate event triggered in a static textbox."
End Sub


clsTextBox:
----------------------------------------------
Option Explicit

Public Event Change(ByVal iID As Integer)
Public Event AfterUpdate(ByVal iID As Integer)

Public TextBoxWrapper As clsWrapper

Public WithEvents MyTextBox As TextBox
Public Sub MyTextBox_Change()
    Call TextBoxWrapper.Change(MyTextBox.Tag)
End Sub
Public Sub MyTextBox_AfterUpdate()
    Call TextBoxWrapper.AfterUpdate(MyTextBox.Tag)
End Sub



clsWrapper:
---------------------------------------------------------
Option Explicit

Private iCount As Integer

Public TextBoxContainer As MSForms.UserForm
Public TextBoxCollection As New Collection

Public Event Change(ByVal iID As Integer)
Public Event AfterUpdate(ByVal iID As Integer)

Public Sub CreateTextBox(ByVal lLeft As Long, lTop As Long, ByVal lWidth As Long, ByVal lHeight As Long)
    Dim MyTextBoxCls As New clsTextBox
    Set MyTextBoxCls.TextBoxWrapper = Me
    Set MyTextBoxCls.MyTextBox = TextBoxContainer.Controls.Add("Forms.TextBox.1", _
      "MyTextBox" & iCount)
    With MyTextBoxCls.MyTextBox
        .Left = lLeft
        .Top = lTop
        .Width = lWidth
        .Height = lHeight
        .Visible = True
        .Tag = iCount
    End With
    Call TextBoxCollection.Add(MyTextBoxCls)
    iCount = iCount + 1
End Sub
Public Sub Change(iID As Integer)
    RaiseEvent Change(iID)
End Sub
Public Sub AfterUpdate(iID As Integer)
    RaiseEvent AfterUpdate(iID)
End Sub
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now