I need to create event procedures for controls added with Controls.Add.
The following approach was suggested:

Private Sub UserForm_Activate()
Dim Ct As Control, StartLine as long

Set Ct = Me.Controls.Add("Forms.TextBox.1", "txtNew" , True)
With ActiveWorkbook.VBProject.VBComponents("UserForm1") .CodeModule
StartLine = .CreateEventProc("Change", Ct.Name) + 1
.... add the text for the procedure..

But I get a runtime error on the .CreateEventProc call - Error 57017 "Event
handler is invalid"

Where have I gone wrong? Are there any other approaches (such as control
arrays in VB6)

Another complication is that when the workbook is protected, the line:
With ActiveWorkbook.VBProject.VBComponents("UserForm1") .CodeModule
causes a run-time error. I really need to trap the TextBox change events
with the VBA code protected.

Many thanks,
--
John Austin

--
John Austin

Hi

I can reproduce the problem, and now I am researching the issue, and I will
update you ASAP.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi

Based on my research, because the VBA code will be compiled and you are
trying to add a component to the currently running and compiled UserForm,
therefore your object is not yet included when the code is compiled and you
get the error.
So far I think you should be creating all of your controls on the UserForm
in design time just like VB, you can hide any you don't want visible.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

I need one text box for each worksheet (variable) in the workbook. Is it
possible to use API calls to hook the Text_changed event for the control to
some existing sub, perhaps passing a handle to the control that raised the
event?

""Peter Huang" [MSFT]" wrote:

Hi

Based on my research, because the VBA code will be compiled and you are
trying to add a component to the currently running and compiled UserForm,
therefore your object is not yet included when the code is compiled and you
get the error.
So far I think you should be creating all of your controls on the UserForm
in design time just like VB, you can hide any you don't want visible.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi

I am sorry I may have some confusion about your scenario.
Because the UserForm is included in the workbook but not the worksheet, I
assume here you want to add the button onto the Worksheet but not the
userform.
If so, I think you may try to take a look at the link below.
How To Add a Button to a Word Document and Assign Its Click Event at
Run-time
http://support.microsoft.com/?id=246299

If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Peter,
No, everything is on the UserForm. It is simply the number of TextBoxes that
I need to add to the UserForm that is related to the number of sheets in the
workbook.

John

""Peter Huang" [MSFT]" wrote:

Hi

I am sorry I may have some confusion about your scenario.
Because the UserForm is included in the workbook but not the worksheet, I
assume here you want to add the button onto the Worksheet but not the
userform.
If so, I think you may try to take a look at the link below.
How To Add a Button to a Word Document and Assign Its Click Event at
Run-time
http://support.microsoft.com/?id=246299

If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.