Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time active Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time active Macro
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" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time active Macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time active Macro
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" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time active Macro
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time active Macro
-----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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help building a macro that displays date/time in active cell | Excel Discussion (Misc queries) | |||
Run a macro in active cell only. Want to place current date/time. | Excel Worksheet Functions | |||
converting a 4 figure number to time in active cell | New Users to Excel | |||
How can I make multiple hyperlinks active at one time? | Excel Discussion (Misc queries) | |||
MACRO with Active X | Excel Discussion (Misc queries) |