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

 

[Marc L]

Hi,​

​

use the VBA text function Format the same way used in your cell formatting.​

​

Even you can read by code the cell format just with the property NumberFormat or when reading a cell with its Text property rathen than Value …​

 

[abdelfattah]

It's difficult for me to do that
may show me how,please?

 

[abdelfattah]

 

[abdelfattah]

I change to this , but doesn't work

.List(i, 4) = Format(LB.List(i, 4), "#,##0.00")

.List(i, 5) = Format(LB.List(i, 5), "#,##0.00")

.List(i, 6) = Format(LB.List(i, 6), "#,##0.00")

Private Sub TextBox2_Change()
  Me.TextBox2.Value = "LYD" & Format(TextBox2.Value, "#,##0.00")
End Sub

Private Sub TextBox3_Change()
  Me.TextBox3.Value = "LYD" & Format(TextBox3.Value, "#,##0.00")
End Sub

and I no know how should also mod when run the userform
any body guid me,please?

 

[Marc L]

You must first convert the text of your textbox to numeric then the Format function should work …​

 

[abdelfattah]

if you mean function CDBL also doesn't work

Private Sub TextBox2_Change()
  Me.TextBox2.Value = "LYD" & Format(CDBL(TextBox2.Value), "#,##0.00")
End Sub

 

[Marc L]

What that clearly means ?​

​

Put a breakpoint to this codeline in order so see when the event is fired …​

 

[dmt32]

if you mean function CDBL also doesn't work

Private Sub TextBox2_Change()
  Me.TextBox2.Value = "LYD" & Format(CDBL(TextBox2.Value), "#,##0.00")
End Sub

If you want to display cell formats in your listbox then use the Range.Text property to populate your array. This should display what you see in the cell & not its underlying value.

Delete the above codes & replace your UserForm_Intialize even with following & see if does what you want

Private Sub UserForm_Initialize()
    Dim rng     As Range
    Dim arr()   As Variant
    Dim r       As Long, c As Long
    
    Set rng = Worksheets("sheet1").Range("A2:F15")
    
    ReDim arr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    
    For r = 1 To UBound(arr, 1)
        For c = 1 To UBound(arr, 2)
            arr(r, c) = rng.Cells(r, c).Text
        Next c
    Next r
    
    With Me.ListBox1
        .ColumnCount = UBound(arr, 2)
        .ColumnWidths = "80,120,80,80,80,80"
        .List = arr
    End With

End Sub

Dave

 

[abdelfattah]

@dmt32 thanks that's what I want , but it effects for the rest of code . it gives error mismatch in this line

 For i = 1 To UBound(arr)

in Private Sub textbox1_Change()