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
Excel VBA Run-time error '424': Object Required when trying to copy TextBox
提问by Sam WB
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:
我试图将文本框的内容从一个复制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(xlw
in 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.
您的宏的问题在于,一旦您打开了目标Workbook(xlw
在您的代码示例中),它就会被设置为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.Activate
to 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 TextBox1
is 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