Event Trigger
 

I have a macro, that works, that I want to run on a certain day at a
certain time, but can't find a way to trigger it.
Tried
Application.OnTime
I have a Class Module with

Public WithEvents xlApp As Application

Private Sub Class_Initialize

Set XL.xlApp = Application

Module

Application.OnTime(9:00 AM),"Date_Lock", , = True

Sub Date_Lock()
ActiveSheet.Select
Range("C3:W384").Select
Range("C349").Activate
Selection.Locked = True
Selection.FormulaHidden = True
ActiveWorkbook.Save
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
End Sub

Date _Lock works when manually run, but won't trigger when I want?

Event Trigger
 

I don't see any reason to have the Class Module.

You have to run a macro with the Applicaton.OnTime command to schedule an
event. See Chip Pearson's page on application.OnTime

http://www.cpearson.com/excel/ontime.htm

When excel is closed, any ontime events are lost - your sample code doesn't
specify a date so it is unclear what a specific date and time means.

--
Regards,
Tom Ogilvy


"lobo" wrote in message
oups.com...
I have a macro, that works, that I want to run on a certain day at a
certain time, but can't find a way to trigger it.
Tried
Application.OnTime
I have a Class Module with

Public WithEvents xlApp As Application

Private Sub Class_Initialize

Set XL.xlApp = Application

Module

Application.OnTime(9:00 AM),"Date_Lock", , = True

Sub Date_Lock()
ActiveSheet.Select
Range("C3:W384").Select
Range("C349").Activate
Selection.Locked = True
Selection.FormulaHidden = True
ActiveWorkbook.Save
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
End Sub

Date _Lock works when manually run, but won't trigger when I want?

Event Trigger
 

I have a date in mind, but haven't worried about that.
I am not sure why I have the class module either, a book I have
suggested that I try having the trigger be the opening of the workbook.

*** Sent via Developersdex http://www.developersdex.com ***

Event Trigger
 

If you want to schedule it whenever any workbook is opened, then you would
make it an application level event and you would need a class module to
"instantiate" workbook level events. If it is for a specific workbook when
it is opened, then you would only need to use the workbook_Open event for
that workbook.

If you want the event to occur if Excel is open on Jan 15, 2006 at 9 AM
regardless of what workbook is open, then you would probably use application
level events. http://www.cpearson.com/excel/appevent.htm
However, this would depend on Excel being open.

If you want to be sure it runs, then you might put it in the workbook open
event for a single workbook and then use Windows scheduler to open excel and
that workbook on the date and time in question.

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"Martin Bentler" wrote in message
...


I have a date in mind, but haven't worried about that.
I am not sure why I have the class module either, a book I have
suggested that I try having the trigger be the opening of the workbook.

*** Sent via Developersdex http://www.developersdex.com ***

Event Trigger
 

Ok, I only one a particular workbook to stop working completely, never
to work again, on January 9, 2006 at 9:00, so I don't need the class
module.
OR it could stop working as soon as it is opened on Jan 9, 06. Either is
good for me.

*** Sent via Developersdex http://www.developersdex.com ***

Event Trigger
 

In the workbook_open event of the workbook you could have code like

In the ThisWorkbook Code Module:

Private Sub Workbook_Open()
if date DateValue("Jan 9, 2006") then
thisworkbook.close
end if
End sub

However, just disabling macros upon opening or changing the system clock
will defeat this protection mechanism - besides the fact that the user can
break into your code and remove it.

--
Regards,
Tom Ogilvy


"Martin Bentler" wrote in message
...


Ok, I only one a particular workbook to stop working completely, never
to work again, on January 9, 2006 at 9:00, so I don't need the class
module.
OR it could stop working as soon as it is opened on Jan 9, 06. Either is
good for me.

*** Sent via Developersdex http://www.developersdex.com ***