View Full Version : Solved: Allowing macro beetwen 4 - 5 PM
slamet Harto
06-27-2008, 05:29 AM
Hi guys,
I need your help to fix the following my VBA Code:
If function is allowed between 4.00 PM until 5 PM. However, the code doesn't work. My PC is setting as Indonesian in Language option and Bangkok-Hanoi-Jakarta for date and time.
Any help and suggestion, would be appreciate
Sub Submission()
Dim CurrDate As Date
Dim CrTime As String
CurrDate = Format(Now, "Short Time")
If CurrDate <= #3:59:00 PM# And CurrDate >= #5:01:00 PM# Then
MsgBox "You can't allowed at this time," & vbCr _
& "Please try between 4 PM until 5 PM !!", vbOKOnly, "System Alert.."
Else
Call SaveUserButton
End If
End Sub
marshybid
06-27-2008, 05:44 AM
Hi guys,
I need your help to fix the following my VBA Code:
If function is allowed between 4.00 PM until 5 PM. However, the code doesn't work. My PC is setting as Indonesian in Language option and Bangkok-Hanoi-Jakarta for date and time.
Any help and suggestion, would be appreciate
Sub Submission()
Dim CurrDate As Date
Dim CrTime As String
CurrDate = Format(Now, "Short Time")
If CurrDate <= #3:59:00 PM# And CurrDate >= #5:01:00 PM# Then
MsgBox "You can't allowed at this time," & vbCr _
& "Please try between 4 PM until 5 PM !!", vbOKOnly, "System Alert.."
Else
Call SaveUserButton
End If
End Sub
See below, works fine for me
Sub Submission()
Dim CurrTime As String
CurrTime = format(Now, "h:mm:ss")
If CurrTime >= #3:59:00 PM# And CurrTime <= #5:01:00 PM# Then
MsgBox "You can't allowed at this time," & vbCr _
& "Please try between 4 PM until 5 PM !!", vbOKOnly, "System Alert.."
Else
Call SaveUserButton
End If
End Sub
Hopefully it will work for you
Marshybid
Bob Phillips
06-27-2008, 05:48 AM
Suggest a small change
If CurrTime > #3:59:59 PM# And CurrTime < #5:00:01 PM# Then
JimmyTheHand
06-27-2008, 05:48 AM
If CurrDate <= #3:59:00 PM# Or CurrDate >= #5:01:00 PM# Then
EDIT
Bob is right, the timestamps can be refined by adding seconds.
So it will be
If CurrDate <= #3:59:59 PM# Or CurrDate >= #5:00:01 PM# Then
Jimmy
marshybid
06-27-2008, 05:51 AM
Suggest a small change
If CurrTime > #3:59:59 PM# And CurrTime < #5:00:01 PM# Then
Good point xld. I spotted that the OP had the <> the wrong way round in original code, but by removing the = the time matches the message box criteria... between 4PM and 5PM
Marshybid :rotlaugh:
marshybid
06-27-2008, 05:52 AM
If CurrDate <= #3:59:00 PM# Or CurrDate >= #5:01:00 PM# Then
EDIT
Bob is right, the timestamps can be refined by adding seconds.
So it will be
If CurrDate <= #3:59:59 PM# Or CurrDate >= #5:00:01 PM# Then
Jimmy
Hi Jimmy, why OR?? The And seemed to work for me when I changed the times to test??
Marshybid :hi:
JimmyTheHand
06-27-2008, 05:56 AM
Because the OP wants to call SaveUserButton between 4 and 5 PM, and he wants to display the warning outside of this time interval. I think your solution does the opposite. Maybe I'm wrong. I didn't test it ;)
Jimmy
Simon Lloyd
06-27-2008, 05:56 AM
Sub Submission1()
If Time <= "15:59:00" Or Time >= "17:01:00" Then
MsgBox "You can't allowed at this time," & vbCr _
& "Please try between 4 PM until 5 PM !!", vbOKOnly, "System Alert.."
Else
Call SaveUserButton
End If
End Sub
marshybid
06-27-2008, 05:58 AM
Sub Submission1()
If Time <= "15:59:00" Or Time >= "17:01:00" Then
MsgBox "You can't allowed at this time," & vbCr _
& "Please try between 4 PM until 5 PM !!", vbOKOnly, "System Alert.."
Else
Call SaveUserButton
End If
End Sub
Hi Simon,
I tried Time in my first attempt at this one and when I ran it it returned an error, variable/object not defined???
Marshybid
marshybid
06-27-2008, 05:59 AM
Because the OP wants to call SaveUserButton between 4 and 5 PM, and he wants to display the warning outside of this time interval. I think your solution does the opposite. Maybe I'm wrong. I didn't test it ;)
Jimmy
Your right Jimmy, I rad it the other way round :thumb
Marshybid
Simon Lloyd
06-27-2008, 06:12 AM
It works perfect for me, obviously i changed the Call SaveUSerButton for a msgbox but it worked fine!
marshybid
06-27-2008, 06:57 AM
It works perfect for me, obviously i changed the Call SaveUSerButton for a msgbox but it worked fine!
Ditto, changed SaveUserButton to a msgbox that read 'working'
Can't seem to get Time recognised though?? :banghead:
Marshybid
Bob Phillips
06-27-2008, 07:37 AM
Ditto, changed SaveUserButton to a msgbox that read 'working'
Can't seem to get Time recognised though?? :banghead:
Marshybid
Select the code that says Time, Shift-F2 and see if it takes you anywhere.
Simon Lloyd
06-27-2008, 07:38 AM
you may be missing a library reference!
Simon Lloyd
06-27-2008, 07:39 AM
oops sorry didn't see your toes there Bob!!! ;)
marshybid
06-27-2008, 07:43 AM
Select the code that says Time, Shift-F2 and see if it takes you anywhere.
I remembered that you advised to do that when I couldn't get 'format' to work :rotlaugh:
Tried, no good, will require further investigation on my part
Thanks for the pointer though Bob.
Marshybid:beerchug:
Simon Lloyd
06-27-2008, 07:59 AM
TIME belongs to the VBA DateTime library (Microsoft Office 11.0 (12.0 for me) Object Library, find it in Tools References from the VBE)
slamet Harto
06-30-2008, 03:26 AM
Dear all,
Apologize for late reply and thank you so muach for your assistance.
highly Appreciate it.
Rgds, Harto
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.