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]

Then why not just tell me what the variable is for and why you have it in the click event?

 

[Micron]

I think I figured it out by myself. Will post code soon.

 

[Mussa]

Then why not just tell me what the variable is for and why you have it in the click event?

what do you mean click event ? just I try it . when select option then implement what I trying , so which I put in event ?

 

[Micron]

Looks like I've solved this - for $ at least. I need to test for LYD and £ and clean up the code as it's a mess right now but company is coming. I'll just post the altered code if that's ok. It was harder than anticipated.

 

[Mussa]

Yes this is exactly what I was it. Hope achieve that. Much appreciated

 

[Micron]

Here's some of what I experienced.
Say you want to type 100 into textbox1. Keep in mind that the following applies if you type into textboxes OR set a value using code:

As soon as you type 1, textbox1 change event happens. That causes FilterData to run.
Code changes textbox2 value thus change event happens. That causes FilterData to run.
Code changes textbox3 value thus change event happens. That causes FilterData to run.
Now type in 0 and 3 events run again.
Now type next 0 and 3 events run again. This is not good.

At first I dealt with this by using textbox AfterUpdate event which meant that you must enter IDTR-100 and exit before list would change.
The great difficulties were:
- dealing with textbox not = 0 if a character was left in
- could not use Val as in earlier post because after a currency sign is there, the first char is not numeric so code exits
- using code to add $ to checkbox fires change event, which runs FilterData which removes currency sign

SO, either change event would have to be suppressed sometimes and sometimes not OR AfterUpdate event should fix that. After many hours of playing with this, I ended up using AfterUpdate. If you are not happy with that you could investigate using a global variable that you turn on/off which causes events to exit or not.

Note that I turned off form modal, otherwise impossible to work with. You can change back if you wish. I didn't edit Initialize code so didn't post it here.
The first code line is what appears under Option Explicit. Note that means MySum and MySum1 are now module level variables. That is part of how I solved the issue of FilterData over writing the textboxes and losing their sums.
I do not see why you had change event for all 3 textboxes, assuming you're filtering the list by only typing into one of them, but I wrote the AfterUpdate code for all 3 anyway. If that is true, then removing Change event for #2 and #3 boxes would eliminate a lot of the repeated calls I mentioned above in this post.

Dim a As Variant
Dim MySum As Double, MySum1  As Double

Private Sub OptionButton1_Click()
Dim lindex As Long
Dim strAdd As String

If OptionButton1.Value = True Then
     If Not Len(TextBox2) = 0 And Not Len(TextBox3) = 0 Then
          strAdd = OptionButton1.Caption & " "
          For lindex = 0 To ListBox1.ListCount - 1
               ListBox1.List(lindex, 8) = strAdd & MySum
               ListBox1.List(lindex, 9) = strAdd & MySum1
          Next
          TextBox2 = strAdd & MySum
          TextBox3 = strAdd & MySum1
     End If
End If

End Sub

Private Sub OptionButton2_Click()
Dim lindex As Long
Dim strAdd As String

If OptionButton2.Value = True Then
     If Not Len(TextBox2) = 0 And Not Len(TextBox3) = 0 Then
          strAdd = OptionButton2.Caption & " "
          For lindex = 0 To ListBox1.ListCount - 1
               ListBox1.List(lindex, 8) = strAdd & MySum
               ListBox1.List(lindex, 9) = strAdd & MySum1
          Next
          TextBox2 = strAdd & MySum
          TextBox3 = strAdd & MySum1
     End If
End If

End Sub

Private Sub OptionButton3_Click()
Dim lindex As Long
Dim strAdd As String

If OptionButton3.Value = True Then
     If Not Len(TextBox2) = 0 And Not Len(TextBox3) = 0 Then
          strAdd = OptionButton3.Caption & " "
          For lindex = 0 To ListBox1.ListCount - 1
               ListBox1.List(lindex, 8) = strAdd & MySum
               ListBox1.List(lindex, 9) = strAdd & MySum1
          Next
          TextBox2 = strAdd & MySum
          TextBox3 = strAdd & MySum1
     End If
End If

End Sub

Function FilterData()
    Dim i As Long, ii As Long, n As Long
   
    Me.ListBox1.List = a
    If Me.TextBox1 = "" Then Exit Function
    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 'moved and corrected
        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 Function

Private Sub TextBox1_AfterUpdate()
FilterData
End Sub

Private Sub TextBox2_AfterUpdate()
FilterData
End Sub

Private Sub TextBox3_AfterUpdate()
FilterData
End Sub

 

[Mussa]

much impressive ! just I need fixing the format number . when select currency it will delete the numberformat from textboxes and listbox .

 

[Micron]

Change as follows for the option button click events:

 ListBox1.List(lindex, 8) = strAdd & Format(MySum, "#,##0.00")
 ListBox1.List(lindex, 9) = strAdd & Format(MySum1, "#,##0.00")

and

TextBox2 = strAdd & Format(MySum, "#,##0.00")
TextBox3 = strAdd & Format(MySum1, "#,##0.00")

 

[Mussa]

great achieving !

I appreciate for your efforts and help

 

[Micron]

Thank you. Sometimes I just hate to admit defeat. ?‍?
Glad it worked for you.