Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    7

    Red face Unanswered: VBA ComboBox Change control when ComboBox created on initialize

    Hi all,

    I have created a series of combo boxes upon userform intialize. However, I can't seem to reference them on a change event. I name them as "Ctr_" & variable number. There is a maximum of 10. I was hoping I could then reference them with a simple sub Ctrl_1_Change(), sub Ctrl_2_Change() but it doesn't appear to recognise the control name.

    My code to add runs through a loop with 'c' as the loop variable e.g. Set cCntrl = Frame2.Controls.Add("Forms.ComboxBox.1", "Ctr_" & c, True)

    Any ideas?

    Thanks in advance for any help.

    Marty

  2. #2
    Join Date
    Aug 2012
    Posts
    7
    I can manually create a combobox with the name 'Ctr_11' and the change event works. Also, surprisingly, I don't receive an ambiguous name error when the form initializes. I believe it can't recognise them as objects/controls, I have tried declaring them as objects rather than controls. Here is all of the code;

    Private Sub UserForm_Initialize()

    'Frame 2 Machine Components Add labels and controls

    For c = 1 To Range("Tbl_MachineComponents").Rows.Count 'Max 10
    Set cCntrl = Frame2.controls.Add("Forms.Label.1")
    With cCntrl
    .Name = "Lbl_" & c + 10
    .Width = 144
    .Height = 18
    .Top = 12
    .Left = 6
    .ZOrder (0)
    .BackStyle = 0
    .Caption = Sheets("Matrices").Range("E8").Offset(c, 0).Value
    End With
    Set cCntrl = Frame2.controls.Add("Forms." & Sheets("Matrices").Range("E8").Offset(c, 1).Value & ".1")
    With cCntrl
    .Name = "Ctr_" & c + 10
    .Width = 144
    .Height = 18
    .Top = uvTop
    .Left = 150
    .ZOrder (0)
    'Add items to comboboxes, works fine
    If Sheets("Matrices").Range("E8").Offset(c, 1).Value = "ComboBox" Then
    For l = 1 To Range("Tbl_MachineList").Rows.Count
    If Sheets("Matrices").Range("E21").Offset(l, 0).Value = Sheets("Matrices").Range("E8").Offset(c, 0).Value Then
    .AddItem Sheets("Matrices").Range("E21").Offset(l, 1).Value
    End If
    Next l
    End If
    End With
    Next c

    Exit sub

    Private Sub Ctr_11_Change()
    MsgBox "woohoo"
    End Sub

  3. #3
    Join Date
    Aug 2012
    Posts
    7
    Hi all,

    I have got around this problem, by creating just comboboxes, but then changing their property values to not show a dropdown button where previously I would have created a text box. I used the following code;

    Me.Controls("ComboBox" & c).ShowDropButtonWhen = fmShowDropButtonWhenAlways

Posting Permissions

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