add currency to textboxes & listbox on userform based on optionbuttons

M

Mussa

Active Member
Joined
Jul 12, 2021
Messages
264
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?
 
Sort by date Sort by votes
Then why not just tell me what the variable is for and why you have it in the click event?
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I think I figured it out by myself. Will post code soon.
 
Upvote 0
Then why not just tell me what the variable is for and why you have it in the click event?
Click to expand...
what do you mean click event ? just I try it . when select option then implement what I trying , so which I put in event ?
 
Upvote 0
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.

1652553431918.png
 
Upvote 0
Yes this is exactly what I was it. Hope achieve that. Much appreciated ;)
 
Upvote 0
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.
VBA Code: 
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
 
Upvote 0
Solution
much impressive ! just I need fixing the format number . when select currency it will delete the numberformat from textboxes and listbox .
 
Upvote 0
Change as follows for the option button click events:
VBA Code: 
 ListBox1.List(lindex, 8) = strAdd & Format(MySum, "#,##0.00")
 ListBox1.List(lindex, 9) = strAdd & Format(MySum1, "#,##0.00")
and
VBA Code: 
TextBox2 = strAdd & Format(MySum, "#,##0.00")
TextBox3 = strAdd & Format(MySum1, "#,##0.00")
 
Upvote 0
Thank you. Sometimes I just hate to admit defeat. ?‍?
Glad it worked for you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
number format in three columns into listbox on useform
Replies
2
Views
520
abdelfattah
A
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
238
krunal123
K
M
  • Solved
make bigger or smaller height the listbox & userform based on populated data in listbox
2
Replies
18
Views
2K
Mussa
M
I
  • Solved
Date on userform shows tomorros date NOT todays
Replies
1
Views
196
ipbr21054
I
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top