add currency to textboxes & listbox on userform based on optionbuttons

Mussa

Board Regular
Joined
Jul 12, 2021
Messages
164
Office Version
  1. 2019
  2. 2010
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 .
1.PNG

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
2.PNG


this is the whole code .
VBA 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


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

Well-known Member
Joined
Jun 3, 2015
Messages
3,738
Office Version
  1. 365
Platform
  1. Windows
I must remember to have coffee before responding . I meant optionbutton, not checkbox. :eek:
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.
VBA Code:
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Mussa

Board Regular
Joined
Jul 12, 2021
Messages
164
Office Version
  1. 2019
  2. 2010
I appreciate that !
unfortunately doesn't work .
what about listbox?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,738
Office Version
  1. 365
Platform
  1. Windows
"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

Board Regular
Joined
Jul 12, 2021
Messages
164
Office Version
  1. 2019
  2. 2010
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

Rich (BB code):
ListBox1.List(lindex, 7) = Format$(ListBox1.List(lindex, 7), myFormat(0)) = OptionButton1.Value
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,738
Office Version
  1. 365
Platform
  1. Windows
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.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,738
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 12, 2021
Messages
164
Office Version
  1. 2019
  2. 2010
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

Well-known Member
Joined
Jun 3, 2015
Messages
3,738
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 12, 2021
Messages
164
Office Version
  1. 2019
  2. 2010
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 :)
 

Forum statistics

Threads
1,175,530
Messages
5,897,952
Members
434,688
Latest member
vi28

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