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

excel vba runtime

154,886

Solution 1

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.).

Solution 2

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.

154,886

Sam WB

Updated on July 05, 2022

Comments

Sam WB almost 2 years
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.
- dan about 11 years
  
  You tried with the full Application.ActiveWorkbook.ActiveSheet.TextBox1? Can you check if the ActiveWorkbook/ActiveSheet isn't xlw? If this is the case, you just have to set an object for your active document before opening another.
- Kazimierz Jawor about 11 years
  
  try this ...= activesheet.TextBox1.Object.value or .Text property at the end should work too
- Sam WB about 11 years
  
  I get an 'object doesn't support this property or method' with either of your solutions dnLL & KazJaw
- dan about 11 years
  
  But did you check what ActiveWorkbook/ActiveSheet are referring to? Seems like it refers to xlw (c:\myworkbook.xlsx) and not the document where is located TextBox1.
- Kazimierz Jawor about 11 years
  
  test also shorter option ...= Activesheet.TextBox1.Text It's important to precede user controls with sheet object. However, if it doesn't work could you make a screen shot of your TextBox and add a link to that picture here?
- Sam WB about 11 years
  
  Activesheet.TextBox1.Text gives the same error It would appear that activesheet is referring to the destination workbook anyways
- dan almost 11 years
  
  Which means you need to add a reference objet to the right workbook. See my answer.
Sam WB about 11 years

Any idea how I can scope textbox1 to the active sheet? I don't believe I can use ActiveSheet.TextBox1
dan about 11 years

This should have been posted as a comment, not an answer.
Seth Moore almost 11 years

@dnLL Why? It is an answer, not a comment or question. It's not as thorough as your answer, but it is an answer by definition.
dan almost 11 years

I guess you are referring to the 8th definition. I still don't think that 2 sentences starting with "I think" and "you may be" is an answer.