在Excel加载项中选择工作表以运行VBA宏 [英] Select worksheet in Excel add-in to run VBA macro

问题描述

我已经使用VBA创建了一个加载项，它是一个带有计算的工作簿。该加载项具有用户表单以从访问数据库提取相关信息并填充该工作簿。填写数据后，在Sheet1中执行计算。我需要将工作表Sheet1从加载项工作表粘贴到运行附加宏的新工作簿中。

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.

当我运行加载项时，工作表似乎是隐藏的，所以我的数据没有更新。我得到这个错误：
运行时错误'1004'：对象'_Global'失败的方法'工作表'。

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

有人可以告诉我如何使用一个加载项，其中包含执行所需计算的工作表？

Can someone tell me how to work with an add-in which has a worksheet where the required calculations are performed?

有趣的部分是当我从Excel中的加载项列表中删除加载项之后，它运行完美。但是当我重新运行宏时，工作表将被隐藏，所以出现相同的错误。我对VBA很新，所以任何建议将不胜感激！

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!

编辑

Edit

代码：

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 

我收到错误...>表格（Sheet1 ）.Visible = True。

I get the error on ...> Sheets("Sheet1").Visible = True.

推荐答案

我只是意识到我不得不在加载VBA代码中使用ThisWorkbook

I just realized that I had to use "ThisWorkbook" in the add-in VBA code.

设置ws1 = ThisWorkbook.Sheets（Sheet1）

VBA工作簿中的代码应使用ThisWorkbook来引用加载项中的工作表或范围。

VBA code within a workbook should use "ThisWorkbook" to reference to sheets or ranges inside the add-in.

这篇关于在Excel加载项中选择工作表以运行VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！