PDA

View Full Version : To Trigger an Event based on Certain Time and Certain Date



Ted608
04-11-2015, 03:21 PM
Hello,

I have 2 separate sub routines that I would like to call upon (automatically without any user intervention):


when 5 pm is reached
when end of month is reached.


How would I go about doing it? (Preferably in VBA for Excel)
Much appreciation for any help!

Ted

Aussiebear
04-11-2015, 05:44 PM
Where do we find the values to trigger the subs?

Ted608
04-11-2015, 07:35 PM
Hello Aussiebear and thank you for your quick response!

I have a simple book keeping project: "Bookkeeping" in which I have 2 subs already written as:


1st - Sub move_Data_to_Daily_Page in which I have programmed as a button and I have to press it in my Excel program every weekday after 5 pm to move the data (range A2:R25 to range A100:R25) from my current_Working_Page into my daily_Page for book keeping.
2nd - Sub move_Data_to_Monthly_Page in which I have programmed as a 2nd button and I have to press it in my Excel program every End of Month to move the data (range A2:R25 to range A5000:R25) from the daily_Page into my monthly _Page for book keeping.


I am now looking to write a code to do those 2 actions above automatically. There are no other data or values. I am thinking of using the computer clock and calendar to sync the above actions. Are they possible?
Thank you very much for your help.

Ted

Aussiebear
04-12-2015, 12:24 AM
Still don't have all the information to be clear about this but for the first issue, you could try:

If the workbook is always OPEN after 5pm


Private Sub Workbook_OpenAfter5()
Application.OnTime TimeValue ("17:00:00"), "move_Data_to_Daily_Page"
End Sub


or if the workbook is NOT OPEN after 5pm


Private Sub Workbook_NotOpenAfter5()
If TimeValue < ("17:00:00") Then
exit Sub
Else
Application.Run (" insert full path name of your macro")
End Sub


In both cases I am assuming there is no prior data in the paste range.

Now in relation to the 2nd issue.... Hopefully it was just a typo but normally a multi column, multi row range has an Upper Left, Lower Right co-ordinate.

Ted608
04-12-2015, 12:07 PM
Hi Aussiebear,

Thanks again for your quick response!


Still don't have all the information to be clear about this but for the first issue, you could try:

The Excel is open 24hrs and I will try your suggestion right away. Thank you!


Now in relation to the 2nd issue.... Hopefully it was just a typo but normally a multi column, multi row range has an Upper Left, Lower Right co-ordinate.

You are right! It should be: range A2:R25 to range A5000:R5000.


In both cases I am assuming there is no prior data in the paste range.

The data will be pasted into its proper place (empty rows & columns).

The Sub move_Data_to_Daily_Page & Sub move_Data_to_Monthly_Page which I have programmed as 2 separate buttons on my excel sheet are working as intended.

Again, much appreciation for your much needed help!

Ted.

Ted608
04-12-2015, 02:12 PM
Hi Aussiebear,

After I adjusted the time (near present time) in order to test & run the suggested piece of code below:


Private Sub Workbook_OpenAfter5()Application.OnTime TimeValue ("14:03:00"), "move_Data_to_Daily_Page"
End Sub

and when the time has passed the set time, ie. "14:04:00", I got no response.

All the subs are written in the same module & workbook.

I am wondering do I have to "enable" anything to make the OnTime Method work?
Or do you have any other suggestions to where I should look for to make it work because the Sub move_Data_to_Daily_Page is working alright through the button which I have to click manually.

Thanks Aussiebear.

Ted

Aussiebear
04-12-2015, 04:04 PM
I would place the new sub into the "This Workbook", and check that I have the name of the sub correctly

Aussiebear
04-12-2015, 06:40 PM
You are right! It should be: range A2:R25 to range A5000:R5000.


Multi Column, Multi Row to Single row?

Ted608
04-12-2015, 09:53 PM
Multi Column, Multi Row to Single row?

My data format contains 2 lines & up until now there is no corruption upon the copying & pasting process. Thanks for your suggestion, Aussiebear.

Ted608
04-12-2015, 10:10 PM
I would place the new sub into the "This Workbook", and check that I have the name of the sub correctly
I don't know how to place the new sub into the "This Workbook" but I did placed it in the new module & tried it but still nothing happens. Would you mind helping a VBA newbie by being more direct & specific. Thanks for being so patient Aussiebear!

Aussiebear
04-13-2015, 03:10 AM
When you open the VBE, towards the upper left you will see the section which starts with VBA Project ( name of your file.xlsm). Underneath that is the Microsoft Exc el Objects listing which will include The sheet names within your workbook and also include the ThisWorkbook object. If you have written sections of code to a particular sheet then those modules will reside within that sheet as objects of that sheet. If you have written sections of code as individual modules they will be displayed under the Modules section. Remember where you place sections of code effects how the workbook reacts to them. Simply highlight your code modules and drag them to the ThisWorkbook object. Save the new setup and then try to run the code.

Ted608
04-13-2015, 04:44 AM
Hi Aussiebear,

Thanks for your response!

With your direction, I found the "ThisWorkbook" under the Microsoft Excel Objects ....

I wrote the code in individual Module and after I highlighted it, the Editor did not allow me to drag and drop the module into the "ThisWorkbook" as you've suggested. (I did close the module which contains the code).

Any other suggestions that I could try, Aussibear?

mperrah
04-14-2015, 09:13 PM
If the code is in a module just highlight the entire sub form the module
(click anywhere in the sub and use ctrl+a to select all)
and ctrl+c to copy
then double click the "this workbook" under sheets
then ctrl+v to paste
As I think aussiebear was trying to bring your attention,
be aware of the name in the top right corner of the thisworkbook sub area.
There is a drop down that has other events associated with the workbook.
like on open, on selection change. Mr bear's sub (I believe) is specific to the events.
hope this helps
mark

Ted608
04-14-2015, 10:44 PM
Hi mperrah,

Thanks for your response.



If the code is in a module just highlight the entire sub form the module
(click anywhere in the sub and use ctrl+a to select all)
and ctrl+c to copy
then double click the "this workbook" under sheets
then ctrl+v to paste

If my "ThisWorkbook" Sub has:


Sub clearAllLinks() Call Sheet1.clearLinks
End Sub

then where should I paste the copied code to? Thanks.

mperrah
04-14-2015, 11:05 PM
That sub could go in a module or the thisworkbook code depending on purpose.
the thing to keep in mind is the event that you want to start that sub.
if it's a workbook or worksheet event, the 'thisworkbook' area makes sense
or if it is a user interaction required, a module makes sense.
for example, if you want the sub to run on worksheet load or selection change- thisworkbook.
if you want the user to enter data, click a cell or button or reply to a message/input box- a module

Ted608
04-15-2015, 12:06 AM
Hi mperrah,

Thanks for your quick reply.

Even though I have not got it working but your above explanation has helped me to understand a bit more about where to place the subs & why there are subs on the modules & subs on the worksheet,... which I have not be able to see & understand why until now....

Aussiebear
04-15-2015, 12:20 AM
Mr bear's sub (I believe) is specific to the events.
hope this helps


Next one of you whippersnappers calls me "Mr", it'll be a carton of rum between us and then a duel with ostrich feathers at 15 paces......

mperrah
04-15-2015, 07:34 AM
Ouch, meant no disrespect Aussiebear (its Bob right?).
I've been a member for ten years and had the honor of being help by you many times.
Was merely going for acknowledging your profound influence in my life.
although I guess I could have taking Bill Murray's' advice,
"Calling me mr is like putting an elevator in an outhouse, kind of unnecessary".
(lol, you are not an outhouse)
:beerchug:
-mark

Ted608
04-15-2015, 09:58 AM
Hi Aussibear,


it'll be a carton of rum between us and then a duel with ostrich feathers at 15 paces......

Even though I have no clue what this is about or its meaning because English is my 2nd language but reading the above mperrah comment I am starting to have some idea ....

Thank you for being so kind & helpful to me and others! I hope that I did not say anything that offends you, if I did may I ask for your apology.

Much respected, Mr. Aussibear

Ted

mperrah
04-16-2015, 08:10 AM
you must like rum and feathers lol
go get em Aussiebear!
i got your back :rotlaugh: