在excel启动时运行两个模块

我有两个模块，我想在工作簿打开时执行它们，最好的方法是什么。下面是我的模块。

模块1

Public Sub workbook_open()

Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
    QuestionToMessageBox = "Do you Agree?"
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Do you agree with disclaimer")
    If YesOrNoAnswerToMessageBox = vbNo Then
         ActiveWorkbook.Close savechanges:=False
    Else
        MsgBox "Congratulations!"
    End If
End Sub

模块2

  Sub workbook_open()
     Dim Expired As Date
     Expired = "31 March 2016"
     If Now() < Expired Then
         Sheet1.Visible = True
         Sheet2.Visible = True
         Sheet3.Visible = True
         Sheet6.Visible = True
         Sheet7.Visible = True
         Sheet8.Visible = True
         Sheet9.Visible = True
         Sheet13.Visible = True
         Sheet5.Visible = True
         Sheet10.Visible = xlSheetHidden
         End If
     If Now() > Expired Then
         MsgBox "This file is no longer in use!"
         Sheet10.Visible = True
         Sheet1.Visible = xlSheetVeryHidden
         Sheet2.Visible = xlSheetVeryHidden
         Sheet3.Visible = xlSheetVeryHidden
         Sheet6.Visible = xlSheetVeryHidden
         Sheet7.Visible = xlSheetVeryHidden
         Sheet9.Visible = xlSheetVeryHidden
         Sheet13.Visible = xlSheetVeryHidden
         Sheet5.Visible = xlSheetVeryHidden
         Sheet8.Visible = xlSheetVeryHidden

     End If
End Sub

Workbook_Open()事件必须在ThisWorkbook模块中声明，而不是在标准代码模块中声明。

您可以重命名当前的过程，只需从打开的事件中调用它们，如下所示：

在Module1:中

Sub Foo()
    MsgBox "First Message"
End Sub

在Module2:中

Sub Bar()
    MsgBox "Second Message"
End Sub

然后在ThisWorkbook模块中：

Public Sub Workbook_Open()
    Foo
    Bar
End Sub

查看您现有的代码，您只需要在If块中包含第二个子：

在ThisWorkbook模块中：

Public Sub workbook_open()
Dim YesOrNoAnswerToMessageBox As String 
Dim QuestionToMessageBox As String
QuestionToMessageBox = "Do you Agree?"
YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Do you agree with disclaimer")
If YesOrNoAnswerToMessageBox = vbNo Then
     ActiveWorkbook.Close savechanges:=False
Else
    MsgBox "Congratulations!"
    OpeningProcedure '// <~~ Note this, to call the other sub
End If
End Sub

在Module1:中

 Sub OpeningProcedure()
 Dim Expired As Date Expired = "31 March 2016"
 If Now() < Expired Then
     Sheet1.Visible = True
     Sheet2.Visible = True
     Sheet3.Visible = True
     Sheet6.Visible = True
     Sheet7.Visible = True
     Sheet8.Visible = True
     Sheet9.Visible = True
     Sheet13.Visible = True
     Sheet5.Visible = True
     Sheet10.Visible = xlSheetHidden
     End If
 If Now() > Expired Then
     MsgBox "This file is no longer in use!"
     Sheet10.Visible = True
     Sheet1.Visible = xlSheetVeryHidden
     Sheet2.Visible = xlSheetVeryHidden
     Sheet3.Visible = xlSheetVeryHidden
     Sheet6.Visible = xlSheetVeryHidden
     Sheet7.Visible = xlSheetVeryHidden
     Sheet9.Visible = xlSheetVeryHidden
     Sheet13.Visible = xlSheetVeryHidden
     Sheet5.Visible = xlSheetVeryHidden
     Sheet8.Visible = xlSheetVeryHidden

 End If
End Sub

相关内容

最新更新

热门标签：