Prompt message of time limit when time is entered

Hi again,

Is it possible to place a prompt message regarding the time limit or return time when entered time is place on the worksheet? Like "Please be back at 8:30 AM" when the user click the Start time button at 8:00 AM. I appreciate your assistance. Thanks

Minato

Hi,

Add the following macro to your start button:

Sub PleaseBeBackBy()
    Dim iHour As Integer, iMin As Integer, iSec As Integer
    iHour = Hour(Now())
    iMin = Minute(Now()) + 30
    iSec = Second(Now())
    MsgBox "Please be back at:" & Chr(13) & "    " & TimeSerial(iHour, iMin, iSec)
End Sub

whoa! that works perfectly Richard. Thanks a lot! I'm wondering if the same code applies when checking for the remaining time of the time limit. Like if the user's time limit is from 8:00 AM to 8:30 AM, if he came early than 8:30 say 8:15 and click the end button, a prompt message will display " You still have 15 minutes remaing. Do want to end?"

Thanks,

Minato

Hi,

More or less. Name a spare cell "start'. This will hold the time that the start button is pressed. Then attach the macros below to the start and end buttons.

Sub PleaseBeBackBy()
    Dim iHour As Integer, iMin As Integer, iSec As Integer
    Range("start") = Time
    iHour = Hour(Now())
    iMin = Minute(Now()) + 30
    iSec = Second(Now())
    MsgBox "Please be back at:" & Chr(13) & "    " & TimeSerial(iHour, iMin, iSec)
End Sub

Sub TimeLeft()
    If TimeSerial(Hour(Now()), Minute(Now() - 30), Second(Now())) > Range("start") Then
        MsgBox "You still have " & 30 - (Minute(Now) - Minute(Range("start"))) & " minutes remaining. " _
        & Chr(13) & "Do you want to end?"
    End If
End Sub

Rgds

hi richard,

I appreciate all of your assistance. I tried the last code you have given but unfortunately i got no luck with that. I have place the word START on one of the cells but once I click the start button there was no time being pasted but only displays the msg prompt of time limit. When I click the end button I am receiving the error message "Method 'Range' of 'object' Global Failed". Im sorry but im on the process of learning excel vba. I know im missing something here. Please enlighten me. Thanks

minato

Hi,

Yes I think you misunderstood. I didn't mean you to enter the word 'start' in a cell, but that you give a name called 'start' to a cell. To name a cell you can either use Insert Name Define and enter the cell reference, or simply select a cell and type the word 'start' in the NAME box, which is on the Formula Bar just above the 'A' of column A on the sheet.

I also realised my Time Left macro didn't cater for minutes where the start minute was before the hour and the end minute after the hour. Please use this modified code.

Sub TimeLeft()
   Dim iTLeft As Integer
   If TimeSerial(Hour(Now()), Minute(Now() - 30), Second(Now())) > Range("start") Then
        iTLeft = 30 - (Minute(Now()) - Minute(Range("start")))
        If Minute(Range("start")) > Minute(Now()) Then iTLeft = 60 + iTLeft
             MsgBox "You still have " & iTLeft & " minutes remaining. " _
             & Chr(13) & "Do you want to end?"
    End If
End Sub

Hi,

Yes I think you misunderstood. I didn't mean you to enter the word 'start' in a cell, but that you give a name called 'start' to a cell. To name a cell you can either use Insert Name Define and enter the cell reference, or simply select a cell and type the word 'start' in the NAME box, which is on the Formula Bar just above the 'A' of column A on the sheet.

I also realised my Time Left macro didn't cater for minutes where the start minute was before the hour and the end minute after the hour. Please use this modified code.

Sub TimeLeft()
   Dim iTLeft As Integer
   If TimeSerial(Hour(Now()), Minute(Now() - 30), Second(Now())) > Range("start") Then
        iTLeft = 30 - (Minute(Now()) - Minute(Range("start")))
        If Minute(Range("start")) > Minute(Now()) Then iTLeft = 60 + iTLeft
             MsgBox "You still have " & iTLeft & " minutes remaining. " _
             & Chr(13) & "Do you want to end?"
    End If
End Sub