excelvbacopy-pasteactivexobjectobject-properties

Pasting into Excel ActiveX ComboBox


I'm trying to copy data from one workbook into another workbook that is build as a survey.

In the survey form, we are using ActiveX controls for combo boxes and check boxes. I left two samples of ways I have tried (and failed).

Sub TransferData()

Set Source = Workbooks.Open("FromHere.xlsm")
Set qstnr = Workbooks.Open("ToHere.xlsx")

' Banner Form Classification
    Source.Activate
    Cells(8, 2).Copy
    qstnr.Activate
    Set Cbo_Classification = qstnr.OLEObjects("Cbo_Classification")
    With Cbo_Classification.Object
     .Text = "Not sure what to do here"
    End With

' Reporting Organization
    Source.Activate
    Cells(9, 2).Copy
    qstnr.Activate
    'ActiveSheet.OLEObjects("Cbo_RptOrg").PasteSpecial Paste:=xlPasteValues

End Sub

EDIT: I have been able to get the object to be pasted into when working in the same workbook with the copy below. I don't understand why it's not successful when working outside the document.

Sub TransferObjects()

Dim wbk As Workbook: Set wbk = Workbooks.Open("CopyFrom.xlsm")
Dim tmplt As Workbook: Set tmplt = Workbooks.Open("CopyTo.xlsx")
Dim qstnr As Worksheet

Set qstnr = tmplt.Sheets("Sheet1")

qstnr.OLEObjects("Cbo_RptOrg").Object.Value = Range("K12").Value

End Sub

Solution

  •     ' Reporting Organization
        Source.Activate
        Dim Cbo_RptOrg As Variant
        Cbo_RptOrg = Cells(2, 9).Value
        qstnr.OLEObjects("Cbo_RptOrg").Object.Value = Cbo_RptOrg
    

    This ended up working. Using a variable as suggested.