Solved: Allowing macro beetwen 4 - 5 PM

[slamet Harto]

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

[VBA]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[/VBA]

[marshybid]

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

[vba]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[/vba]

See below, works fine for me

[vba]
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
[/vba]

Hopefully it will work for you

Marshybid

[Bob Phillips]

Suggest a small change

[vba]

If CurrTime > #3:59:59 PM# And CurrTime < #5:00:01 PM# Then
[/vba]

[JimmyTheHand]

[vba]If CurrDate <= #3:59:00 PM# Or CurrDate >= #5:01:00 PM# Then[/vba]

EDIT
Bob is right, the timestamps can be refined by adding seconds.
So it will be
[vba]If CurrDate <= #3:59:59 PM# Or CurrDate >= #5:00:01 PM# Then[/vba]

Jimmy

[marshybid]

Suggest a small change

[vba]

If CurrTime > #3:59:59 PM# And CurrTime < #5:00:01 PM# Then
[/vba]

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

[marshybid]

[vba]If CurrDate <= #3:59:00 PM# Or CurrDate >= #5:01:00 PM# Then[/vba]

EDIT
Bob is right, the timestamps can be refined by adding seconds.
So it will be
[vba]If CurrDate <= #3:59:59 PM# Or CurrDate >= #5:00:01 PM# Then[/vba]

Jimmy

Hi Jimmy, why OR?? The And seemed to work for me when I changed the times to test??

Marshybid

[JimmyTheHand]

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]

[VBA]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[/VBA]

[marshybid]

[vba]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[/vba]

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]

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

Marshybid

[Simon Lloyd]

It works perfect for me, obviously i changed the Call SaveUSerButton for a msgbox but it worked fine!

[marshybid]

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??

Marshybid

[Bob Phillips]

Ditto, changed SaveUserButton to a msgbox that read 'working'

Can't seem to get Time recognised though??

Marshybid

Select the code that says Time, Shift-F2 and see if it takes you anywhere.

[Simon Lloyd]

you may be missing a library reference!

[Simon Lloyd]

oops sorry didn't see your toes there Bob!!!

[marshybid]

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

Tried, no good, will require further investigation on my part

Thanks for the pointer though Bob.

Marshybid

[Simon Lloyd]

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]

Dear all,

Apologize for late reply and thank you so muach for your assistance.
highly Appreciate it.

Rgds, Harto