Adding Macro to TextBox in VBA

[jackass209]

I've got a userform which adds Textboxes automatically depending on a certain value. And the amount of Textboxes varies from 1 - 100. I add them with the line:

Set MyControl = UserForm1.Frame1.Controls.Add("forms.textbox.1", strControl, Visible)

Now my question is:
Can I add a macro to each of these textboxes automatically? A macro for the Change event I think it is.

All textboxes would have the same macro.

I kinda hoped it would be one of the following:
MyControl.OnAction = "test"
MyControl.Change = "test"

But you guessed it, that doesn't work!
Hope someone can help me out. Thanks!

 

[al_b_cnu]

Hi,

Perhaps you could use a class module, Heres a link to Andrew Poulsom's example: http://www.mrexcel.com/board2/viewtopic.php?t=68361

 

[jackass209]

Hi al_b_cnu,

Thanks for your reply. I don't see in that example how to add a macro to a textbox on a userform. That's all I need to know. Maybe I'm missing something but I don't see it.

Hope you or someone else can help me with this.

Thanks!

 

[al_b_cnu]

Hi,

Adapting Andrews code to suit your requirement follows. This code will catch the chenge event for all textboxes in the userform with a name starting 'Text':

'   Class module named CtrlClass

Public WithEvents CBGroup As MSForms.TextBox

Private Sub CBGroup_Change()
    MsgBox "TextBox " & CBGroup.Name & " changed"
End Sub

'    UserForm module

Dim CBs() As New CtrlClass

Private Sub UserForm_Initialize()
    Dim CBCount As Integer
    Dim Ctrl As Control
    CBCount = 0
    For Each Ctrl In UserForm1.Controls
        If TypeName(Ctrl) = "TextBox" Then
            If Left$(Ctrl.Name, 4) = "Text" Then
                CBCount = CBCount + 1
                ReDim Preserve CBs(1 To CBCount)
                Set CBs(CBCount).CBGroup = Ctrl
            End If
        End If
    Next Ctrl
End Sub

 

[jackass209]

Oh okay... I see what you mean. I'll try to see if I can get this to work. I'll keep you posted! Thanks alot!

 

[jackass209]

I get an error and it selects the following in blue:

ReDim Preserve CBs(1 To

I copied the codes you provided and put them in the userform.
Changed the Userform name.
It says something like "Variable not defined"

So I take it it's about the CBs but I'm not sure bout that.
Hope you can help me out... again.
Thanks!

 

[al_b_cnu]

Hi,

Did you declare the CBs variable at the top of the userform code,i.e. OUTSIDE any subs?

 

[jackass209]

This is the code I have:

' Class module named CtrlClass

Public WithEvents CBGroup As MSForms.TextBox

Private Sub CBGroup_Change()
MsgBox "TextBox " & CBGroup.Name & " changed"
End Sub

' UserForm module

Dim CBs() As New CtrlClass

Private Sub UserForm_Initialize()
Dim CBCount As Integer
Dim Ctrl As Control
CBCount = 0
For Each Ctrl In RegRap.Frame1.Controls
If TypeName(Ctrl) = "TextBox" Then
If Left$(Ctrl.Name, 4) = "Text" Then
CBCount = CBCount + 1
ReDim Preserve CBs(1 To CBCount)
Set CBs(CBCount).CBGroup = Ctrl
End If
End If
Next Ctrl
End Sub

So only changed the UserForm1 into RegRap.Frame1.
Unless I gotta change some more?

 

[Norie]

And is all this in the userform module?

I think what Alan means here is that the code should be going into a class module called CtrlClass.

'  Class module named CtrlClass 

Public WithEvents CBGroup As MSForms.TextBox 

Private Sub CBGroup_Change() 
MsgBox "TextBox " & CBGroup.Name & " changed" 
End Sub

And this in the userform module.

' UserForm module 

Dim CBs() As New CtrlClass 

Private Sub UserForm_Initialize() 
Dim CBCount As Integer 
Dim Ctrl As Control 
CBCount = 0 
For Each Ctrl In RegRap.Frame1.Controls 
If TypeName(Ctrl) = "TextBox" Then 
If Left$(Ctrl.Name, 4) = "Text" Then 
CBCount = CBCount + 1 
ReDim Preserve CBs(1 To CBCount) 
Set CBs(CBCount).CBGroup = Ctrl 
End If 
End If 
Next Ctrl 
End Sub

 

[al_b_cnu]

Hi,

the code

Private Sub CBGroup_Change() 
MsgBox "TextBox " & CBGroup.Name & " changed" 
End Sub

MUST be in a class module named 'CtrlClass'.

To do this in VBE from the menu Insert > Class Module. This will create a class module names 'Class1'.

Then change the name in the properties window to CtrlClass, (if the Properties window is not visible,press [F4])

Then paste the above code into it.

Remove the Sub 'CBGroup_Change'code from the userform code window.

[Edit] Thanks Norie,you beat me to it again,and so much more eloquently too :¬/

[Edit 2] ...and correct, unlike my submission too I missed the 'Public Witevents' statement :¬(