+ Reply to Thread
Results 1 to 9 of 9

Event procedures for controls added with CreateEventProc

  1. #1
    John Austin
    Guest

    Event procedures for controls added with CreateEventProc

    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

  2. #2
    Peter Huang [MSFT]
    Guest

    RE: Event procedures for controls added with CreateEventProc

    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.


  3. #3
    Peter Huang [MSFT]
    Guest

    RE: Event procedures for controls added with CreateEventProc

    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.


  4. #4
    John Austin
    Guest

    RE: Event procedures for controls added with CreateEventProc

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


  5. #5
    Peter Huang [MSFT]
    Guest

    RE: Event procedures for controls added with CreateEventProc

    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.


  6. #6
    John Austin
    Guest

    RE: Event procedures for controls added with CreateEventProc

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


  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    John

    Why are you adding a button for each worksheet?

    Can't you have a combobox, populated when the form is initialized, listing all the worksheets in the workbook?

    You could then have one button that uses the value from the combobox.

  8. #8
    Peter Huang [MSFT]
    Guest

    RE: Event procedures for controls added with CreateEventProc

    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.


  9. #9
    Peter T
    Guest

    Re: Event procedures for controls added with CreateEventProc

    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

    ""Peter Huang" [MSFT]" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1