Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Detecting Click Event of Dynamically created controls

    I have created a Userform using Excel 2007 VBA. I have written code to add a number of labels and comboboxes to the Userform (dependent on a given number of perosnnel at work). The form looks good! But I am struggling to get the prgramme to return the text from the comboboxes! HELP

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    How many comboboxes (the maximum) are we talking about here?

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    The form could have up to 40 comboboxes. Further, I would like to be able to use the spreadsheet with both Excel 2003 & 2007!

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    One option would be to programatically add event handler code to the userform's class module as you add the comboboxes.

    Another option would be to add all of the comboboxes at design time, set their visible properties to false, and then just make whichever ones you need visible at runtime.

    Given the number of comboboxes (40!!!) another option would be to use a class module to mimic a control array - we can set up a communal event handler shared by all of the comboboxes which saves having to use repetitive code. I've attached a simple demonstration of this which dynamically adds 10 comboboxes and gives a message box when the Change event is raised.

    All that's required is a blank userform called ufm_Example.
    In the ufm_Example class module resides this code:
    Code:
    Option Explicit
    
    Const sngUFM_WIDTH As Single = 300
    Const sngUFM_HEIGHT As Single = 500
    
    Private pComboboxes As Collection
    
    Private Sub UserForm_Initialize()
    
        'resize the userform
        Me.Width = sngUFM_WIDTH
        Me.Height = sngUFM_HEIGHT
        
        AddComboboxes
    End Sub
    
    
    
    Private Sub AddComboboxes()
        Const sngCBX_WIDTH As Single = 100
        Const sngCBX_HEIGHT As Single = 20
        Const intCBX_TO_ADD As Integer = 10
        
        Dim intCounter As Integer
        Dim cbx As c_Combobox
        Dim ctl As MSForms.ComboBox
        
        If pComboboxes Is Nothing Then Set pComboboxes = New Collection
        
        'for this example let's add 10 comboboxes to our userform
        For intCounter = 1 To intCBX_TO_ADD
        
            'add the control
            Set ctl = Me.Controls.Add(bstrProgID:="Forms.ComboBox.1", _
                                        Name:="Combobox" & intCounter, _
                                        Visible:=True)
                          
            With ctl
                'position the combobox
                .Width = sngCBX_WIDTH
                .Height = sngCBX_HEIGHT
                .Left = Me.Left + ((Me.Width - ctl.Width) / 2)
                .Top = Me.Top + (Me.Height / (intCBX_TO_ADD + 2) * intCounter)
                
                
                'populate the combobox
                .List = Sheet1.Range(Sheet1.Cells(2, intCounter), Sheet1.Cells(9, intCounter)).Value
                
                
                Set cbx = New c_Combobox
                cbx.SetCombobox ctl
                pComboboxes.Add cbx
            End With
            
        Next intCounter
        
                                        
        
    End Sub
    Then all we need is a custom class module called c_Combobox:
    Code:
    Option Explicit
    
    Public WithEvents cbx As MSForms.ComboBox
    
    Sub SetCombobox(ctl As MSForms.ComboBox)
        Set cbx = ctl
    End Sub
    
    'a generic change event handler for our comboboxes
    Private Sub cbx_Change()
        If cbx.ListIndex > -1 Then
            MsgBox "You clicked on " & cbx.Name & vbLf & _
                    "The value is " & cbx.Value
        End If
    End Sub

    I have attached an example workbook containing this code so you can download and experiment with it.



    I hope that helps...
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2009
    Posts
    3

    Thanx to Colin L!!!!!!!!!!!!!!

    The routine you submitted is perfect! Thanks a million!

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    You're welcome! Good luck with the rest of your project...

  7. #7
    Join Date
    Mar 2012
    Posts
    2

    Just what I was looking for

    Hi
    I have 210 Possible comboboxes accross 10 pages in a multipage (all created as requred at runtime). So I hope this work as I have not had the time as of yet to test it , but I can not see why it wont

    I will let you know how I get on

    Thanks again

  8. #8
    Join Date
    Mar 2012
    Posts
    2

    I Have A Problem ( can not call procedure from Class)

    Hi

    The code you provided works fine but when I change the msgbox to a procedure call (Calc_Price cbo.text, cboidx) I can not find the procedure although the same procedure call from the predefined cboboxes works fine

    Any suggestions??

    Bruce

  9. #9
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    hi Bruce,

    It's probably because of the scope of the Calc_Price procedure. Is it declare as Private or Public, and is it in the userform class module or in a standard code module? Where it should be placed really depends on what the procedure does and what it uses, but you'll have to make sure it can be "seen" by the c_Combobox objects.

  10. #10
    Join Date
    Aug 2013
    Posts
    1
    Hi i am working on a project.
    there is a userform.. on each click one combobox and right to that a text box create.
    if we select a data from the list from combobox and enter value in text box .. after entering value if i hit submit button it should go to perticular cell of an excel..
    could you please tell how to do it

Posting Permissions

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