Excel VBA Run-time error 424 Object Required when trying to copy TextBox

Question

I m attempting to copy the contents of a text box from one workbook to another   I have no problem copying cell values from the first workbook to the 2nd  but I get an object required error when I attempt to copy the text box   This macro is being run from the workbook containing the data I want copied   Using Excel 2007 Code   Sub UploadData       Dim xlo As New Excel Application     Dim xlw As New Excel Workbook     Set xlw   xlo Workbooks Open  c  myworkbook xlsx       xlo Worksheets 1  Cells 2  1    Range  d4   Value  Copy cell content  this works fine      xlo Worksheets 1  Cells 2  2    TextBox1 Text  This gives me the object required error     xlw Save     xlw Close     Set xlo   Nothing     Set xlw   Nothing End Sub   Thanks for any help

User · Answer

The issue is with this line   xlo Worksheets 1  Cells 2  2    TextBox1 Text   You have the textbox defined at some other location which you are not using here  Excel is unable to find the textbox object in the current sheet while this textbox was defined in xlw   Hence replace this with    xlo Worksheets 1  Cells 2  2    worksheets  xlw   TextBox1 Text

User · Answer

I think the reason that this is happening could be because TextBox1 is scoping to the VBA module and its associated sheet  while Range is scoping to the  Active Sheet    EDIT  It looks like you may be able to use the GetObject function to pull the textbox from the workbook

User · Answer

The problem with your macro is that once you have opened your destination Workbook  xlw in your code sample   it is set as the ActiveWorkbook object and you get an error because TextBox1 doesn t exist in that specific Workbook  To resolve this issue  you could define a reference object to your actual Workbook before opening the other one   Sub UploadData       Dim xlo As New Excel Application     Dim xlw As New Excel Workbook     Dim myWb as Excel Workbook      Set myWb   ActiveWorkbook     Set xlw   xlo Workbooks Open  c  myworkbook xlsx       xlo Worksheets 1  Cells 2  1    myWb ActiveSheet Range  d4   Value     xlo Worksheets 1  Cells 2  2    myWb ActiveSheet TextBox1 Text      xlw Save     xlw Close     Set xlo   Nothing     Set xlw   Nothing End Sub   If you prefer  you could also use myWb Activate to put back your main Workbook as active  It will also work if you do it with a Worksheet object  Using one or another mostly depends on what you want to do  if there are multiple sheets  etc

[excel] Excel VBA Run-time error '424': Object Required when trying to copy TextBox

Examples related to excel

Examples related to vba

Examples related to runtime