Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

add specific currency in textbox based on optionbutton

0

hello

I have userform  contains three combobox .each other depends on other and I have two optionbuttons . I would when I select optionbutton (LYD ) then populate the price in textbox1 from column e for specific item after select from the three combobox, then should show LYD  beside  the  amount in textbox . the  code  works  perfectly  except  the  showed currency  . it  just  show  $  whether select LYD or USD .

Option Explicit
Private dic As Object

Private Sub UserForm_Initialize()
    Dim a, i As Long, ii As Long
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("brand").Cells(1).CurrentRegion
        a = .Parent.Evaluate(.Address & "&""""")
    End With
    For i = 2 To UBound(a, 1)
        If Not dic.exists(a(i, 1)) Then
            Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
        End If
        If Not dic(a(i, 1)).exists(a(i, 2)) Then
            Set dic(a(i, 1))(a(i, 2)) = CreateObject("Scripting.Dictionary")
        End If
        dic(a(i, 1))(a(i, 2))(a(i, 3)) = Array(a(i, 4), a(i, 5))
    Next
    Me.ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox2.List = dic(Me.ComboBox1.Value).keys
   GetPrice
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox3.List = dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).keys
   GetPrice
End Sub

Private Sub ComboBox3_Click()
   GetPrice
End Sub

Private Sub TextBox1_AFTERUPDATE()
    With Me.TextBox1
        If IsNumeric(.Value) Then .Value = Format(.Value, "$#,##0.00")
    End With
End Sub

Private Sub OptionButton1_Click()
    GetPrice
End Sub

Private Sub OptionButton2_Click()
    GetPrice
End Sub

Private Sub GetPrice()
    Dim i As Long, myCol As Long
    Me.TextBox1 = ""
    For i = 1 To 3
        If Me("combobox" & i).ListIndex = -1 Then Exit Sub
    Next
    If Not dic.exists(Me.ComboBox1.Value) Then Exit Sub
    If Not dic(Me.ComboBox1.Value).exists(Me.ComboBox2.Value) Then Exit Sub
    If Not dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).exists(Me.ComboBox3.Value) Then Exit Sub
    For i = 1 To 2
        If Me("optionbutton" & i) Then Exit For
    Next
    If i < 3 Then Me.TextBox1 = Format$(dic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(i - 1), "$#,##0.00")
End Sub

any  help  to  fix  it, please? 

Answer
Discuss

Discussion

Hi Abdo and welcome to the Forum. 

I'm away from my PC for some hours yet (so can't see the code behind your option button). Please edit your original question and use the CODE button to show it (paste over the text "Code_Goes_Here"). I'll then try to look ahead of my return to my PC.

Thanks.
John_Ru (rep: 6092) May 25, '22 at 1:19 pm
Hi John,
I  attached the  code .
Abdo M (rep: 16) May 25, '22 at 1:29 pm
Add to Discussion

Answers

0
Selected Answer

Abdo

Your problem can be fixed by firstly declaring a new variable Curr (for currency) outside the Userform procedures so it's available to all procedures, like this (changes in bold):

Option Explicit
Private dic As Object
Dim Curr As String

Then the code associated with the Option buttons sets that variable as follows when the option are chosen (in the attached file):

Private Sub OptionButton1_Click()
    Curr = "LYD"
    GetPrice
End Sub

Private Sub OptionButton2_Click()
    Curr = "USD"
    GetPrice
End Sub

Then your GetPrice sub has the final line changed from  ...=Format$(,,, "S#,##0.00) to the bold line below:

Private Sub GetPrice()
    Dim i As Long, myCol As Long
    Me.TextBox1 = ""
    For i = 1 To 3
        If Me("combobox" & i).ListIndex = -1 Then Exit Sub
    Next
    If Not dic.exists(Me.ComboBox1.Value) Then Exit Sub
    If Not dic(Me.ComboBox1.Value).exists(Me.ComboBox2.Value) Then Exit Sub
    If Not dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).exists(Me.ComboBox3.Value) Then Exit Sub
    For i = 1 To 2
        If Me("optionbutton" & i) Then Exit For
    Next
    If i < 3 Then Me.TextBox1 = Format$(dic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(i - 1), Chr(34) & Curr & Chr(34) & "#,##0.00")
End Sub

where the format element at the end:

Chr(34) & Curr & Chr(34) & "#,##0.00"

equates to setting a Custom worksheet format like:

"LYD"#,##0.00

since CHR(34) is the ASCII character for the double quotation mark (").

I deleted the sub TextBox1_AFTERUPDATE() which attempted to apply the S#,##0.00 format.

Note that I changed Label4 of your UserForm from QUANTITY to PRICE which it now shows. (Personally I would report the price in a label which the user cannot overtype!). I also aligned the elements to make it look neater and grouped the two option buttons and added this to set the default selection to LYD:

Private Sub UserForm_Initialize()
    Dim a, i As Long, ii As Long

    'Set default selection to Option Button 1 (LYD)
    OptionButton1.Value = True

Hope this fixes your problem.

Discuss

Discussion

wow !  you did me  a big  favor .
million thanks for  your assistance  !!
Abdo M (rep: 16) May 25, '22 at 6:50 pm
Glad that helped (and thanks for selecting my Answer, Abdo). I just corrected my Answer to remove a superfluous : in the text.
John_Ru (rep: 6092) May 26, '22 at 12:33 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login