PDA

View Full Version : Solved: Adding Time Stipulation to VBA



jo15765
11-07-2011, 02:15 PM
I know I can use this code to add time constraints:


'Runs a macro at 4:30 PM
Application.OnTime TimeValue("16:30:00"), "Name_of_Macro"


And I know you can use this code to run specific code depending on the day:


' Dim sDate As String
' sDate = Format(Date, "ddd")
' If sDate = "Sun" Then
' Run "MacroName"
' Else
' If sDate = "Mon" Then
' Run "MacroName"
' End If
' End If


How can I combine the two and say something like:


' Dim sDate As String
' sDate = Format(Date, "ddd")
' If sDate = "Mon" and Windows.Time < ("12:00:00") Then
Run "MacroName"
' Else
' If sDate = "Mon" and Windows.Time > ("12:00:00") Then
' Run "MacroName"
' End If
' End If


I want to be able to have one button on a Excel UserForm, and be able to run two different macro's depending on what time it is. I don't want to use the OnTime value mentioned above because the time varies, but what never varies is that one macro needs to be run before lunch, and the other macro needs to be run after lunch. How can I code it to do the above?

mdmackillop
11-07-2011, 02:38 PM
Not far away from it
Try
Dim sDate As String
sDate = Format(Date, "ddd")
If sDate = "Mon" And Time < TimeValue("12:00:00") Then
Run "MacroName"
Else
If sDate = "Mon" And Time > TimeValue("12:00:00") Then
Run "MacroName"
End If
End If

jo15765
11-07-2011, 03:24 PM
That worked perfectly, thank yoU!!!