• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

User Form text box issue


New Member
i have a database that when a colleague wants to update their record, they click add button and a user form appears. So far so good, they then press submit and the data transfers onto the table.

i Have left the option open for any user to input data directly onto the table. When it comes to the time format it must be as HH:MM so 13:45 not 13.45. If the user does not enter the time format in the correct way it still transfers the data but the sum value is wrong.

is it possible to put a code on the user from text box where it is asking for start time that they must put the entry in 00:00 format when entering onto the user form of add or use Else it will return a critical message to re enter.


Excel Ninja
There are many ways to do it.

One of the easiest way to split Hour & Minutes into separate textbox and validate each box has only number and between 0 to 23 for hours, 0 to 59 for minutes.

Another method is to use Instr() function to check that there is ":" in the string. Then split the string using ":" as delimiter and check that 1st element falls between 0 to 23 and 2nd element falls between 0 to 59.

Exact method and code will likely depend on your specific set up. I'd recommend uploading simplified/desensitized sample.


New Member
Hi there,

Thanks for the reply and help. I've uploaded my database - To log in select ADMIN - Password - Chuckles1 The password for VBA is pbarratt6344. Basically I need the best way so that when the user form is opened to add or use an entry where it says start time / end time this need to be in 00:00 format only. Can you describe the best code and where to put it as im struggling.





Well-Known Member
First code wil pop up a message that the format has to be 00:00 format when you enter the Starttime textbox
Private Sub TextBox3_Enter()
    MsgBox "Start time need to be in 00:00 format only.", vbExclamation, "Use correct format"
End Sub
The second code will correct to 00:00 if the user used the wrong format.
Private Sub TextBox3_AfterUpdate()
Dim tString As String
With TextBox3
    'Check if user put in a colon or not
    If InStr(1, .Value, ":", vbTextCompare) = 0 Then
        'If not, make string 4 digits and insert colon
        tString = Format(.Value, "0000")
        tString = Left(tString, 2) & ":" & Right(tString, 2)
        TextBox3.Value = Format(TimeValue(tString), "HH:MM")
        'Otherwise, take value as given
        .Value = Format(.Value, "hh:mm")
    End If
End With
End Sub
Do the same for the End time textbox.
See also attachment, ik put the the code in there for te 2 textboxes.
Hope this helps.