在 Excel 加载项中选择工作表以运行 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19125612/
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
Select worksheet in Excel add-in to run VBA macro
提问by VBAlearner
I have created an add-in using VBA which is a workbook with calculations. The add-in has a userform to extract relevant information from access database and populates the workbook. After the data is populated, calculations are performed in Sheet1. I need to paste the worksheet "Sheet1" from the add-in worksheet to a new workbook on running the add-in macro.
我使用 VBA 创建了一个加载项，它是一个带有计算的工作簿。加载项有一个用户表单，用于从访问数据库中提取相关信息并填充工作簿。填充数据后，将在 Sheet1 中执行计算。我需要将工作表“Sheet1”从加载项工作表粘贴到运行加载项宏的新工作簿中。
When I run the add-in however, the worksheet appears to be hidden so my data is not updating. I get this error: " Run-time error '1004': Method 'Worksheets' of object '_Global' failed".
但是，当我运行加载项时，工作表似乎被隐藏，因此我的数据没有更新。我收到此错误：“运行时错误‘1004’：对象‘_Global’的方法‘工作表’失败”。
Can someone tell me how to work with an add-in which has a worksheet where the required calculations are performed?
有人可以告诉我如何使用具有工作表的加载项，在其中执行所需的计算吗？
The intriguing part is when I load the add-in after removing it from the list of add-ins in excel, it runs perfectly. But when I re-run the macro, the worksheet becomes hidden, so the same error appears. I am fairly new to VBA so any suggestions would be appreciated!
有趣的部分是当我从 excel 的加载项列表中删除加载项后加载加载项时，它运行完美。但是当我重新运行宏时，工作表变得隐藏，因此出现相同的错误。我对 VBA 相当陌生，因此任何建议将不胜感激！
Edit
编辑
Code:
代码：
Private Sub OptionOK_Click() 'On selecting OK from userform
Dim ws1 As Worksheet
Sheets("Sheet1").Visible = True
Set ws1 = Worksheets("Sheet1")
'User Form Validation
If Trim(Me.cboData.value) = "" Then
Me.cboData.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If
'copies data to given cell in excel
ws1.Range("A1").value = Me.cboData.value
'To copy selection from "Sheet1" into new workbook
Workbooks("myaddin.xlam").Sheets(1).Copy
End Sub
I get the error on ...> Sheets("Sheet1").Visible = True.
我在 ...> Sheets("Sheet1").Visible = True 上收到错误。
回答by VBAlearner
I just realized that I had to use "ThisWorkbook" in the add-in VBA code.
我刚刚意识到我必须在加载项 VBA 代码中使用“ThisWorkbook”。
Set ws1 = ThisWorkbook.Sheets ("Sheet1")
Set ws1 = ThisWorkbook.Sheets ("Sheet1")
VBA code within a workbook should use "ThisWorkbook" to reference to sheets or ranges inside the add-in.
工作簿中的 VBA 代码应使用“ThisWorkbook”来引用加载项内的工作表或范围。
回答by Portland Runner
If you know what sheet it is and you have access to the add-in code just make sure it's visible before the line that throws the error.
如果您知道它是什么工作表并且您可以访问加载项代码，只需确保它在引发错误的行之前可见。
Sheets("Sheet3").Visible = True
I suspect you have another problem though because you can still reference a hidden sheet in code.
我怀疑您还有另一个问题，因为您仍然可以在代码中引用隐藏的工作表。
Are you sure this line is correct:
你确定这一行是正确的：
Workbooks("myaddin.xlam").Sheets(1).Copy
Before you were referencing the name of the sheet now your referencing the position of the sheet in the workbook.
在引用工作表名称之前，现在引用工作簿中工作表的位置。
