PDA

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