show specific currency and numberformat in listbox & textbox

[abdelfattah]

hi

I create specific currency as in sheet in columns E,F "LYD" by cell format . so waht I want when show the userform should show number format in list box based on columns D,E,F but the currency based on columns E,F also the same thing when write numbers in textbox2,3 should show numberformat and currency .

this is the whole code

Dim arr



Private Sub UserForm_Initialize()
With Sheets("sheet1")
Set rg = .Range("A2:F15")
arr = rg
End With
    With ListBox1
    .ColumnCount = 6
    .ColumnWidths = "80,120,80,80,80,80,80,80,80"
    .List = arr
    End With

End Sub

Private Sub textbox1_Change()
Dim rw()
    For i = 1 To UBound(arr)
    If LCase(arr(i, 2)) Like "*" & LCase(TextBox1.Value) & "*" Then
    ReDim Preserve rw(p)
    rw(p) = Application.Index(arr, i, 0)
    p = p + 1
    End If
    Next
 
   If p = 0 Then MsgBox "NO MATCH": Exit Sub
With ListBox1
    If p > 1 Then
        .List = Application.Transpose(Application.Transpose(rw))
    Else
        .Column = Application.Transpose(rw)
    End If
End With
End Sub

Sub FormatLB()
With ListBox1
For i = 0 To .ListCount - 1
.List(i, 1) = Format(LB.List(i, 1), "dd/mm/yyyy")
.List(i, 2) = LB.List(i, 2)
.List(i, 3) = LB.List(i, 3)
.List(i, 4) = LB.List(i, 4)
.List(i, 5) = LB.List(i, 5)
.List(i, 6) = LB.List(i, 6)

Next
End With
End Sub

structure my data in sheet and userform

 

[dmt32]

well done ! but I have question about declare this

Dim arr()   As Variant

what's the difference if I put module level above or inside the UserForm_Intialize event?

Module level , the variable is available to all procedures in the module.
Procedure level, the variable is limited to the procedure

Glad resolved

Dave

 

[abdelfattah]

Dave you achieved a great work despite still I want how achieve in textbox but this is not a big problem. i will search for way to do that
thanks very much for your assistance