can you stop Workbook_Open opening on its own?

http://www.jkp-ads.com/articles/preventopenevent.asp

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.

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

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

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?

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

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.

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?

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?

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

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

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

Select allOpen in new window

Thanks rorya