Msg Box Timer
 

Hello. I have a workbook with a Workbook_Open event in it that calls a main
sub in the workbook. I am scheduling this code to run via the Windows\Tasks
method. But, I would also like to manually open the file to perform work on
it without the code executing upon opening. So I was thinking if there was
a popup upon opening that would count down 30 seconds, with the "cancel
event" button on it. So I could open the file and hit cancel and the code
would not be called. If the popup times out 30 seconds, the sub would then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

since you schedule it to run why not check the time and it it isn't the
scheduled time, then exit.

Private Sub Workbook_Open()
if time < TimeValue("10:00 PM") or time TimeValue("11:00 PM") then
exit sub
end if
' current code
End Sub

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hello. I have a workbook with a Workbook_Open event in it that calls a
main
sub in the workbook. I am scheduling this code to run via the
Windows\Tasks
method. But, I would also like to manually open the file to perform work
on
it without the code executing upon opening. So I was thinking if there
was
a popup upon opening that would count down 30 seconds, with the "cancel
event" button on it. So I could open the file and hit cancel and the code
would not be called. If the popup times out 30 seconds, the sub would
then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

Hi Tom,

Hadn't thought of that. Does it make a huge difference if the job is
scheduled to run only once a week?

"Tom Ogilvy" wrote in message
...
since you schedule it to run why not check the time and it it isn't the
scheduled time, then exit.

Private Sub Workbook_Open()
if time < TimeValue("10:00 PM") or time TimeValue("11:00 PM") then
exit sub
end if
' current code
End Sub

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hello. I have a workbook with a Workbook_Open event in it that calls a
main
sub in the workbook. I am scheduling this code to run via the
Windows\Tasks
method. But, I would also like to manually open the file to perform
work
on
it without the code executing upon opening. So I was thinking if there
was
a popup upon opening that would count down 30 seconds, with the "cancel
event" button on it. So I could open the file and hit cancel and the
code
would not be called. If the popup times out 30 seconds, the sub would
then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

Not unless you will be editing it during its normal run time. If that is
the case, then you could also check the day of the week.

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hi Tom,

Hadn't thought of that. Does it make a huge difference if the job is
scheduled to run only once a week?

"Tom Ogilvy" wrote in message
...
since you schedule it to run why not check the time and it it isn't the
scheduled time, then exit.

Private Sub Workbook_Open()
if time < TimeValue("10:00 PM") or time TimeValue("11:00 PM") then
exit sub
end if
' current code
End Sub

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hello. I have a workbook with a Workbook_Open event in it that calls
a
main
sub in the workbook. I am scheduling this code to run via the
Windows\Tasks
method. But, I would also like to manually open the file to perform
work
on
it without the code executing upon opening. So I was thinking if
there
was
a popup upon opening that would count down 30 seconds, with the
"cancel
event" button on it. So I could open the file and hit cancel and the
code
would not be called. If the popup times out 30 seconds, the sub would
then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

An alternative to Tom's suggestion *might* be the application's
UserControl property. For more, check XL VBA help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello. I have a workbook with a Workbook_Open event in it that calls a main
sub in the workbook. I am scheduling this code to run via the Windows\Tasks
method. But, I would also like to manually open the file to perform work on
it without the code executing upon opening. So I was thinking if there was
a popup upon opening that would count down 30 seconds, with the "cancel
event" button on it. So I could open the file and hit cancel and the code
would not be called. If the popup times out 30 seconds, the sub would then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

to the OP. Is the application visible when it is run by the schedular. If
so, then I don't think UserControl will work. But I haven't tested it -
perhaps Tushar has.

--
Regards,
Tom Ogilvy

"Tushar Mehta" wrote in message
om...
An alternative to Tom's suggestion *might* be the application's
UserControl property. For more, check XL VBA help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello. I have a workbook with a Workbook_Open event in it that calls a
main
sub in the workbook. I am scheduling this code to run via the
Windows\Tasks
method. But, I would also like to manually open the file to perform
work on
it without the code executing upon opening. So I was thinking if there
was
a popup upon opening that would count down 30 seconds, with the "cancel
event" button on it. So I could open the file and hit cancel and the
code
would not be called. If the popup times out 30 seconds, the sub would
then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

Tom,

Unfortunately it is scheduled to run at 5pm Saturday. So it is quite
probable that the file will need to be edited around that time on workdays.
So, will this work? Thanks for your help!

Private Sub Workbook_Open()
if Weekday(Date, vbSaturday) < 1 then
if time < TimeValue("10:00 PM") or time TimeValue("11:00 PM") then
exit sub
end if
' current code
end if
End Sub

"Tom Ogilvy" wrote in message
...
Not unless you will be editing it during its normal run time. If that is
the case, then you could also check the day of the week.

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hi Tom,

Hadn't thought of that. Does it make a huge difference if the job is
scheduled to run only once a week?

"Tom Ogilvy" wrote in message
...
since you schedule it to run why not check the time and it it isn't
the
scheduled time, then exit.

Private Sub Workbook_Open()
if time < TimeValue("10:00 PM") or time TimeValue("11:00 PM") then
exit sub
end if
' current code
End Sub

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hello. I have a workbook with a Workbook_Open event in it that
calls
a
main
sub in the workbook. I am scheduling this code to run via the
Windows\Tasks
method. But, I would also like to manually open the file to perform
work
on
it without the code executing upon opening. So I was thinking if
there
was
a popup upon opening that would count down 30 seconds, with the
"cancel
event" button on it. So I could open the file and hit cancel and
the
code
would not be called. If the popup times out 30 seconds, the sub
would
then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

Maybe I am missing something here...
Pressing the Shift key when clicking the Enable Macros button
disables the Open event.

Jim Cone
San Francisco, USA


"Steph" wrote in message
...
Hello. I have a workbook with a Workbook_Open event in it that calls a main
sub in the workbook. I am scheduling this code to run via the Windows\Tasks
method. But, I would also like to manually open the file to perform work on
it without the code executing upon opening. So I was thinking if there was
a popup upon opening that would count down 30 seconds, with the "cancel
event" button on it. So I could open the file and hit cancel and the code
would not be called. If the popup times out 30 seconds, the sub would then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

Hi Tom,

What I tested some time ago was a VBS script that ran XL and opened a
particular workbook. The workbook had a _open event procedure
conditioned on the UserControl property.

I don't know what will happen if XL is opened directly by the Windows
scheduler. Hence the use of *might* in my post. ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
to the OP. Is the application visible when it is run by the schedular. If
so, then I don't think UserControl will work. But I haven't tested it -
perhaps Tushar has.

Msg Box Timer
 

Hi, you asked:

Unfortunately it is scheduled to run at 5pm Saturday. So it is quite
probable that the file will need to be edited around that time on
workdays.
So, will this work?

Tom's suggestion should work for you. Here is my take on what Tom
suggested, "then you could also check the day of the week":

If WeekdayName(Weekday(Now)) = "Saturday" _
And Time TimeValue("5:00 PM") Then
'Run the report
Else
'Don't run report
Exit Sub
End If

HTH--Lonnie M.

Msg Box Timer
 

Exactly Lonnie
I don't know how precise schedular is, but if after 4:30 on Saturday would
be good for considering it as the scheduled run, then I would modify to

If WeekdayName(Weekday(Now)) = "Saturday" _
And Time TimeValue("4:30 PM") Then
'Run the report
Else
'Don't run report
Exit Sub
End If

Make sure WeekdayName is supported in your version of Excel. I forget which
version it was introduced in, but it was later than xl97.

--
Regards,
Tom Ogilvy


"Lonnie M." wrote in message
oups.com...
Hi, you asked:

Unfortunately it is scheduled to run at 5pm Saturday. So it is quite
probable that the file will need to be edited around that time on
workdays.
So, will this work?

Tom's suggestion should work for you. Here is my take on what Tom
suggested, "then you could also check the day of the week":

If WeekdayName(Weekday(Now)) = "Saturday" _
And Time TimeValue("5:00 PM") Then
'Run the report
Else
'Don't run report
Exit Sub
End If

HTH--Lonnie M.

Msg Box Timer
 

Hard to say, but a side effect is that doing that disables all macros for
that workbook which might not be desirable.

--
Regards,
Tom Ogilvy

"Jim Cone" wrote in message
...
Maybe I am missing something here...
Pressing the Shift key when clicking the Enable Macros button
disables the Open event.

Jim Cone
San Francisco, USA


"Steph" wrote in message
...
Hello. I have a workbook with a Workbook_Open event in it that calls a
main
sub in the workbook. I am scheduling this code to run via the
Windows\Tasks
method. But, I would also like to manually open the file to perform
work on
it without the code executing upon opening. So I was thinking if there
was
a popup upon opening that would count down 30 seconds, with the "cancel
event" button on it. So I could open the file and hit cancel and the
code
would not be called. If the popup times out 30 seconds, the sub would
then
be called. Is this possible?? Thanks!!

Msg Box Timer
 

Tom,

On my Windows XP, xl2002 machine the
Workbook Open event does not fire, but other macros continue to work.

Regards,
Jim Cone
San Francisco, USA


"Tom Ogilvy" wrote in message
...
Hard to say, but a side effect is that doing that disables all macros for
that workbook which might not be desirable.

--
Regards,
Tom Ogilvy


"Jim Cone" wrote in message
...
Maybe I am missing something here...
Pressing the Shift key when clicking the Enable Macros button
disables the Open event.

Jim Cone
San Francisco, USA

Msg Box Timer
 

I stand corrected - guess I usually choose to disable macros when I open
that way. So perhaps your way is the best way, but certainly prone to
forgetting to do it.

--
Regards,
Tom Ogilvy

"Jim Cone" wrote in message
...
Tom,

On my Windows XP, xl2002 machine the
Workbook Open event does not fire, but other macros continue to work.

Regards,
Jim Cone
San Francisco, USA


"Tom Ogilvy" wrote in message
...
Hard to say, but a side effect is that doing that disables all macros
for
that workbook which might not be desirable.

--
Regards,
Tom Ogilvy


"Jim Cone" wrote in message
...
Maybe I am missing something here...
Pressing the Shift key when clicking the Enable Macros button
disables the Open event.

Jim Cone
San Francisco, USA