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 think you're using the wrong event. Change event fires with just about every keystroke so any thing you try to add there will get added upon each keystroke. I would use BeforeUpdate event and do

TextBox1 = "LYD" & TextBox1 (or whatever is correct for the textbox).
Call FilterData

However, if the event fires again, LYD will be added again, so you'd have to check what your option frame value is and if it matches LYD and LYD is found in the textbox value, don't add it again. I looked for your frame code but didn't see it posted here.

 

[Mussa]

I looked for your frame code but didn't see it posted here.

I don't understand it, what you mean?
there is no frame on userform at all.

 

[Micron]

There should be an option group (aka option frame) if you're going to use option buttons.

 

[Micron]

 

[Mussa]

There should be an option group (aka option frame) if you're going to use option buttons.

just question if you don't mind . why should add the frame?
in some cases when use optionbuttons without add frame it works normally

 

[Micron]

AFAIK, you can select or deselect any number of checkboxes in any pattern if they're not bound to a frame. I'm assuming that what you want to happen depends on the selected checkbox. If you cannot select LYD and $ at the same time, then you either have a frame and don't know it or these controls don't behave the same way that they do in M$ Access. Otherwise, if you can select 2 or more options and try to run code you'll get both LYD and $ added to your number (or even 3 items if all are selected). When coding, you don't just code for what you know will happen - you code for what can happen and shouldn't.

 

[Mussa]

so what's the next step ? I added the frame and it's still not working .

 

[Micron]

Looks like I owe you an apology. I tested by adding 3 option buttons without a frame and found that only one can be selected. This is not the way it happens in Access, which is the sort of thing that frustrates me when trying to help out here. You'd think there would be some consistency between the same things in two M$ apps.
I'll get back to you very soon with something that should work.

EDIT - I need to know which control you want to fire the event; checkbox or textbox. If checkbox, could textbox be empty when checkbox is clicked on? Would help if you could describe the way this form is used or filled out.

 

[Mussa]

based on my code & the userform there is no checkbox just optionbutton & textbox1 when write the item will show data in textbox2,3 & listbox1 , when select one of them ,then should add currency before the amount in textbox2,textbox3 & listbox1 for columns 7,9
yes textbox could be empty then when select optionutton should just add like this $0.00 in textbox2,3. as to listbox1 should add the currency for columns 7,9 because it will show data in listbox when run the userform .