Correct formatting for excel VBA hour

Question

Correct formatting for excel VBA hour

Good afternoon!

I'm running into a problem that I figured was basic but couldn't solve.

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

The idea is when I make a registration (by Userform of inputs), 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 does my time appear in the wrong format.

In the spreadsheet:

In The Listview:

I thought declaring my TextBox of time as Time would be enough, since with the date it seems to have worked.

Follow my code on 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 the whole code, as it would be very extensive, so I left only the snippets that had minimal connection with the variables of my problem.

Does anyone know how to tell me how to solve this? I appreciate your attention.

1

excel vba excel-vba excel-userform userform

Author: Sena, 2020-09-14

Source

1 answers

score 1 · Accepted Answer

Text Only

You do not need to format if you want to import only the text into the 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 for perform calculations, declare a variable time and assign the value of the cell (hora = rData(i, 2).Value).

Then format to the desired format by inserting in the 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