Userform to check time change before entering data into spreadsheet

[LoneWolf3574]

I'm trying to get my userform to check that the time entered is 2 hours later than the previous line in the same column before entering the data into the spreadsheet (the data is taken in 2 hour intervals, so the time change will always be 2 hours). If it is not, then a message box pops up stating "Please enter the data for hh:mm first!" or something like that, I haven't decided on the exact message yet. I do understand how to check that there is a time in the textbox first, it's just that I haven't figured out how to get it to check the time is as I described.

I have tried to use DateDiff, but I can only get it to make a formula/function that I can use to create a helper column. When I do plug it into the coding for the UserForm nothing happens, it doesn't even return an error.

So I'm stuck on how to get the userform to check the previous lines time for the required 2 hour interval going into the new line. For instance, let's say the last entry made was for 14:00 and the user tries to enter data for 20:00, the form will stop, not enter the data and tell the user that they are missing the data for 16:00 (minutes and seconds play no part in the time check).

I've included the Function coding, formulas and UserForm coding below, as well as a link to the file.

Any help would be greatly appreciated at this point, thanks.

ThermalTesting.xlsm

Function TimeCheck(Time1 As Date, Time2 As Date) As Long      
  TimeCheck= DateDiff("h", Time1, Time2) 
End Function

=If(C6="",0,If(TimeCheck(C5,C6)=-22,2,TimeCheck(C5,C6)))  
=If(C6="",0,(C6-C5)+IF(C6<c5,1)) '<--="" this="" is="" actually="" easier="" to="" use="" in="" my="" opinion[="" code]
 
[CODE]Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Thermals")

'finds first empty row in the spreadsheet
iRow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
 
'check for a data entry
If Trim(Me.cboDate.Value) = "" Then
  Me.cboDate.SetFocus
  MsgBox "Please enter the Date"
  Exit Sub
End If

If Trim(Me.cboTime.Value) = "" Then
  Me.cboTime.SetFocus
  MsgBox "Please enter the Time"
  Exit Sub
End If
     
'THIS IS THE CODING I'VE TRIED TO USE
[COLOR=#ff0000]If Trim(Me.cboTime.Value) = DateDiff("h", Range("C5").End(xlDown).Select, Me.cboTime.Value) Then
  Me.cboTime.SetFocus
  MsgBox "Please check that you are entering the correct time!"
  Exit Sub
End If[/COLOR]
    
'copy the data to the database
With ws
  .Cells(iRow, 2).Value = Me.cboDate.Value
  .Cells(iRow, 3).Value = Me.cboTime.Value
  .Cells(iRow, 4).Value = Me.txtChWtrSup.Value
  .Cells(iRow, 5).Value = Me.txtChWtrRet.Value
  .Cells(iRow, 6).Value = Me.txtConWtrRet.Value
  .Cells(iRow, 7).Value = Me.txtConWtrSup.Value
  .Cells(iRow, 8).Value = Me.txtHeatWtrSup.Value
  .Cells(iRow, 9).Value = Me.txtHeatWtrRet.Value
  .Cells(iRow, 10).Value = Me.txtSluHeatSup.Value
  .Cells(iRow, 11).Value = Me.txtSluHeatRet.Value
  .Cells(iRow, 12).Value = Me.txtWstHeatSup.Value
  .Cells(iRow, 13).Value = Me.txtWstHeatRet.Value
  .Cells(iRow, 14).Value = Me.txtDomHWtrRet.Value
  .Cells(iRow, 15).Value = Me.txtDomColdWtr.Value
  .Cells(iRow, 16).Value = Me.txtDomHWtrSup.Value
  .Cells(iRow, 17).Value = Environ("Username")
  .Cells(iRow, 18).Value = Now
End With

'clear the userform
  Me.cboDate.Value = Format(Date, "Medium Date")
  Me.cboTime.Value = ""
  Me.txtChWtrSup.Value = ""
  Me.txtChWtrRet.Value = ""
  Me.txtConWtrRet.Value = ""
  Me.txtConWtrSup.Value = ""
  Me.txtHeatWtrSup.Value = ""
  Me.txtHeatWtrRet.Value = ""
  Me.txtSluHeatSup.Value = ""
  Me.txtSluHeatRet.Value = ""
  Me.txtWstHeatSup.Value = ""
  Me.txtWstHeatRet.Value = ""
  Me.txtDomHWtrRet.Value = ""
  Me.txtDomColdWtr.Value = ""
  Me.txtDomHWtrSup.Value = ""
  Me.cboDate.SetFocus
End Sub

</c5,1))>I've also made posts in other forums asking for help on this same issue (see below)

http://www.excelforum.com/excel-pro...fore-entering-data.html?p=3092167#post3092167
http://www.ozgrid.com/forum/showthre...d=1#post645520<c5,1)) '<--="" this="" is="" actually="" easier="" to="" use="" in="" my="" opinion[="" code]

</c5,1))>

 

[AlphaFrog]

One solution could be to select the next date\time values for the user.

Put this at the end of the UserForm_Initialize procedure and the cmdAdd_Click procedure.
Next_Date_Time

Then add this procedure in the UserForm module. It selects the next Date|Time value in the comboboxes.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Next_Date_Time()
  [COLOR=darkblue]With[/COLOR] Sheets("Thermals")
    cboDate.Value = Format(.Range("B" & .Rows.Count).End(xlUp).Value, "dd-mmm-yy")
    cboTime.Value = Format(.Range("C" & .Rows.Count).End(xlUp).Value, "hh:mm")
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
  [COLOR=darkblue]If[/COLOR] cboDate.ListIndex = -1 [COLOR=darkblue]Then[/COLOR] cboDate.ListIndex = 0
  [COLOR=darkblue]If[/COLOR] cboDate.ListIndex = -1 [COLOR=darkblue]Then[/COLOR] cboDate.ListIndex = 0
  [COLOR=darkblue]If[/COLOR] cboDate.ListIndex = cboDate.ListCount - 1 And cboTime.ListIndex = cboTime.ListCount - 1 [COLOR=darkblue]Then[/COLOR]
    MsgBox "The last Date in the date list with the last time has already been filled", , "Exit Form"
    Unload Me   [COLOR=green]'???[/COLOR]
  [COLOR=darkblue]Else[/COLOR]
    [COLOR=darkblue]If[/COLOR] cboTime.ListIndex = cboTime.ListCount - 1 [COLOR=darkblue]Then[/COLOR]
        cboDate.ListIndex = cboDate.ListIndex + 1
        cboTime.ListIndex = 0
    [COLOR=darkblue]Else[/COLOR]
        cboTime.ListIndex = cboTime.ListIndex + 1
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  End If
End [COLOR=darkblue]Sub[/COLOR]

 

[LoneWolf3574]

That does automatically advance the date/time to the next required entry, but it isn't giving a messagebox popup if I manually override the time beyond the 2 hour advancement.

 

[LoneWolf3574]

That will also prevent someone from doing an date/time entry that is older than the most recent. Also, did you mean for the second

[COLOR=darkblue]If[/COLOR] cboDate.ListIndex = -1 [COLOR=darkblue]Then[/COLOR] cboDate.ListIndex = 0

to be this instead?

[COLOR=darkblue]If[/COLOR] cboTime.ListIndex = -1 [COLOR=darkblue]Then[/COLOR] cboTime.ListIndex = 0

 

[AlphaFrog]

I can manually override the date and\or time and not receive a message. I can manually set it to any date or time and add that data.

Yes, the second cboDate line should be cboTime just as you suggested. What that line does is if the last entry on the sheet is not from the dropdown list, then default the combobox to to the first item in the list.