add currency to textboxes & listbox on userform based on optionbuttons

[Mussa]

hello,
I try adding the currency to textboxe2,3 and listbox after populate data based on textbox1 by select one of option button
based on picture after populate values .

when select one of the optionbuttons , for instance LYD then should add the currency before the numbers into textbox2,3 and the column 7,9 in listbox like this

this is the whole code .

Option Explicit

Dim a As Variant

Private Sub TextBox1_Change()
  Call FilterData
End Sub

Private Sub TextBox2_Change()
  
  Call FilterData
 
End Sub

Private Sub TextBox3_Change()

  Call FilterData
  

End Sub

Sub FilterData()
    Dim i As Long, ii As Long, n As Long
    Me.ListBox1.List = a
    If Me.TextBox1 = "" Then Exit Sub
    With Me.ListBox1
        .Clear
        For i = 0 To UBound(a, 1)
            If UCase$(a(i, 3)) Like UCase$(Me.TextBox1) & "*" Then
                .AddItem
                .List(n, 0) = n + 1
                For ii = 1 To UBound(a, 2)
                    .List(n, ii) = a(i, ii)
                Next
                n = n + 1
            End If
        Next
    End With
    Dim r As Long
       Dim MySum, MySum1  As Double

        MySum = 0
        MySum1 = 0
        With ListBox1
            For r = 0 To .ListCount - 1
                MySum = MySum + .List(r, 7)
                MySum1 = MySum1 + .List(r, 9)
            Next r
        End With

        TextBox2.Value = Format(MySum, "#,##0.00")
        TextBox3.Value = Format(MySum1, "#,##0.00")
        
End Sub

Private Sub UserForm_Activate()
      
      

End Sub
Private Sub UserForm_Initialize()
    Dim lindex&
    Dim rngDB As Range, rng As Range
    Dim i, myFormat(1) As String
    Dim sWidth As String
    Dim vR() As Variant
    Dim n As Integer
    Dim myMax As Single
    Set rngDB = Range("A2:J20")
    For Each rng In rngDB
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = rng.EntireColumn.Width
    Next rng
    myMax = WorksheetFunction.Max(vR)
    For i = 1 To n
        vR(i) = myMax
    Next i
    With Sheets("purchase").Cells(1).CurrentRegion
        myFormat(0) = .Cells(2, 8).NumberFormatLocal
        myFormat(1) = .Cells(2, 9).NumberFormatLocal
        Set rng = .Offset(1).Resize(.Rows.Count - 1)
        a = .Cells(1).CurrentRegion.Value
    End With

    sWidth = Join(vR, ";")
    Debug.Print sWidth
    With ListBox1
        .ColumnCount = 10
        .ColumnWidths = sWidth '<~~ 63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63
        .List = rng.Value
        .BorderStyle = fmBorderStyleSingle
        For lindex = 0 To .ListCount - 1
            '.List(lindex, 0) = (Format((.List(lindex, 0)), "dd/mm/yyyy"))   ' BL = dates
                        .List(lindex, 0) = lindex + 1

            .List(lindex, 7) = Format$(.List(lindex, 7), myFormat(0))
            .List(lindex, 8) = Format$(.List(lindex, 8), myFormat(1))
            .List(lindex, 9) = Format$(.List(lindex, 9), myFormat(1))
        Next
       
        a = .List
        '<--- this line
    End With
End Sub

and I try adding some codes but doesn't work

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
If IsNumeric(TextBox2.Value) And IsNumeric(TextBox3.Value) Then
TextBox2.Value = OptionButton1.Value
TextBox3.Value = OptionButton1.Value
ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton1.Value
ListBox1.List(lindex, 9) = Format$(ListBox1.List(lindex, 9), myFormat(1)) = OptionButton1.Value
End If
End If
End Sub
Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
If IsNumeric(TextBox2.Value) And IsNumeric(TextBox3.Value) Then
TextBox2.Value = OptionButton2.Value
TextBox3.Value = OptionButton2.Value
ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton2.Value
ListBox1.List(lindex, 9) = Format$(ListBox1.List(lindex, 9), myFormat(1)) = OptionButton2.Value
End If
End If
End Sub
Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
If IsNumeric(TextBox2.Value) And IsNumeric(TextBox3.Value) Then
TextBox2.Value = OptionButton3.Value
TextBox3.Value = OptionButton3.Value
ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton3.Value
ListBox1.List(lindex, 9) = Format$(ListBox1.List(lindex, 9), myFormat(1)) = OptionButton3.Value
End If
End If
End Sub

any suggestion to help?

 

[Micron]

I must remember to have coffee before responding . I meant optionbutton, not checkbox.
This should do what you asked for, but it won't undo. That is, if you want to change $ to £ and the £ button is True, you'll have to choose another button first (which should do nothing) and then remove £ then click $ option. To get this code to work you must change the caption property of each button. I have set as follows:
#1 is $
#2 is £
#3 is LYD
I also had to rem out your listbox lines since I don't have that and its corresponding code/data in my wb. Maybe un-comment those lines after you test the code. Note that Excel considers $40 to be numeric, so I used Val function, which returns 0 if the first character in a string is not a number. You don't need .Value in most cases (this code being one of them) but you can put it back in if you prefer.

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then
   If Not Val(TextBox2) = 0 And Val(TextBox3) = 0 Then
      TextBox2.Value = OptionButton1.Caption & TextBox2
      TextBox3.Value = OptionButton1.Caption & TextBox3
      'ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton1.Value
      'ListBox1.List(lindex, 9) = Format$(ListBox1.List(lindex, 9), myFormat(1)) = OptionButton1.Value
   End If
End If

End Sub
Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then
   If Not Val(TextBox2) = 0 And Not Val(TextBox3) = 0 Then
      TextBox2.Value = OptionButton2.Caption & TextBox2
      TextBox3.Value = OptionButton2.Caption & TextBox3
      'ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton2.Value
      'ListBox1.List(lindex, 9) = Format$(ListBox1.List(lindex, 9), myFormat(1)) = OptionButton2.Value
   End If
End If

End Sub
Private Sub OptionButton3_Click()

If OptionButton3.Value = True Then
   If Not Val(TextBox2) = 0 And Not Val(TextBox3) = 0 Then
      TextBox2.Value = OptionButton3.Caption & " " & TextBox2
      TextBox3.Value = OptionButton3.Caption & " " & TextBox3
      'ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton3.Value
      'ListBox1.List(lindex, 9) = Format$(ListBox1.List(lindex, 9), myFormat(1)) = OptionButton3.Value
   End If
End If

End Sub

EDIT - I changed OR in If Not line to And as I now suspect that is the proper choice.

 

[Mussa]

I appreciate that !
unfortunately doesn't work .
what about listbox?

 

[Micron]

"Doesn't work" doesn't help (it's in my signature). Post what you tried. What about the listbox? I did not modify your listbox code - your OP shows that it was working. But did tell you to un-comment out the lines that I had to comment out. Did you do that if the code added the value you wanted to the textboxes?

 

[Mussa]

I put the codes on userform module and write item into textbox1 then show data in listbox and amounts in textboxes2,3 after this select $ doesn't show any thing

about listbox doesn't work in my OP and pop up error compile error function not defined as in bold word lindex in

ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton1.Value

 

[Micron]

You are using Option Explicit (good) so you must declare all variables. lindex is either ListIndex misspelled or you forgot to declare a variable by that name.
I don't have enough information to help you with the population of the listbox. What I gave you works for what you asked for, namely to put $ or £ or LYD in front of a number. Maybe if you posted the wb somewhere I could figure it out.

 

[Mussa]

here is the file search on userform (2).xlsm

 

[Micron]

I think I get what you're asking now. You want the added characters to show in the listbox as well.
You will have to explain what lindex is. It's an undeclared variable in the code you posted, which I used without knowing what it is. However, I believe I did ask but that wasn't answered. If you got this code from somewhere, post a link to it if you do not know what lindex is. The only clue I have is that it is declared here

Private Sub UserForm_Initialize()
Dim lindex&

but that is no good for any other procedure.

 

[Mussa]

I appreciate for your trying to help me

forgive me , I no know why you want the link for this code .I gave you the whole code and the file !!!

just I try modifying by adding some currencies .

and you don't answer me why doesn't work in textboxes 2,3 even if I ignore listbox in my file but you say it should work .

 

[Micron]

I no know why you want the link for this code

So I can figure out what lindex is supposed to be, but since you are not answering that question and don't want to provide a link to the code, I think I have done all I can for you.

 

[Mussa]

I no know if I can get the link , it will take much time , if I got it I will give you
anyway thanks for your time