Correct formatting for excel vba time

Question

Good afternoon!

I’m finding a problem that I thought was basic but that I couldn’t solve.

I have a Userform (Entradas) to register entries. These entries are played to my spreadsheet in Excel. In my second Userform (Listagem) I pull the data from this spreadsheet into a Listview. So far I have been able to pull this data - already inserted and stored in my spreadsheet -, minus one: the time.

The idea is when I make a record (by Userform of entries), I pull the date and time automatically without the user seeing. In the spreadsheet this data appears normally, only when I pull this data into my Listview is that my time appears in wrong format.

On the spreadsheet:

In Listview:

I thought I declare mine TextBox hourly as Time would be enough, since with the date it seems to have worked.

Follow my code in the first Userform (Entradas):

Private Sub BtnSalvar_Click()

Dim LINHA As Integer

LINHA = Planilha3.Range("A1000000").End(xlUp).Row + 1


Planilha3.Range("A" & LINHA) = TextData.Text
Planilha3.Range("B" & LINHA) = TextHora.Text

Private Sub TextHora_AfterUpdate()

    TextHora.Value = Format(TextHora, "00"":""00"":""00")

End Sub

Private Sub UserForm_Initialize()

    TextData = Date
    TextHora = Time

End Sub

I did not put all the code, because it would be too long, so I left only the snippets that had minimal connection with the variables of my problem.

Can anyone tell me how to solve this? I appreciate the attention.

Answer 1

Text only

It is not necessary to format if you only want to import the text to Listview, as it is already formatted correctly in the spreadsheet.

A test was performed with the following data:

And the code of this answer:

Private Sub PopularListView()

    'Declaração de variáveis
    Dim wksOrigem As Worksheet
    Dim rData As Range
    Dim rCell As Range
    Dim LstItem As ListItem
    Dim linCont As Long
    Dim colCont As Long
    Dim i As Long
    Dim j As Long

    'Definição da planilha de origem
    Set wksOrigem = Worksheets("Planilha1")

    'Definição do range de origem
    Set rData = wksOrigem.Range("A2").CurrentRegion

    'Adicionar cabeçalho no listview com laço de repetição 'For'
    For Each rCell In rData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rCell.Value, Width:=90
    Next rCell

    'Alimentar variável linCont com número de linhas do intervalo fonte
    linCont = rData.Rows.Count

    'Alimentar variável colCont com número de linhas do intervalo fonte
    colCont = rData.Columns.Count

    'Popular o ListView
    For i = 2 To linCont
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rData(i, 1).Text)
        For j = 2 To colCont
            LstItem.ListSubItems.Add Text:=rData(i, j).Text
        Next j
    Next i

End Sub
Private Sub UserForm_Initialize()

    'Ajustes de algumas propriedades importantes do ListView
    With Me.ListView1
        .Gridlines = True                        'linhas de grade visíveis (true)
        .HideColumnHeaders = False               'False para cabeçalho visível e True para invisível
        .View = lvwReport                        'Ajustar visualização do listview
    End With
    'se quiser detalhes da propriedade .view consulte: https://msdn.microsoft.com/en-us/library/aa733652(v=vs.60).aspx)

    'Chamar o procedimento para popular o Listview1
    Call PopularListView

End Sub

The result is as follows:

Time value and format

You already want to save the time value as a variable to perform calculations, declare a time variable and assign the cell value (hora = rData(i, 2).Value).

Then format to the desired format when inserting into Listview:LstItem.ListSubItems.Add Text:=Format(hora, "hh:mm:ss")

Private Sub PopularListView()

    'Declaração de variáveis
    Dim wksOrigem As Worksheet
    Dim rData As Range
    Dim rCell As Range
    Dim LstItem As ListItem
    Dim linCont As Long
    Dim colCont As Long
    Dim i As Long
    Dim j As Long
    Dim hora As Double

    'Definição da planilha de origem
    Set wksOrigem = Worksheets("Planilha1")

    'Definição do range de origem
    Set rData = wksOrigem.Range("A2").CurrentRegion

    'Adicionar cabeçalho no listview com laço de repetição 'For'
    For Each rCell In rData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rCell.Value, Width:=90
    Next rCell

    'Alimentar variável linCont com número de linhas do intervalo fonte
    linCont = rData.Rows.Count

    'Alimentar variável colCont com número de linhas do intervalo fonte
    colCont = rData.Columns.Count

    'Popular o ListView
    For i = 2 To linCont
        hora = rData(i, 2).Value
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rData(i, 1).Text)
        LstItem.ListSubItems.Add Text:=Format(hora, "hh:mm:ss")
    Next i

End Sub
Private Sub UserForm_Initialize()

    'Ajustes de algumas propriedades importantes do ListView
    With Me.ListView1
        .Gridlines = True                        'linhas de grade visíveis (true)
        .HideColumnHeaders = False               'False para cabeçalho visível e True para invisível
        .View = lvwReport                        'Ajustar visualização do listview
    End With
    'se quiser detalhes da propriedade .view consulte: https://msdn.microsoft.com/en-us/library/aa733652(v=vs.60).aspx)

    'Chamar o procedimento para popular o Listview1
    Call PopularListView

End Sub