PDA

View Full Version : [SOLVED] Detect listbox item is numeric problem



emina002
07-22-2016, 09:06 AM
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
07-22-2016, 10:49 PM
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
07-22-2016, 10:54 PM
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