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]

Did not look at all your code
if declared the arr variable at procedure level as shown in my post then that needs to be deleted as you already have declared it at module level

Dave

 

[abdelfattah]

sorry ! about this point you're right , but even if delete it .it stiil shows the error .

 

[dmt32]

sorry ! about this point you're right , but even if delete it .it stiil shows the error .

Module declaration should be like this

Dim arr()   As Variant

Dave

 

[abdelfattah]

this is what existed in your updating . and still show the error

 

[dmt32]

try changing this line

For i = 1 To UBound(arr)

to this

For i = 1 To UBound(arr,1)

Dave

 

[abdelfattah]

nothing changes . the same error

 

[dmt32]

nothing changes . the same error

Think this is a different issue but publish all the code behind your form or better, place copy of your workbook on filesharing site & provide a link to it

Dave

 

[abdelfattah]

here is the file
CC.xlsm

 

[dmt32]

As I suspected, you have deleted the Declaration at module level

the line needs to be at the VERY TOP of your forms code page

Dim arr()   As Variant

and DELETE it in the UserForm_Intialize event

also, return then line that was erroring back to its original state

For i = 1 To UBound(arr)

Dave

 

[abdelfattah]

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?