Excel VBA 运行时错误“424”:尝试复制文本框时需要对象

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/16084686/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 15:26:38  来源:igfitidea点击:

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

excelvbaruntime

提问by Sam WB

I'm attempting to copy the contents of a text box from one workbookto another. I have no problem copying cell values from the first workbookto the 2nd, but I get an object requirederror when I attempt to copy the text box. This macrois being run from the workbook containing the data I want copied. Using Excel 2007Code:

我试图将文本框的内容从一个复制workbook到另一个。我将单元格值从第一个复制workbook到第二个没有问题,但是object required当我尝试复制文本框时出现错误。这macro是从包含我要复制的数据的工作簿中运行的。使用Excel 2007代码:

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.

谢谢你的帮助。

采纳答案by dan

The problem with your macro is that once you have opened your destination Workbook(xlwin your code sample), it is set as the ActiveWorkbookobject and you get an error because TextBox1doesn't exist in that specific Workbook. To resolve this issue, you could define a reference object to your actual Workbookbefore opening the other one.

您的宏的问题在于,一旦您打开了目标Workbookxlw在您的代码示例中),它就会被设置为ActiveWorkbook对象,并且您会收到错误消息,因为TextBox1不存在于该特定Workbook 中。要解决此问题,您可以在打开另一个之前定义一个指向实际工作簿的引用对象。

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.Activateto put back your main Workbookas active. It will also work if you do it with a Worksheetobject. Using one or another mostly depends on what you want to do (if there are multiple sheets, etc.).

如果您愿意,也可以使用myWb.Activate将主工作簿恢复为活动状态。如果您使用Worksheet对象执行此操作,它也将起作用。使用一个或另一个主要取决于您想要做什么(如果有多个工作表等)。

回答by Seth Moore

I think the reason that this is happening could be because TextBox1is scoping to the VBA module and its associated sheet, while Range is scoping to the "Active Sheet".

我认为发生这种情况的原因可能是因为TextBox1范围是 VBA 模块及其关联的工作表,而范围是范围是“活动工作表”。

EDIT

编辑

It looks like you may be able to use the GetObjectfunction to pull the textbox from the workbook.

看起来您可以使用GetObject函数从工作簿中提取文本框。

回答by kunal

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.

您在此处未使用的其他位置定义了文本框。在 xlw 中定义此文本框时,Excel 无法在当前工作表中找到该文本框对象。

Hence replace this with

因此将其替换为

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