VBA Programatically added Combobox and code - code doesn't want to execute????

[Dustan]

I have a btnclick event that adds a combobox to a userform. This works fine. I have it set the left, top, width, font.color, font.italic and font.text attributes - It all works good. Here is that code...

strComboBox = "Forms.ComboBox.1"
strTextBox = "Forms.TextBox.1"
strLabel = "Forms.Label.1"


   intNumberOfLines = txtNumberOfLines.Value
    intNewLineNumber = intNumberOfLines + 1


    strNewPartNumberComboboxName = "cmbPartNumber" & intNewLineNumber
    
    Set cmbPartNumberCombobox = NewPurchaseOrder.Controls.Add(strComboBox, strNewPartNumberComboboxName)
    With cmbPartNumberCombobox
        .Top = lngTopofCells + lngStepsLong
        .Left = lngPartNumberLeft
        .Width = lngPartNumberWidth
        .ForeColor = &H8000000C
        .Font.Italic = True
        .Text = "Part Number:"
    End With

I then have the btnclick event write an enter and exit procedure for the newly created control. Again, this works as expected. Code is as follows...

    Dim strCodeToWrite As String
    
    strCodeToWrite = "Private Sub " & strNewPartNumberComboboxName & "_Enter()" & vbCrLf
    strCodeToWrite = strCodeToWrite & "If " & strNewPartNumberComboboxName & ".Text = ""Part Number:"" Then" & vbCrLf
    strCodeToWrite = strCodeToWrite & strNewPartNumberComboboxName & ".Text = """"" & vbCrLf
    strCodeToWrite = strCodeToWrite & strNewPartNumberComboboxName & ".Font.Italic = False" & vbCrLf
    strCodeToWrite = strCodeToWrite & strNewPartNumberComboboxName & ".ForeColor = &H80000008" & vbCrLf
    strCodeToWrite = strCodeToWrite & "End If" & vbCrLf
    strCodeToWrite = strCodeToWrite & "End Sub"
    
    ActiveWorkbook.VBProject.VBComponents("NewPurchaseOrder").CodeModule.AddFromString strCodeToWrite


    strCodeToWrite = "Private Sub " & strNewPartNumberComboboxName & "_Exit(ByVal Cancel As MSForms.ReturnBoolean)" & vbCrLf
    strCodeToWrite = strCodeToWrite & "Dim strPartNumber As String" & vbCrLf
    strCodeToWrite = strCodeToWrite & "strPartNumber = " & strNewPartNumberComboboxName & ".Value" & vbCrLf
    strCodeToWrite = strCodeToWrite & vbCrLf
    strCodeToWrite = strCodeToWrite & "If strPartNumber = """" Then" & vbCrLf
    strCodeToWrite = strCodeToWrite & strNewPartNumberComboboxName & ".Font.Italic = True" & vbCrLf
    strCodeToWrite = strCodeToWrite & strNewPartNumberComboboxName & ".ForeColor = &H8000000C" & vbCrLf
    strCodeToWrite = strCodeToWrite & strNewPartNumberComboboxName & ".Text = ""Part Number:""" & vbCrLf
    strCodeToWrite = strCodeToWrite & "End If" & vbCrLf
    strCodeToWrite = strCodeToWrite & "End Sub"


    ActiveWorkbook.VBProject.VBComponents("NewPurchaseOrder").CodeModule.AddFromString strCodeToWrite

This produces both the enter and exit procedures that I want - procedures that work as requested on controls that were added in design mode. They look like this...

Private Sub cmbPartNumber2_Enter()
If cmbPartNumber2.Text = "Part Number:" Then
    cmbPartNumber2.Text = ""
    cmbPartNumber2.Font.Italic = False
    cmbPartNumber2.ForeColor = &H80000008
End If
End Sub


Private Sub cmbPartNumber2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strPartNumber As String
strPartNumber = cmbPartNumber1.Value


If strPartNumber = "" Then
    cmbPartNumber2.Font.Italic = True
    cmbPartNumber2.ForeColor = &H8000000C
    cmbPartNumber2.Text = "Part Number:"
End If
End Sub

The issue that I am running in to is.... I can't get the Enter or Exit procedures to run on the control that is added programatically. When I run the code, the userform pops up. When I click the button - triggering the btnclick procedure to add the new control and the vba code - the new control appears with the text displayed "Part Number:" as expected. When I click on the control - it is supposed to execute the ENTER procedure that was written by the btnclick procedure, a procedure that works on design added controls - nothing happens. It is supposed to remove the text "Part Number:", change the italics and font color. The cursor defaults to the end of the "Part Number:" text.... Not changing the properties or removing the text as it is supposed to.

Any help is greatly appreciated.

 

[Kyle123]

Firstly, don’t add event handlers like that, it’s hacky and other users won’t be able to run your code without lowering their securities settings. You should be using a class to create an object that subscribes to the events raised by your button, see here: https://stackoverflow.com/questions...-created-dynamically-in-vba/10596866#10596866

Secondly, it’s difficult to respond to the enter and exit events for controls added at runtime, you need to code an alternative, have a look here: https://www.excelforum.com/excel-pr...-multiple-userform-textboxes.html#post5040782