Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    on click event for code created controls (xp)

    I have code that creates a number of command button controls (number created depends on the user)

    e.g.

    For x = 1 to UserChoice
    set MyArray(x) = Controls.Add("Forms.commandbutton.1", "score_" & x, True)

    (other code)
    next x


    How do I then create code that will operate when the button is clicked (click event), I tried creating a sub:

    Private Sub score_1_click()
    OtherSub 1
    end sub



    othersub (input as integer)
    ...
    end sub


    But this wouldn't start when the button was clicked. Also this means that I would need to create a code for an unknown number of buttons (as user decides)

    All I need from each button is a value. For example if score_1 is clicked then the code needs to run "othersub" with the parameter for that sub being 1

    So is there a way of creating the on click event code at run time rather than at design time?

    Thanks in advance

    Simon

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: on click event for code created controls (xp)

    What application are you doing this in? The mechanics will differ between apps.
    Charlotte

  4. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on click event for code created controls (xp)

    Sorry, bit slack:

    I am using VBA in Excel 2002. The about window says VBA 6.3

    Thanks

    Simon

  5. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: on click event for code created controls (xp)

    The following code should work for command buttons on the active worksheet. It requires that the buttons be named "Score_x", where x is a unique numeric identifier.

    This code should be run after you add all the required buttons. If you are working with a UserForm a similar approach could be used.

    Dim Ctrl As OLEObject, shtModule
    Set shtModule = ThisWorkbook.VBProject.VBComponents(ActiveSheet.Na me).CodeModule
    For Each Ctrl In ActiveSheet.OLEObjects
    If Ctrl.progID = "Forms.CommandButton.1" _
    And Left(Ctrl.Name, 6) = "Score_" Then
    strCode = "Private Sub " & Ctrl.Name & "_Click" & _
    vbCrLf & vbTab & "OtherSub " & _
    Val(Right(Ctrl.Name, Len(Ctrl.Name) - 6)) & _
    vbCrLf & "End Sub"
    With shtModule
    .InsertLines .CountOfLines + 1, strCode
    End With
    End If
    Next

    Andrew C

  6. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on click event for code created controls (xp)

    Thanks for that code Andrew, I have learnt some more!.

    However (as always) I think the problem I am having is more fundamental than that (as the code did not solve the problem)

    Try adding this to a blank user form

    Private Sub UserForm_Initialize()
    Dim Cmdbutton As Control

    Set Cmdbutton = Controls.Add("forms.commandbutton.1", "Instruction", True)

    End Sub


    Private Sub Instruction_click()
    MsgBox "1"

    End Sub

    Private Sub Cmdbutton_click()
    MsgBox "2"
    End Sub

    When the form is run the button appears, but clicking on it does not trigger the click event (ie Private Sub Instruction_click()). I tried refering to the variable (Private Sub Cmdbutton_click()), but that doesn't work either.

    Yet, if I add a button (commandbutton1) manually to the form the event code would be commandbutton1_click()

    Any ideas?

    Simon

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: on click event for code created controls (xp)

    I don't know if this will help, but you might have a look at John Walkenbach's Creating a UserForm Programmatically. It contains a downloadable demo of creating a userform with working controls entirely in code.

  8. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts

    Re: on click event for code created controls (xp)

    This info from the MS Office 2000 VB Developers Guide might also be worth a look.

  9. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on click event for code created controls (xp)

    All of this is much simpler when you use a button from the forms toolbar:

    Sub AddButton()
    Dim oButton As Object
    Dim iCount As Integer
    For iCount = 1 To 4
    Set oButton = ActiveSheet.Buttons.Add(Left:=10, Top:=10 + 50 * iCount, Width:=50, Height:=50)
    oButton.OnAction = "test"
    oButton.Name = "Button_" & iCount
    oButton.Caption = "Button_" & iCount
    Next
    End Sub

    Sub test()
    MsgBox Application.Caller
    End Sub

    You can have one single sub in a normal module that handles the click of each button.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: on click event for code created controls (xp)

    You are likely running into a problem that requires saving the workbookbook so that Excel knows the control exists.

    Tryadding, say:

    Thisworkbook.Save

    Right after you create the control.

Posting Permissions

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