Time active Macro

[Lorenzo]

I have what is probably a very small problem, but the solution to it
simply keeps on eluding me. I have a macro that runs a series of
operations on a sheet, that has to be run only for a certain time of
the day. As on a sheet I have in B21 the current time and in cell B22
the time the last update of the sheet was done, I was trying to figure
a way to have the macro run when the time in cell B21 reaches 07.30am
and have it stop when the time value in B22 is equal to or greater
than 10.30pm, to then start again at 7.30 the day after and so on. My
first thought was using an
If *value in cell B21 < 07.30* or *value in cell B22 > 22.30* then
*end the sub*
else *the rest of the macro code, which works perfectly*
Being a VB newbie, most of its' language still baffles me, as you may
have noticed.
I really hope you can help me, and I thank you for any help you might
be able to give. Lorenzo

 

[Henry]

Lorenzo,

Private Sub Halfhourly()
Do While DateTime.Time > ("07:29:00") And DateTime.Time < ("22:31:00")

If Minute.Time Mod 30 = 0 Then 'Is it 00 or 30 minutes past the hour
'*
'*
'Do your stuff
'*
'*
Application.Wait Time + TimeValue("00:01:00")
'Wait whilst time moves on to 01 or 31 minute past the hour
'You wont need this line if your "stuff" takes longer than 1 minute to run


End If

Loop
End Sub

HTH
Henry

 

[Lorenzo]

Henry, thanks a lot for the help you've given me. Still, I seem to
have a slight problem with your code. I didn't quite get what the

If Minute.Time Mod 30=0 Then

line is supposed to do. As an argument for Minute is mandatory, I have
to change the line, but I'm afraid I didn't really catch your hint. Do
you think you could please explain me better? Please?
Again, thank you very much for all the help you're giving me - you
have no idea how much it means to me. Thank you - Lorenzo

 

[Henry]

Lorenzo,

Sorry, I think it should read Minute(Time) Mod 30 = 0

what that line is doing is dividing the minutes part of the current time by
30 and looking at the remainder (Mod 30). The remainder will be 0 on the
hour and the half hour only.

HTH
Henry

 

[Lorenzo]

Henry, again thank for your help. Still, I'm afraid your macro has a
slight glitch, probably because I didn't quite explain myself. When
running the macro, the workbook keeps processing the macro without
allowing access to any of the sheets. As it is something to be largely
used around the office, I really need something that will run
"quietly" in the bckground, while still allowing people to use and
modufy the file. Any suggestions?
Thank you very much again for all your time - Lorenzo

 

[roger miller]

-----Original Message-----
I have what is probably a very small problem, but the solution to it
simply keeps on eluding me. I have a macro that runs a series of
operations on a sheet, that has to be run only for a certain time of
the day. As on a sheet I have in B21 the current time and in cell B22
the time the last update of the sheet was done, I was trying to figure
a way to have the macro run when the time in cell B21 reaches 07.30am
and have it stop when the time value in B22 is equal to or greater
than 10.30pm, to then start again at 7.30 the day after and so on. My
first thought was using an
If *value in cell B21 < 07.30* or *value in cell B22 > 22.30* then
*end the sub*
else *the rest of the macro code, which works perfectly*
Being a VB newbie, most of its' language still baffles me, as you may
have noticed.
I really hope you can help me, and I thank you for any help you might
be able to give. Lorenzo
.
Try using the Application OnTime function along w/

TimeValue("07:30"). I think if you read up on
the "OnTime" function you'll see how to start and stop
the procedure.