can you stop Workbook_Open opening on its own?

I have a workbook that has a 'Workbook_Open' macro in it. My understanding was this would only kick in when I open the Workbook, but, everytime I close the workbook, 30 secs or so later it opens [on its own] Can someone explain to me why this is happening and what I can do to stop it.

Thanks
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JagwarmanAuthor Commented:
Further to my above post it looks to me like the reason is, when I close the file, the VBA project remains in VBA editor because when I open excel, press F11, there it is, the VBA project from the file I closed.
0
Rory ArchibaldCommented:
Usually the only reason the workbook would reopen is that you have run some code using Application.Ontime and not cancelled it. As far as the project still appearing in the VBE is concerned, I have seen that with COM add-ins loaded (PowerPivot causes it for me).
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

JagwarmanAuthor Commented:
rorya, I do have these Subs so will this be causing it?

Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime setDate, "TimeUp", , False
End Sub
 
Private Sub Workbook_Open()

    UpdateTime
   
    WorkbookDate

End Sub
 
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    UpdateTime
   
   
   
End Sub
 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    On Error Resume Next
    Application.OnTime setDate, "TimeUp", , False
0
Rory ArchibaldCommented:
I'd need to see the rest of the code. Either something isn't being cancelled properly, or a routine you ran while testing hasn't been cancelled. Have you restarted Excel and tried the workbook again?
0
JagwarmanAuthor Commented:
rorya

The above and the sub below are in ThisWorkbook

Sub WorkbookDate()


    Dim Msg As String, Ans As Variant
     
    Msg = "Do you want to insert date??"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
         Sheets("Deliveries").Select
        Range("I4").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("I3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Case vbNo
GoTo Quit:
    End Select
     
Quit:

End Sub


The one below here is in Module 1

Option Explicit
Public setDate As Date
Sub UpdateTime()
    setDate = Now() + TimeValue("00:01:00")
    Application.OnTime setDate, "TimeUp"
End Sub
Sub TimeUp()
    ThisWorkbook.Worksheets("STATIC DATA").Range("R2") = Time
    UpdateTime
End Sub


Sub MyMacro()
    Time = Now + TimeValue("00:03:00")
    Application.OnTime Time, "MyMacro"
    If Time >= TimeValue("09:00:00 PM") Then
        Application.OnTime Time, "MyMacro", , False
        Exit Sub
    End If
     '....rest of my code
     
     MsgBox "Run it Now"
     
End Sub

and this is in Module 2

Public dTime As Date
Sub doMacro()
    MsgBox "Message"
   
    'restart the timer
    dTime = Now() + TimeSerial(0, 0, 10) ' time to wait (hh,mm,ss)
    Application.OnTime dTime, "doMacro"
   
End Sub

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

So, when it opens it puts in todays date in the Tab 'Deliveries' in cell I4

It also runs a clock and updates the time

Hope this helps
0
Rory ArchibaldCommented:
And have you restarted Excel?

There doesn't appear to be a cancel routine for the last two macros, but there's nothing that calls them automatically. However if you had run them yourself, they would reopen the workbook unless you had shut down and restarted Excel.
0
JagwarmanAuthor Commented:
how would I put in a cancel routine. I am fairly new at this game. I thought you couldn;t have 2 subs with the same name?
0
Rory ArchibaldCommented:
I am simply referring to a routine that uses Ontime with the last argument set to False to cancel a scheduled routine.

So, again, have you restarted Excel?
0
JagwarmanAuthor Commented:
morning rorya, yes I re-started Excel.

When I restart Excel and open the file the macro kicks in. When I close the file, it still re-opens.

Regards
0
Rory ArchibaldCommented:
OK - looked a bit closer at your code. You have two issues:
1. You are using the WindowActivate and WindowDeactivate events of the workbook, rather than the Activate and Deactivate events. The former two are triggered every time you switch sheets in the workbook, so you are scheduling the event twice every time it runs.
2. Once you correct that, you are scheduling the event in both the Workbook_Open and Workbook_Activate events, both of which fire when you first load the workbook. You don't need to schedule it in the workbook_open.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
rorya, Thanks for your help back in Oct I thought I had it sussed but I don't, although I am sure that if I fully understood your solution it would work. you said:
1. You are using the WindowActivate and WindowDeactivate events of the workbook, rather than the Activate and Deactivate events. The former two are triggered every time you switch sheets in the workbook, so you are scheduling the event twice every time it runs.
2. Once you correct that, you are scheduling the event in both the Workbook_Open and Workbook_Activate events, both of which fire when you first load the workbook. You don't need to schedule it in the workbook_open.

and this is what I have :

Option Explicit
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime setDate, "TimeUp", , False
End Sub
 
Private Sub Workbook_Open()

    UpdateTime
   
    WorkbookDate
End Sub
 
Private Sub Workbook_WindowActivate (ByVal Wn As Window)
    UpdateTime
   
   
   
End Sub
 
Private Sub Workbook_WindowDeactivate (ByVal Wn As Window)
    On Error Resume Next
    Application.OnTime setDate, "TimeUp", , False
End Sub

So are you saying I should change Workbook_WindowActivate  to Workbook_WindowActivateEvents etc

Sorry to trouble you but I am newish to all this and I am going round in circles.

Regards
John
0
Rory ArchibaldCommented:
No, the last two routines should become:
Private Sub Workbook_Activate()
    UpdateTime
End Sub

Private Sub Workbook_Deactivate()
    On Error Resume Next
    Application.OnTime setDate, "TimeUp", , False
End Sub

Open in new window

0
JagwarmanAuthor Commented:
Thanks rorya
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.