EXCEL 2003 MACRO Problem

[Neil Holden]

Hi all gurus please help.

I have a windows schelduler to open at a certain time and when the cells
have been updated the file closes.

The problems i'm having it the file stays open and i have to manually close
the open file on a weekly basis, the cells are not updated either?

Please see my code below:


Private Changed As Boolean
Private Sub Workbook_Open()
'start with the workbook showing unchanged
Changed = False
' create our shutdown timer
Application.OnTime Now + TimeValue("00:00:10"),
procedure:="ThisWorkbook.Auto_Close"
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
'if something is changed then restart the timer
Changed = True
End Sub
Private Sub Auto_Close()
'if no changes detected then save and close
If Changed = False Then
ThisWorkbook.Close SaveChanges:=True
End If
Changed = False
'cancel the timer
Call Application.OnTime(Now + TimeValue("00:00:15"),
"ThisWorkbook.Auto_Close")
End Sub

 

[Jan Karel Pieterse]

Hi Neil,

You cannot schedule a macro that is placed in the thisworkbook module.

Move the sub your ontime method needs to fire to a normal module.

Also: do not call that sub Auto_close, this is a reserved name for a macro that
starts when the workbook is closed.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

I have a windows schelduler to open at a certain time and when the cells
have been updated the file closes.

The problems i'm having it the file stays open and i have to manually close
the open file on a weekly basis, the cells are not updated either?

Please see my code below:


Private Changed As Boolean
Private Sub Workbook_Open()
'start with the workbook showing unchanged
Changed = False
' create our shutdown timer
Application.OnTime Now + TimeValue("00:00:10"),
procedure:="ThisWorkbook.Auto_Close"
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
'if something is changed then restart the timer
Changed = True
End Sub
Private Sub Auto_Close()
'if no changes detected then save and close
If Changed = False Then
ThisWorkbook.Close SaveChanges:=True
End If
Changed = False
'cancel the timer
Call Application.OnTime(Now + TimeValue("00:00:15"),
"ThisWorkbook.Auto_Close")
End Sub

-------------------------------------------------
==========
microsoft/public.excel.misc #546592, from Neil_Holden, 1879 chars, Wed, 17 Mar
2010 05:34:01 -0700
----------
Thread-Topic: EXCEL 2003 MACRO Problem
thread-index: AcrFzh9U9533cbqeRHGWgdaoJVZr7A==
X-WBNR-Posting-Host: 217.45.250.225
From: =?Utf-8?B?TmVpbCBIb2xkZW4=?= <[email protected]>
Subject: EXCEL 2003 MACRO Problem
Date: Wed, 17 Mar 2010 05:34:01 -0700
Lines: 36
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
Newsgroups: microsoft.public.excel.misc
NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGHUB02.phx.gbl
Xref: TK2MSFTNGP01.phx.gbl microsoft.public.excel.misc:903285
X-VA-Origin: Zcisinet:microsoft/public.excel.misc


Hi all gurus please help.

I have a windows schelduler to open at a certain time and when the cells
have been updated the file closes.

The problems i'm having it the file stays open and i have to manually close
the open file on a weekly basis, the cells are not updated either?

Please see my code below:


Private Changed As Boolean
Private Sub Workbook_Open()
'start with the workbook showing unchanged
Changed = False
' create our shutdown timer
Application.OnTime Now + TimeValue("00:00:10"),
procedure:="ThisWorkbook.Auto_Close"
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
'if something is changed then restart the timer
Changed = True
End Sub
Private Sub Auto_Close()
'if no changes detected then save and close
If Changed = False Then
ThisWorkbook.Close SaveChanges:=True
End If
Changed = False
'cancel the timer
Call Application.OnTime(Now + TimeValue("00:00:15"),
"ThisWorkbook.Auto_Close")
End Sub