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

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

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

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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.

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

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

  9. #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
  •