CODEWITHSUNDEEP
exceltimeformatvba
Joakim Grønstrand
Joakim Grønstrand

Reputation: 15

Userform time format problems

Ok, so i have been working on a project for a while now. And its almost done. But i keep having small issues that i cant solve. This time its a formatting issue.

Short and simple: I have a table in sheet1(database) of information that i display in a userform (see image) Sheet1 Userform There are several approaches to add a new line in the database, you can add it manually by filling all the empty text boxes, or you can double click an existing row and the text boxes fill them self. My problem is when i double clicking existing row, time format changes. see image. image2

I solved that using this code:

Private Sub Reg5_Change()
Reg5.Value = Format(Reg5.Value, "hh:mm")
End Sub

But then i have trouble doing it the manually way. When i try to type in time i end up getting 00:00 and having trouble changing it.

Is there a way to solve this so i can use both methods for adding new row in database?

Other questions: I want the textbox ETA to contain ETD + EET. Image3

I tried this with a result of 08:02 when ETD = 08:00 and EET = 02:00. Seems like EET is added as minutes. 

Private Sub Reg7_AfterUpdate()
Reg7.Value = Reg5.Value + Reg6.Value
end sub

Update: 

Private Sub Reg7_Change()
Dim etd As String: etd = Reg5.Value
Dim eet As String: eet = Reg6.Value

Dim detd As date
Dim deet As date

detd = TimeSerial(Split(etd, ":")(0), Split(etd, ":")(1), 0)
deet = TimeSerial(Split(eet, ":")(0), Split(eet, ":")(1), 0)
Reg7.Value = detd + deet
end sub

Getting this error Error But as you can see in the image the time is correct and that part is working correct.

hope there is someone out there who can help me out

Upvotes: 1

Views: 319

Answers (1)

Vityata
Vityata

Reputation: 43595

Concerning the part for "I tried this with a result of 08:02 when ETD = 08:00 and EET = 02:00. Seems like EET is added as minutes."

Try to use TimeSerial() declare variables as date and pass variables to it. This is a simple example:

Public Sub TestMe()

    Dim etd As String: etd = "08:00"
    Dim eet As String: eet = "02:00"

    Dim detd    As Date
    Dim deet    As Date

    detd = TimeSerial(Split(etd, ":")(0), Split(etd, ":")(1), 0)
    deet = TimeSerial(Split(eet, ":")(0), Split(eet, ":")(1), 0)

    Debug.Print detd + deet '10:00:00

End Sub

Upvotes: 1

Related Questions