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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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 ***

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" <[email protected]> wrote in message
news:OyF6%[email protected]...
>
>
> 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 ***

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 ***

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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 ***