CODEWITHSUNDEEP
vbaexcelmodule
Leocodetwist
Leocodetwist

Reputation: 61

run two module at excel start up

I have two modules that i would like to be executed at the open of the workbook what is the best way to do that. below are my module.

module 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

module 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

Upvotes: 0

Views: 52

Answers (1)

SierraOscar
SierraOscar

Reputation: 17647

The Workbook_Open() event has do be declared in the ThisWorkbook module, not a standard code module.

You can rename your current procedures and just call them both from the open event like so:

In Module1:

Sub Foo()
    MsgBox "First Message"
End Sub

In Module2:

Sub Bar()
    MsgBox "Second Message"
End Sub

Then in the ThisWorkbook module:

Public Sub Workbook_Open()
    Foo
    Bar
End Sub

Looking at your existing code, you just need to incorporate the second sub in your If block:

In the ThisWorkbook module:

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

and in 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

Upvotes: 2

Related Questions