从窗体按钮调用Sub

Question

我试图通过用户表单按钮从当前工作表中调用一个sub，但出现以下错误；'编译错误：子或函数未定义"。

这是代码

Private Sub form_addDate_clickSubmit_Click()
    Dim WKB As Workbook
    Dim SHT_EDIT As Worksheet
    Set WKB = ActiveWorkbook
    Set SHT_EDIT = WKB.Sheets("EDIT")
    Call loadData
    'Tried various other ways such as SHT_EDIT.loadData
End Sub

loadData子项是公共的。

感谢

Answer 1

您的Sub loadData可能在图纸模块中。

您可以用[SHT_EDIT].loadData调用它，假设工作表的CodeName是SHT_EDIT(代码名称是VBA工程资源管理器中显示的名称，而不是Excel中工作表的名称。

一个更好的解决方案是将Sub移到一个常规模块中。

Answer 2

以下是我如何在工作表上编写命令按钮的代码：

Option Explicit
Public WS As Worksheet     ' global Worksheet object
Private Sub InsertNextWeek_Button1_Click()
'Button to call the Insert Next Week subprocedure
  'Save the current active worksheet
  Set WS = ThisWorkbook.ActiveSheet
  'Run procedure to Insert 7 new rows for Next Week
  Call Insert_NextWeek
  'Make this worksheet is left as the active worksheet
  WS.Activate
  'clear worksheet variable
  Set WS = Nothing
End Sub
Private Sub PublishRunStats_Button6_Click()
'Button to call the Publish RunStats subprocedure
  'Save the current active worksheet
  Set WS = ThisWorkbook.ActiveSheet
  'Run procedure to Save the Run Stats worksheet to a new workbook
  Call Publish_RunStats_Sheet
  'Make this worksheet is left as the active worksheet
  WS.Activate
  'clear worksheet variable
  Set WS = Nothing
End Sub

被调用的子过程在同一工作簿的模块1中被编码为Public
在每个命令按钮子过程中都使用全局WS对象，以确保我的显示焦点最终回到与刚才单击的命令按钮相同的工作表上。