Excel - Textbox issue: Time displayed in decimal format

Issue

  • I have an EXCEL sheet with a list containing various data including hours (in hh: mm format).
  • I have also created a form that allows me to add or edit lines, but it seems that hours are being transformed into the decimal format upon import , (example 0.5625 for 13:30).
  • While in contrast, the exporting the hours from the TextBox to the list is done in the hh:mm format.
  • Here is an example of my workbook:here

Solution

Try this:

Private Sub NNI_Change()
    Dim Lg As Long

    Lg = NNI.ListIndex + 2
    For Each ctl In Me.Controls
        If ctl.Tag > "A" Then
            ctl.Value = Feuil2.Range(ctl.Tag & Lg)
            If ctl.Tag > "K" Then
                ctl.Value = Format(Feuil2.Range(ctl.Tag & Lg), "hh:mm")
            End If
        End If
    Next

End Sub

Thanks to Mytå for this tip.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Textbox issue: Time displayed in decimal format », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).