Events for controls created on the fly on a userform

[Guest]

I want to create a number of TextBox controls on a userform when a cell is
double clicked. I can do this with the Contols.Add method, but I want to be
able to code some of the TextBox events (as you can with a control array in
VB6). Any ideas?

 

[Wiggler]

I want to create a number of TextBox controls on a userform when a cell is
double clicked. I can do this with the Contols.Add method, but I want to
be
able to code some of the TextBox events (as you can with a control array
in
VB6). Any ideas?

You didnt say if you were using VBA but that is assumed..
If you double click the textbox it will add an _chg sub in the userform
code.
Just view code and and tell it what you want it to do from there.

 

[Bob Phillips]

Here is an example of adding code on the fly

Dim StartLine As Long

With ActiveWorkbook.VBProject.VBComponents("Userform1").CodeModule
StartLine = .CreateEventProc("Change", "Textbox1") + 1
.InsertLines StartLine, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbOYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

Which will add this code

Private Sub Textbox1_Change()
Dim ans
ans = MsgBox("All OK", vbOYesNo)
If ans = vbNo Then Cancel = True

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I guess I am missing a trick here. In frmAdj Form_Activate, I have:

Set Ct = Me.Controls.Add("Forms.TextBox.1", "txtNew" & CStr(N), True)
With ActiveWorkbook.VBProject.VBComponents("frmAdj").CodeModule
StartLine = .CreateEventProc("Change", Ct.Name) + 1

At this point it goes bang with "Error 57017. Event handler is invalid"
I can't see why it is upset - can you help?

Also in VB6 I would put this in Form_Load, but Excel dosn't have a Load and
the initialize does not seen to work the same way.