How to apply custom format (currency) to a textbox based in a combobox selection?-CodePudding

I have a combobox with different currency items: "PEN, USD, etc" I have a textbox with an amount that I need it to have the corresponding currency symbol based on the selection of the combobox.

Not a solution:

If I use: TextBox1.Value = Format(TextBox1.Value, "$#,##0.00") it shows the dollar symbol.

TextBox1.Value = Format(TextBox1.Value, "[$S/-es-PE] #,##0.00") it doesnt show the S/ symbol (corresponding to PEN) I coppied this format from when custom formating a cell in a sheet.

TextBox1.Value = Format(TextBox1.Value, "currency") it shows the regional configuration currency symbol. I don't want to change my regional settings since this doesnt solve the problem. I want to be able to show different symbol according to the currency selected.

thanks!

CodePudding user response：

Please, try the next way. I tried to create a function to extract the value from the text. You can use it in order to make arithmetical operations with the text box value. Having a combo ("ComboBox1") and a text box ("TextBox1"), please paste the following code the combo box Click event:

Private Sub ComboBox1_Change()
   Dim arrCur, arrEch, mtch
  arrC = Split("USD,PEN,EURO", ",") 'the string representation from combo, separated by comma (not by ", ")
  arrEch = Split("$,S/.,€ ", ",")   'the desired corresponding format string
  If Me.ComboBox1.ListIndex <> -1 Then 'if a selection exists (in combo):
    If Me.TextBox1.Value <> "" Then    'if text box is  not empty:
        mtch = Application.match(Me.ComboBox1.Value, arrC, 0)
        Me.TextBox1.Value = arrEch(mtch - 1) & Format(numberPart(Me.TextBox1.Value), "#,##0.00")
    End If
  End If
End Sub

You can add as many currencies as you need, as comma separated in arrCur array. And then the corresponding sign to appear as suffix, in arrEch array. The next function is necessary to extract the numeric part and eliminate when change the format. It can be also used if you need to use the numeric part for other operations:

Private Function numberPart(strVal As String) As Currency
    Dim i As Long
    For i = 1 To Len(strVal)
        If IsNumeric(Mid(strVal, i, 1)) Then numberPart = CCur(Right(strVal, Len(strVal) - i   1)): Exit Function
    Next i
End Function

You can use the same (first) array to load the combo, pasting the next code in the form 'Initializeevent. In such a case, it should be good to declarearrCuras aPrivatevariable on top of the form module (in the declarations area) and just using it in the comboChange` event:

Private Sub UserForm_Initialize()
  Dim arrCur, El
  arrC = Split("USD,PEN,EURO", ",")
  For Each El In arrC
     Me.ComboBox1.AddItem El
  Next
End Sub

Please, test it and send some feedback.