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
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:
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
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, _
'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
Then all we need is a custom class module called c_Combobox:
Public WithEvents cbx As MSForms.ComboBox
Sub SetCombobox(ctl As MSForms.ComboBox)
Set cbx = ctl
'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
I have attached an example workbook containing this code so you can download and experiment with it.
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 Have A Problem ( can not call procedure from Class)
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
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.
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