show specific currency and numberformat in listbox & textbox

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010
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
VBA 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
k.PNG
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010

ADVERTISEMENT

I change to this , but doesn't work

Code:
.List(i, 4) = Format(LB.List(i, 4), "#,##0.00")
Code:
.List(i, 5) = Format(LB.List(i, 5), "#,##0.00")
Code:
.List(i, 6) = Format(LB.List(i, 6), "#,##0.00")
Code:
Private Sub TextBox2_Change()
  Me.TextBox2.Value = "LYD" & Format(TextBox2.Value, "#,##0.00")
End Sub
Code:
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?
 
Last edited:

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows

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

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010

ADVERTISEMENT

if you mean function CDBL also doesn't work
VBA Code:
Private Sub TextBox2_Change()
  Me.TextBox2.Value = "LYD" & Format(CDBL(TextBox2.Value), "#,##0.00")
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows

What that clearly means ?​
Put a breakpoint to this codeline in order so see when the event is fired …​
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,922
Office Version
  1. 2019
Platform
  1. Windows
if you mean function CDBL also doesn't work
VBA Code:
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

VBA Code:
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
 
Solution

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010
@dmt32 thanks that's what I want , but it effects for the rest of code . it gives error mismatch in this line
VBA Code:
 For i = 1 To UBound(arr)
in Private Sub textbox1_Change()
 

Forum statistics

Threads
1,147,922
Messages
5,743,890
Members
423,824
Latest member
leahpatterson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top