PDA

View Full Version : code created text box not firing change event



brusk
03-05-2013, 02:09 PM
I've created a User form with a set of 7 text boxes per row and 20-80 rows depending on previous user input. I want to create these on the form_initialize which isn't a problem but on testing I can't get the change event to work. I can manually create and name all these text boxes but I'd rather not go through all that trouble when I should be able to do it in the code when the form starts.

snb
03-06-2013, 02:11 AM
You'd better create a userform in design mode.
The VBEditor is designed to duplicate groups of elements very fast: ctrl mouse track.
The only thing to do in initialize is making 'rows' visible/invisible.

Aflatoon
03-06-2013, 05:50 AM
It is quite possible to hook up controls created at runtime using classes, although not all events are available (the Change event is). How are you trying to do it currently?

brusk
03-06-2013, 06:31 AM
For testing here's what I had that wasn't doing anything. I would create the textbox but not kick off the change event.

Private Sub UserForm_Initialize()
Set mytxt = Me.mpUIPNetwork.Pages(0).Controls.Add("forms.textbox.1")
With mytxt
.Name = "txtTest"
.Top = 140
.Left = 138
.Width = 36
.Height = 15.75
End With
End Sub
Private Sub txtTest_Change()
MsgBox "worked"
End Sub

Aflatoon
03-06-2013, 06:55 AM
That won't work - you need a class really. Do you want all the textboxes (or all the rows) to do the same thing?

brusk
03-06-2013, 07:45 AM
Thanks, I'll have to look into trying it that way. Each text box will be doing different validation. It's all server information such as hostname so that one can't have illegal characters, IP Addresses so I'll have to check for proper IP Address formatting etc.

brusk
03-06-2013, 12:35 PM
I've been playing with this a little bit as I found a similar example and can actually get it created AND a check event. The problem I'm seeing is in this example of what I have working it does create the textbox in Frame1 but if I change it to say Me.MultiPage1.Pages(0) or other variations Me, Me.Controls, Me.MultiPage1.Pages(0).Controls etc it doesn't work and I get debug errors of, er Excel just crashed on me again so not sure what it was but it didn't like the line where I call the newtextbox.

Here's the class I created
Option Explicit
'private variables
Private WithEvents TB As MSForms.TextBox
'constructor
Private Sub Class_Initialize()
MsgBox "Initialize"
End Sub
'initialize procedure (as can't have parameters in constructor)
Public Sub Init(parentFrame As MSForms.Frame, nameValue As String, leftValue As Double, topValue As Double, widthValue As Double, heightValue As Double)
Set TB = parentFrame.Add("Forms.TextBox.1")
TB.Name = nameValue
TB.Left = leftValue
TB.Top = topValue
TB.Width = widthValue
TB.Height = heightValue
End Sub
'events
Public Sub TB_Change()
MsgBox "Worked"
End Sub

And here's the port on a button on UserForm1 that has a MultiPage1 and a Frame1
Private Sub CommandButton1_Click()
Dim newTextBox As NewTB
Dim Loc As Control
Set Loc = Me.Frame1
Set newTextBox = New NewTB
Call newTextBox.Init(Me.Frame1, "TextBox3", 5, 10, 80, 30)
End Sub

brusk
03-06-2013, 01:06 PM
Nvm I just saw that in the Class the example had MSForms.Frame, I had to change it to MSForms.Control

mikerickson
03-06-2013, 07:39 PM
If there is only one text box, this can be done within the userform's code (no class needed)

Public WithEvents myTxt As MSForms.TextBox

Private Sub UserForm_Initialize()
Set myTxt = Me.mpUIPNetwork.Pages(0).Controls.Add("forms.textbox.1")
With myTxt
.Name = "txtTest"
.Top = 140
.Left = 138
.Width = 36
.Height = 15.75
End With
End Sub

Private Sub myTxt_Change()
MsgBox "worked"
End Sub
But if you need multiple text boxes, each running the same Change event, a class module would be the way to go.

Userforms are class modules with a user interface "attached" Property Get, Set and Let and WithEvents are all avaliable in a userform's code module. If you wanted to, one could make multiple instances of the userform, one visible form and many instances that aren't .Shown that hold the code for each text box.

' in Userform1's code module

Public WithEvents myTxt As MSForms.TextBox
Dim madeTextBoxes As New Collection

Private Sub UserForm_Activate()
Dim i As Long
Dim newTextBox As UserForm1

For i = 1 To 10
Set newTextBox = New UserForm1
Set newTextBox.myTxt = Me.Controls.Add("forms.textbox.1")
With newTextBox.myTxt
.Height = 20: .Width = 50
.Top = 5 + 25 * i: .Left = 20
End With
madeTextBoxes.Add newTextBox
Next i

Set newTextBox = Nothing
End Sub

Private Sub myTxt_Change()
MsgBox "worked"
End Sub

Private Sub UserForm_Terminate()
Dim oneBox As UserForm1
For Each oneBox In madeTextBoxes
Unload oneBox
Next oneBox
Set madeTextBoxes = Nothing
End Sub

However, you cannot use the initialize event to generate the subordinate userforms, otherwise you cascade into infinite looping. The Activate event should be used instead.

snb
03-07-2013, 04:30 AM
I made a revision of this post.

brusk
03-08-2013, 04:20 PM
I played with a class and now have the change events working as I'd like. The problem and it seems like a normal one is by running it as a class you lose the exit event functionality.

SamT
03-10-2013, 01:35 PM
In VB5 Sub Class_Terminate() and VB6 Sub Finalize() are triggered ~when all references to class are dead

HTH

Aflatoon
03-11-2013, 02:00 AM
It is true that you lose the Exit (plus Enter, BeforeUpdate and AfterUpdate) event because they actually belong to the container and only get hooked up at design time. There are some possible workarounds but they are very convoluted and not 100% reliable.