Excel VBA add code userform programmatically

Question

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.

The code you posted seems unrelated to the problem you describe. Are you asking how to write that code to the form's code behind? — RubberDuck, Nov 16 '14 at 14:52
@RubberDuck: No, I know how to write the code to the form's code. I need to call the Textboxes variabel. But they are objects. So I need to figure out how to call objects on a variabel name ;) — Exic, Nov 16 '14 at 15:37
Can you provide an example of the code you want written into the code behind? I think I can help, but aren't real clear on what you're trying to accomplish. — RubberDuck, Nov 16 '14 at 16:00
Hard to understand your question but sounds like you need to use the Controls Collection of the form and the Add method to add the controls. You can then access the controls programatically. You can create a custom class module with member of class MSForms.TextBox, declared using WithEvents as well as whatever common event handlers you need. Also, use this method to instantiate the form so you can mess with it: `Dim f As UserForm1:Set f = New UserForm1:f.Show` — Cool Blue, Nov 16 '14 at 16:42

score 1 · Accepted Answer · answered Dec 13 '14 at 14:17

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

Excel VBA add code userform programmatically

1 Answers1

Linked