Time trigger Macro

[Rinconpaul]

Believe it or not the question I'm about to ask is very difficult to nail a direct answer to? I've got a macro called Copy_Race:
#Sub Copy_Race()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("BOT")
Set pasteSheet = Worksheets("Sheet1")

copySheet.Range("A2:R24").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub#
It is currently triggered manually with a button on sheet "BOT". On this sheet, I've got this formula, which extracts the Race Start time:
=TIME(MID(A2,FIND(":",A2)-2,2),MID(A2,FIND(":",A2)+1,2),0)

Let's say it's showing 10:06 AM. I want to automatically enact the macro Copy_Race at this time, or any other time displayed as each race changes. How can I do this?
Cheers

[Rinconpaul]

To further elaborate, on sheet 'BOT' I've added a function: =NOW() in cell 'A29' .
In the VBA index in Objects Sheet2(GRUSS) I've added this code:

#
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With ThisWorkbook
If .Sheets("GRUSS").Range("B28").Value <> 1 Then
.Sheets("GRUSS").Range("B29").Value = 0
End If
If .Sheets("GRUSS").Range("B28").Value = 1 And .Sheets("GRUSS").Range("B29").Value <> 1 Then
Copy_Race
.Sheets("GRUSS").Range("B29").Value = 1
End If
Application.EnableEvents = True
End With
End Sub

On sheet 'GRUSS' there's a formula in cell B28 : =IF(BOT!A30="YES",1,0)

I'm thinking that the two cells on sheet 'BOT' with the time, one derived by a formula, the other by NOW aren't recognising each other format wise, even though they look the same text wise?

Any ideas?

[Captain Sensible]

You have to remember excel treats time as a decimal value such as 0.00055566 but obviously to make life easier we can set a cell to display it in an easier to read format like time i.e. 00:00:48. Your formula =TIME(MID(A2,FIND(":",A2)-2,2),MID(A2,FIND(":",A2)+1,2),0) will extract the time in the correct format because you've used TIME.

Not sure where you're having problems as the code you stuck up doesn't seem to use any timing in it just If .Sheets("GRUSS").Range("B28").Value = 1 And .Sheets("GRUSS").Range("B29").Value <> 1 Then I've no idea what generates those B28 B29 values.

It may be you're trying to be too precise when catching times because 0.00055566 and 0.00055568 would both show as 00:00:48 when formatted to time, try to use ranges when trying to trigger macros from static times as even things like a slow refresh could mean you miss the exact time. i.e. >=TIMEVALUE("00:00:47") and <=TIMEVALUE("00:00:49")

[Rinconpaul]

The Worksheet_Change is looking at two cells for the correct sequence of 0 & 1, which is generated by the two times matching. They aren't talking to each other as they've never turned "YES"! So the code will probably work, if the YES signal could be generated? One cell is formatted Custom h:mm AM/PM and the NOW() is formatted Time 1:30 PM. You'd think they'd be the same? You might be right about the range idea, I'll play with it some more.

Cheers anyway, you're a great asset to the forum.

[Rinconpaul]

I have sorted it! To benefit other forumites, I've attached a basic shell. The text in red font?.....DON'T mess with it. This workbook will copy and paste to Sheet1, the current prices, calculations, bet instructions etc, that you configure on sheet 'BOT' at the official start time. Just set up your races for the day on auto to appear say 1 minute before start. Very useful tool, and devilishly difficult to create. Maybe not for some, and I'm sure it can be improved somewhat? If it can be, please post.

Whoops, I can't attach the file: "The extension xlsm is not allowed" How do I get around that, as the code is what it's all about??

[Captain Sensible]

Good to see you've sorted it.

Whoops, I can't attach the file: "The extension xlsm is not allowed" How do I get around that, as the code is what it's all about??

xlsm files contain macros so I doubt you'll find any forums that allow them to be posted simply because it's not that hard to write malicious vba code that'll run when the file's opened. You either have to host it on one of the filesharing sites and post a link or just post up the code and people can make their own amendments or sheets if they want to.

Code: Select all

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rowFindLast As Long
Static MyMarket As Variant
Static SwitchMarket As Variant
Dim i As Long

    If Target.Columns.Count <> 16 Then Exit Sub

etc

 

[Rinconpaul]

Seems strange, as BetAngel, Cymatic all have a forum board for spreadsheets, many containing vba code. The trouble is posting code get's lost in the translation so often, especially if you don't know your way around excel vba? Ahh well, I tried!

[Captain Sensible]

Dunno about Cymatic but the Bet Angel forum doesn't allow you to post xlsm files either. It's just too risky to allow users to post macro enabled files as the VBA code to wipe your PC is very easy to find and add to a spreadsheet.