Assign code to _Change method of a Combobox

Question

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

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

Answer 1

The Trust Centre is there for a reason.

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.