Excel VBA以编程方式添加代码用户窗体 [英] Excel VBA add code userform programmatically

问题描述

我的VBA代码遇到了一个棘手的问题.情况是，我有一个手动创建的用户表单.我使用宏将控件添加到用户窗体，它对我来说很好用.但是现在我还需要将事件代码添加到用户表单中.以下代码我想用.CodeModule.InsertLines添加.重要的是，我要调用的文本框应该可变地工作，但它不工作，有什么想法可以解决此问题? (文本框的命名如下:textbox_0，textbox_1及以下)

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

我认为问题是我无法向我的文本框对象添加变量.我也可以使用文本框的完整路径来工作，并使用.Designer.Controls("textbox_" & iCount & "")调用它，但这是一堆代码，我希望避免这种情况.

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.

推荐答案

我想出了一种非常简单的方法来解决我的问题.我在单独的模块中编写了所有必要的代码.在这里我可以解决我需要的所有变量和信息.之后，在创建UserForm时，我只需将所有代码复制到UserForms代码块中.

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以编程方式添加代码用户窗体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！