Event procedures for controls added with CreateEventProc

[John Austin]

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,

 

[Peter Huang [MSFT]]

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.

 

[Peter Huang [MSFT]]

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.

 

[John Austin]

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]]

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.

 

[John Austin]

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]]

Hi

I think it is hard to do that.
Because althougth the userform is compiled, but it is compiled with the vba
special p-code which have no public document, and then it will run in the
VBA runtime, there have no a standone process to run the vba code, it is
all in the vba runtime. So I think the win32 API will have no help here to
change unknown format code.

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

Also I think you may try to post the feedback to MSWish website.
http://register.microsoft.com/mswish/suggestion.asp

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.

 

[Peter T]

FWIW, this problem appears to be similar to what emerged in a discussion
concerning adding withevents code to newly added worksheet controls.

Subject: running same code with multiple controls (Jan 2005)
http://tinyurl.com/6uj3d

Regards,
Peter T