VBA equivalent to TEXT

[andrewb90]

Hi all

I have this code:

Order00a.ComboBox4.List = Range("QTIME").Value
Order00a.ComboBox4.ListIndex = 0

My issue is, the values in the range are formatted as time, and when they appear in my combobox, they are decimals. How do I adjust this?

 

[dmt32]

Re: VBA equivillent to TEXT

Hi all

I have this code:

Order00a.ComboBox4.List = Range("QTIME").Value
Order00a.ComboBox4.ListIndex = 0

My issue is, the values in the range are formatted as time, and when they appear in my combobox, they are decimals. How do I adjust this?

Hi,

try using the Range.Text property

Order00a.ComboBox4.List = Range("QTIME").Text
Order00a.ComboBox4.ListIndex = 0

Dave

 

[andrewb90]

Re: VBA equivillent to TEXT

I get run time error 381
Invalid property array index/.

 

[Rick Rothstein]

Re: VBA equivillent to TEXT

Hi all

I have this code:

Order00a.ComboBox4.List = [B][COLOR="#FF0000"]Format([/COLOR][/B]Range("QTIME").Value[B][COLOR="#FF0000"], "h:mm")[/COLOR][/B]
Order00a.ComboBox4.ListIndex = 0

My issue is, the values in the range are formatted as time, and when they appear in my combobox, they are decimals. How do I adjust this?

See if adding what I show in red above does what you want.

 

[dmt32]

Re: VBA equivillent to TEXT

I get run time error 381
Invalid property array index/.

Oops sorry about that - only glanced at your code & did not note you were using the List property of combobox

This seems a bit busy but maybe this will help

    Dim Cell As Range, QTIME As Range
    Dim arr() As String, i As Integer
    Set QTIME = Range("QTIME")
    ReDim arr(1 To QTIME.Cells.Count)
    For Each Cell In QTIME
        i = i + 1
        arr(i) = Cell.Text
    Next Cell


    Me.ComboBox4.List = arr

Dave

 

[andrewb90]

Re: VBA equivillent to TEXT

I get a type mismatch error.

 

[MARK858]

Re: VBA equivillent to TEXT

I get a type mismatch error.

Are you replying to Rick or Dave? and on what line are you getting the type mismatch error?

 

[andrewb90]

Re: VBA equivillent to TEXT

that was replying to Rick. the error was on the line with the QTIME format.

 

[Rick Rothstein]

Re: VBA equivillent to TEXT

that was replying to Rick. the error was on the line with the QTIME format.

I just realized that QTIME is a range of cells, not a single cell reference. Try using this line of code to assign your list of times to your ComboBox...

Order00a.ComboBox4.List = [IF({1},TEXT(QTIME,"h:mm"))]

 

[andrewb90]

Re: VBA equivillent to TEXT

Wonderful! Thank you!