Data validation for text box in user form

I have 2 text boxes in my user form.
1- txtpresencehours
2- txtproductionhours
I want production hours value does not exceed presence hour (users can not enter)and if it happened a warning message window pops up

Code:

---

Private Sub txtPresenceHours_Afterupdate()
Dim tString As String
 With txtPresenceHours

'Check if user put in a colon or not
 If InStr(1, .Value, ":", vbTextCompare) = 0 And Len(.Value) > 1 Then

'If not, make string 4 digits and insert colon
 tString = Format(.Value, "0000")
 tString = Left(tString, 2) & ":" & Right(tString, 2)
 txtPresenceHours.Value = Format(TimeValue(tString), "hh:mm")
Else

'Otherwise, take value as given
 .Value = Format(.Value, "hh:mm")
End If
End With



End Sub
Private Sub txtProductionHours_AfterUpdate()

Dim tString As String
 With txtProductionHours

'Check if user put in a colon or not
 If InStr(1, .Value, ":", vbTextCompare) = 0 And Len(.Value) > 1 Then

'If not, make string 4 digits and insert colon
 tString = Format(.Value, "0000")
 tString = Left(tString, 2) & ":" & Right(tString, 2)
 txtProductionHours.Value = Format(TimeValue(tString), "hh:mm")
Else

'Otherwise, take value as given
 .Value = Format(.Value, "hh:mm")
End If
End With

If txtProductionHours.Value > txtPresenceHours.Value Then
MsgBox "Productuin hours must not exceed Presence hours", vbExclamation, "Userform1"
        Me.txtProductionHours.SetFocus
        Exit Sub
    End If
End Sub

---

1-What if i have for example 50 textboxes in my form, is there a a code just to put the boxes name in it instead of writing this code for each box

Code:

---

Private Sub txtPresenceHours_Afterupdate()
Dim tString As String
 With txtPresenceHours

'Check if user put in a colon or not
 If InStr(1, .Value, ":", vbTextCompare) = 0 And Len(.Value) > 1 Then

'If not, make string 4 digits and insert colon
 tString = Format(.Value, "0000")
 tString = Left(tString, 2) & ":" & Right(tString, 2)
 txtPresenceHours.Value = Format(TimeValue(tString), "hh:mm")
Else

'Otherwise, take value as given
 .Value = Format(.Value, "hh:mm")
End If
End With

---

2- Above code gives error for value higher than 23:59 ,also I don't want to round to days and hours i want them to be shown as they are. I changed the format to [h]:mm but didn't work

Not sure why you'd want 50+ text boxes with the same coding... but you can use the me.controls to loop them.. just name them and add a number to the end - usually two digits...
Example : txtProdHr01 txtPresHr01, txtProdHr02 txtPresHr02

loop would be something like

Code:

---

dim iCtr as long
dim sMsg as String
for iCtr = 1 to 25
  if me.controls("txtPres"+format(iCtr,"00"))<me.controls("txtProd"+format(iCtr,"00")) then
      sMsg+="Presence Hours are greater than Production hours ("+format(iCtr,"00")+")"
  end if
next
if len(sMsg) > 0 then msgbox "Problems with input : "+vbcrlf+sMsg

---

Now as you've said, problems with data entry.. well add them to the loop and validate that the entered data is correct. Either get the time it represents, or just get the digits. you can get that to check first and it if passes the validation check, check the hours.

Below is code to loop all controls... you'd need to select exactly which you want abd process accordingly...

Code:

---

dim ctl as control
for each ctl in me.controls
  if ctl.name like "txtPres*" then
    '---- do summat
  end if
next

---

* note : Access vba code.. slightly different for Excel, but similar

Quote:

---

What if i have for example 50 textboxes in my form, is there a a code just to put the boxes name in it instead of writing this code for each box

---

you can also use a collection of a class of textbox withevents, so the same afterupdate code procedure, can work for all the textboxes

Quote:

---

Above code gives error for value higher than 23:59

---

i would expect it should, you need to concatenate the hours and minutes to a string

Quote:

---

you need to concatenate the hours and minutes to a string

---

Oops. I don't know how to

Quote:

---

Oops. I don't know how to

---

you already are dong that with tString = Left(tString, 2) & ":" & Right(tString, 2)

as a date time value, then biggest you can have is 23:59
timevalue attempts to convert the string to a time value, which will cause an error with a value that can not converted
if you remove timevalue it should work correctly, but will allow values like 99:75 if values are entered into the textbox incorrectly

Quote:

---

as a date time value, then biggest you can have is 23:59

---

Quote:

---

if you remove timevalue it should work correctly, but will allow values like 99:75 if values are entered into the textbox incorrectly

---

If i'm right you say with VBA it,s impossible to have time format for values greater than 23:59

Quote:

---

it,s impossible to have time format for values greater than 23:59

---

no, you can format any string as hh:mm, but you can not have a timevalue > 23:59