0

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
Jessica Archote
  • 1
  • 2
  • `Text` is a read only property. For Checkboxes you would use the `Caption` property to set the text of the Checkboxes. The Value property is true/false. For Combo boxes they are a bit more complicated. Use the AddItem method. – SmileyFtW Mar 23 '20 at 13:58
  • @SmileyFtW But wouldn't AddItem add another option to the drop-down? The goal is to copy the value of a different cell, and paste it as the input into the combo box. – Jessica Archote Mar 23 '20 at 16:28
  • In your first code, I think instead of COPY you can store the source cell value in a variable. Then assign that value to qstnr object as done in the second code... For example.. `Dim ValVariable` ... `ValVariable = source.cells(8,2)` .. `qstnr.OLEObjects("Cbo_RptOrg").Object.Value = ValVariable` – Naresh Mar 24 '20 at 13:00
  • If you use a linked cell two things happen 1) if you change the value in the linked cell, the value in the combo box reflects the value in the linked cell 2) if you change the value in the combo box the value in the linked cell changes to reflect the value in the combo box. I misunderstood what you are trying to do. Maybe a more in depth explanation would help us provide some guidance – SmileyFtW Mar 24 '20 at 19:39

1 Answers1

0
    ' 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.

Jessica Archote
  • 1
  • 2