Geek asks and answers

Excel VBA Runtime Error "424": Object Required when trying to copy a TextBox

I am trying to copy the contents of a text field from one workbookto another. I have no problem copying cell values ​​from the first workbookto the second, but I get an error object requiredwhen trying to copy a text box. This one macrostarts from a book containing the data I want to copy. Usage Excel 2007Code:

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.

+5
source share
3 answers

, , (xlw ), ActiveWorkbook, , TextBox1 . , , .

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

, myWb.Activate, . , Worksheet. , ( ..).

+4

, , , , TextBox1 VBA , Range " ".

, GetObject, .

+1

Problem with this line

 xlo.Worksheets(1).Cells(2, 2) = TextBox1.Text

You have a text box defined elsewhere that you are not using here. Excel cannot find the text field object in the current sheet while this text field was defined in xlw.

Hence replace it with

 xlo.Worksheets(1).Cells(2, 2) = worksheets("xlw").TextBox1.Text
0
source

More articles:


All Articles