Shutdown Macro

I have an auto shutdown macro (recommended on here ) that runs after 15 mins and closes the sheet if no info has been added.

It opens a new workbook and creates a message to tell the user whats happened.

It is working properly except it, on occasion, after the worksheet has closed seems to randomly reopen the sheet and perform the closedown again.

Anyone have any ideas why this may happen?

Thanks in advance.

Originally Posted by suss-hmfc

I have an auto shutdown macro (recommended on here ) that runs after 15 mins and closes the sheet if no info has been added.

It opens a new workbook and creates a message to tell the user whats happened.

It is working properly except it, on occasion, after the worksheet has closed seems to randomly reopen the sheet and perform the closedown again.

Anyone have any ideas why this may happen?

Thanks in advance.

It would be better if you could post the macro here so that we can take a look. The terminology is a little confusing. You can close a workbook but not a sheet. I guess that's what you mean, but I am guessing.

Since the macro can only run when the workbook is open in memory, it seems unlikely that the workbook has indeed been closed. Or perhaps it's not the macro in the workbook you've closed that's running, but one in say your Personal Workbook or perhaps some other.

Rgds

Public close Down Module

Public CloseDownTime As Variant

Public Sub ResetTimer()
On Error Resume Next
If Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime, Procedure:="CloseDownFile", Schedule:=False
CloseDownTime = Now + TimeValue("00:11:00") ' hh:mm:ss
Application.OnTime CloseDownTime, "CloseDownFile"
End Sub

Public Sub CloseDownFile()
On Error Resume Next
Disable.Workbook_BeforeSave

Call newsheet
ThisWorkbook.Close SaveChanges:=True
End Sub

Standard Module

Sub newsheet()
'
' newsheet Macro
' Macro recorded 22/04/2008 by SL280W - Steven Somerville - 52886
'

'
    Workbooks.Add
    Cells.Select
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("j20").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 26
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 2
    End With
    Range("j20").Select
    ActiveCell.FormulaR1C1 = _
        "ASC/Complaint sheet closed due to file inactivity threshold being reached."
    Range("j20").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With

End Sub

From This Workbook

Option Explicit

Dim Msg As Long

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
ResetTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ResetTimer
End Sub

Sorry yes i meant close down the workbook.

It seems to re open and then close the sheet again running the newsheet macro and leaving the newly created sheet (as well as any previously generated) I think its re opening and closing in reference to the shutdown clock, i.e. if you have it set to 11 mins, 11 mins after closing it opens, closes and creates another newsheet (from the newsheet macro)

thanks

Originally Posted by mudraker

Macro set to run at a future time
The workbook containg the macro is closed
The instance of Excel that was used to setup the future macro run time is still open

With the above conditions when the alloted macro run time is reached Excel will open the closed workbook & run the macro

Any idea how to stop this?

Should

There another way to open excel.

the IT dept does not have excel on the c drives so cant access the path?