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?
--
John Austin

"John Austin" wrote in message
...
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?
--
John Austin
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.

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)


"John Austin" wrote in message
...
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?
--
John Austin

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").Co deModule
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.

"Bob Phillips" wrote:

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)


"John Austin" wrote in message
...
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?
--
John Austin