Change listbox colomns to hh:mm

[dotchieJack]

Hello everybody,
I need a bit of code to change column 7 and 8 into hh:mm in my listbox called ListofData
now I get decimals
The list is populated from Formulas -> Defined name list Called ListofData
file included
Thanks in advance

 

[Chihiro]

Changed following.
1. Made ListofData dynamic using formula to populate range
=OFFSET(Invulblad!$A$3,0,0,COUNTA(Invulblad!$B:$B)-1,12)

Your original used static range and populated unnecessarily large range to the listbox (with empty cells).

2. Inserted following part after ".List = Myarray" Line

        For i = 0 To rng.Rows.Count - 1
                .List(i, 6) = (Format(Val(.List(i, 6)), "hh:mm"))
                .List(i, 7) = (Format(Val(.List(i, 7)), "hh:mm"))
        Next

Note that .List starts from 0. So Column 7 is referred to as .List(i, 6).

See attached.

 

[dotchieJack]

Hello Chihiro,
Thank you for you quick reply and answer. However, i get an error. I cleared the contens of the first (and only) row and then i got the error. I tried to solve it but i can't find a solution

 

[Chihiro]

Hmm? Do you mean that you want to load without any data upon Userform initialize?

Easiest way to get around that is to include header row.

 

[dotchieJack]

Hello Chihiro,
Thank you again.
Yes, that is what i mean, i should be able to start with an empty sheet, exept for the header row (row 2)
the header row is row 2, i did not clear the contents of row 2 only of row3. I did not change anything else in the example file

 

[Chihiro]

Ok then. Change following.
1. ListofData Range formula adjusted to...
=OFFSET(Invulblad!$A$3,0,0,COUNTA(Invulblad!$B:$B),12)

2. Changed format code portion to only apply if rng.Rows.Count is greater than 1

        If rng.Rows.Count > 1 Then
            For i = 0 To rng.Rows.Count - 2
                    .List(i, 6) = (Format(Val(.List(i, 6)), "hh:mm"))
                    .List(i, 7) = (Format(Val(.List(i, 7)), "hh:mm"))
            Next
        End If

 

[dotchieJack]

Hoi
Still one problem, the listbox gives values 00:00 and 00:00 but i filled in 08:00 and 13:00

 

[Chihiro]

Hmm? Can you detail exactly when that happens?

I tested it by entering dummy data and had no issues.

 

[dotchieJack]

Hoi,
This is wat i get

 

[dotchieJack]

New try

 

[Chihiro]

Sorry, can't replicate your issue. You are using the file I uploaded, yes?

 

[dotchieJack]

Hello,
Yes, I am using your uploaded file, I am using a Dutch excel version, can that be the issue?

 

[Chihiro]

May be... but to my knowledge time format shouldn't change between languages.

Test by replacing "hh:mm" portion of the code with other variants such as "H:mm" and see what happens.

 

[dotchieJack]

I give it a try tommorow and comeback to you
I saw the formula in the ListofData name reference was auto translated to dutch
Thanks for all the help until now
Have a good night

 

[dotchieJack]

Hello Chihiro,
I've been trying a few things the last 2 hours and i don't find a solution
Any ideas?

 

[Chihiro]

Try this workaround.

When adding items to Myarray. Use .Text of Cells. This should enter value as string and retain format. One side effect is that you will need to convert it back to Time Value if you need to do operation within VBA.

However, didn't see any operation done using these values in your code.

See attached.

 

[dotchieJack]

Hello Chihiro,
Thank you for your reply and Solution.
The 3th version is working fine
Thank you for your patience and assistance
Greetz
(problem solved)