Detect listbox item is numeric problem

[emina002]

I have a listbox in a userform where all the column header from A:AZ listed, I used a name range to capture it. There is no fix data or column header and it could be change from time to time(depend on the user), what I need to to determine is if the vba code detects that the line item is numeric it could format amount into "0,00.00", but date has to be formatted "mm/dd/yyyy". I have also a textbox where the items on listbox will be listed through double clicking.

Is there a way or function inside vba to detect numeric value? Thanks

[SamT]

Dim myVar As Variant
Dim Cel as Range

For each Cel in MyNamedRange
If IsNumeric(Cel) then 
myVar = Format(Cel, "#,##0.00")

ElseIf IsDate(Cel) then 
myVar = Format(myVar, "dd mmm, yyyy")

Else: myVar = Cel
End If

ListBox1.AddItem myVar
Next Cel

[mikerickson]

Rather than test for data type, you could use the .Text property of the cell and use the formatting determined in the worksheet.

Dim oneCell as Range

For Each oneCell in Range("A1:AZ1")
    ListBox1.AddItem oneCell.Text
Next oneCell