将代码分配给组合框的_Change方法 [英] Assign code to _Change method of a Combobox

问题描述

下面的代码在工作表的单元格 A3 中添加了一个 ComboBox 。它还写入代码，以便在 ComboBox 中更改值时，使用该值更新单元格 A2 。这是代码：

below code adds a ComboBox in cell A3 of a worksheet. It also writes code so that when the value is changed in the ComboBox, cell A2 is updated with the value. Here is the code:

Sub AddComboBox()

    Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet7")
    Dim oRN As Range: Set oRN = oWS.Range("A3")
    Dim oCB As Object
    Dim sCode As String
    Dim iLR As Integer

    ' Get last row for column D (holds the values for combobox)
    With oWS
        iLR = .Cells(.Rows.Count, "D").End(xlUp).Row
    End With

    ' Create Combobox
    With oRN

        Set oCB = oWS.OLEObjects.Add("Forms.Combobox.1", Left:=.Left, Top:=.Top, Height:=.Height, Width:=.Width)
        oCB.ListFillRange = "Sheet7!D1:D" & iLR
        oCB.Name = "cmbTest1"
        oCB.Object.Font.Size = 8

    End With

    ' Set code to add for the Combobox
    sCode = "Private Sub " & oCB.Name & "_Change()" & Chr(13) & _
            "   ThisWorkbook.Worksheets(" & Chr(34) & oWS.Name & Chr(34) & ").Range(" & Chr(34) & "A2" _
                & Chr(34) & ").Value = Me." & oCB.Name & ".Value" & _
            "End Sub"

    ' Add the code for Combobox
    With ThisWorkbook.VBProject.VBComponents(oWS.Name).CodeModule
        .InsertLines .CountOfLines + 1, sCode
    End With

End Sub

代码工作正常但是为了使其工作，用户必须确保在信任中心中选择了对VBA项目对象模型的信任访问。我的问题是，是否有另一种方法来解决这个问题，以便用户不必在PC上更改设置？即可能只是在Class模块中分配一个宏。我试过这个，但我需要能够通过调用UDF的 ComboBox 的名称，但不知道如何做到这一点，或者甚至可以用这种方法？我想要传递 ComboBox 的名称的原因是因为： ComboBox 的命名约定将给我单元格地址其中创建了 ComboBox 。我需要这样做，因为工作表中的每一行将有多个 ComboBoxes （动态创建），表格中将有多行

Code works fine but for it to work, user has to make sure that Trust access to the VBA project object model is selected in Trust Center. My question is, is there another way to approach this problem so that user doesn't have to change the settings on their PC? i.e. maybe just assign a macro in a Class module. I did give this a try but I need to be able to pass the name of the ComboBox to the called UDF but have no idea how to do that or if that is even possible with this approach? Reason why I want to pass the name of ComboBox is because: the naming convention for ComboBox will give me the cell address where the ComboBox is created. I need this as each row in the sheet will have multiple ComboBoxes (created dynamically) and there will be multiple rows in the sheet

推荐答案

信托中心是出于某种原因。

The Trust Centre is there for a reason.

为了解决这个问题，你可以把xl -vba文件在特定文件夹中。然后将此文件夹作为受信任的位置，通过可信赖的设置，如下所示：
信任中心>信任中心设置>受信任的位置

In order to get around it, you may put the xl-vba file in a specific folder. Then add this folder as a trusted location, through the Trusted Settings like this: Trust Center>Trust Center Setting>Trusted Locations

但我不知道是否这将比确保选择信任访问更少的工作。但是，这是一个选项。

But I am not sure whether this would be less job than making sure that the Trust access is selected. However, it is an option.

这篇关于将代码分配给组合框的_Change方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！