Error Alert Message Box

[raydeep1347]

Hi All

I am new to the forum and kindly help if I am not posting this query at the right place.

I have a sheet that has 2 buttons - "Start" & "End" which capture the time one clicks these buttons. the start click time is recorded in the Col. P & End click time on col. Q. There's a formula in col. R which calculates the difference of the end & start time and gives me the "Time Taken".

Problem arises when someone 'by mistake' someone clicks the end button first. So i need a msgbox to give out an error message whenever someone clicks the "End" button before clicking the "Start" button and simultaneously not record the time in that case. So basically when a time gets recorded in the col. Q and there's no time entry in col. P, the error msg should fire and this needs to work for the entire Col. P & Q.

Will be great if I could get some help on this.... need to roll out the stuff on priority. Thanks in Advance.

 

[rudfaden]

Can you post your code?

 

[raydeep1347]

START button :

Sub record()
'
' record Macro
'
'
Dim r1 As Range, r2 As Range
Set r1 = Intersect(Range("P:P"), Cells.SpecialCells(xlCellTypeBlanks))
Set r2 = Cells(Rows.Count, "P").End(xlUp).Offset(1, 0)
If r1 Is Nothing Then
r2.Select
Else
r1.Select
End If
ActiveCell.FormulaR1C1 = Time
End Sub

END button :

Sub endrecord()
'
' endrecord Macro
'
Dim r1 As Range, r2 As Range
Set r1 = Intersect(Range("Q:Q"), Cells.SpecialCells(xlCellTypeBlanks))
Set r2 = Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0)
If r1 Is Nothing Then
r2.Select
Else
r1.Select
End If
ActiveCell.FormulaR1C1 = Time
End Sub

 

[rudfaden]

Maybe this will work

Sub TimeStart()
Range("P2") = Format(Now, "hh:mm:ss")
End Sub

Sub TimeStop()
If IsEmpty(Range("P2")) Then
MsgBox "Your error message"
Else
Range("Q2") = Format(Now, "hh:mm:ss")
End If
End Sub

 

[raydeep1347]

Hi Rudfaden

Thanks for your prompt response....

Actually I also had to keep in mind that there will be multiple start & end time entries in both P & Q col. thus only P2 or Q2 would not work.

Anyway, I finally figured out the complete work with the help of your inputs. Following is what is the final code in END Click :

Sub endrecord()
'
' endrecord Macro
'
Dim r1 As Range, r2 As Range
Set r1 = Intersect(Range("Q:Q"), Cells.SpecialCells(xlCellTypeBlanks))
Set r2 = Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0)
If r1 Is Nothing Then
r2.Select
Else
r1.Select
End If
If ActiveCell.Offset(0, -1).Value = "" Then
MsgBox "Please enter START time first"
Else
ActiveCell.FormulaR1C1 = Time
End If
End Sub