Excel VBA add code userform programmatically

问题:

I got a tricky issue with my VBA-code. The situation is that I have a manual created userform. I add controls to the userform with a macro and it works fine for me. But now I also need to add event-code to the userform. Following code I want to add with .CodeModule.InsertLines. The important part is that the textboxes, which I want to call should work variably, but it doesn't work, any ideas how to fix this? (Textboxes are named like this: textbox_0, textbox_1 and following)

Dim iMaxColumns As Integer
Dim iCount As Integer 

iMaxColumns = Tabelle4.Cells(8, 2).Value
Dim vArray(0 To iMaxColumns - 1) As String


For iCount = 0 To iMaxColumns - 1
    vArray(iCount) = textbox_ & iCount &.Value
Next

'do sth. with the arrray

I assume the problem is that I can't add variables to my textbox object. I also could work with the complete path to my textbox calling it with .Designer.Controls("textbox_" & iCount & "") but that is a bunch of code and I hope to avoid that.

回答1:

I figured out a quite easy way to solve my problem. I wrote all the necessary code in a seperate module. There I can address all the variables and information I need. After that when creating the UserForm I just copy all the code into the UserForms code block.

Public Function edit_userform(strUserForm As String, _
strUserFormEvents As String)
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim VBComp_Event As VBIDE.VBComponent
    Dim strCode As String

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(strUserForm)
    Set VBComp_Event = VBProj.VBComponents(strUserFormEvents)

    With VBComp_Event.CodeModule
        strCode = .Lines(1, .CountOfLines)
    End With

    VBComp.CodeModule.AddFromString strCode
End Function