Activation at a certain time

[kulmjord]

Hey guys,

My code currently activates on a specific day but I was wondering if I can also get it to activate at in between specific times as well? The times a need it to activate is in between 12pm and 1pm the code is bellow.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Weekday(Date) = 6 And Time("12:00:00") Then
fn = InputBox("Date", "Input the date", Date)
If fn = vbNullString Then
Cancel = True
Exit Sub
End If
ChDir "G:\CHPP\METALLURGY\Reports\2017\CHPP Summary\Weekly"
    ActiveWorkbook.SaveAs Filename:= _
        "G:\CHPP\METALLURGY\Reports\2017\CHPP Summary\Weekly\01 January17" & fn & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End If
End Sub

Any help is appreciated,
Kulmjord.

 

[Veritan]

Depending on what you're going for, there's a couple ways to accomplish this. The Application.OnTime method will allow you to schedule an event to occur at a specific time. So this code below will run a sub named "SaveWB" at exactly 12 noon. Note that you will likely either need to make the workbook you have this code in a trusted document (recommended) or will need to lower your security settings to allow macros to run all the time (not recommended).

Private Sub Workbook_Open()
[COLOR=#008000]    'The "SaveWB" below is the name of the sub that you want to run, change it as needed.[/COLOR]
    Application.OnTime TimeValue("12:00:00"), "SaveWB"
End Sub

Another option is to use TimeValue in an If statement, like this:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
    If Weekday(Date) = 6 And TimeValue(Now) >= 12 / 24 And TimeValue(Now) <= 13 / 24 Then Call SaveWB
End Sub

Note that TimeValue will return a decimal value between 0 and 1, so noon is the equivalent of 12/24, or 0.5, and 1 PM is the equivalent of 13/24, or 0.5416667. The code above will activate when a user attempts to close the sheet on Friday between noon and 1 PM. See if either of these methods works for you.