show specific cuurency and numberformat in textbox on userform

alhagag · Oct 27th 2021

hi

I try making textbox contain specific currency , number format in textbox5,6 whether write in textbox or search by combobox . should show like this LYD 1,000.00

but it gives mismatch in this line

Code

s = "LYD"

theses codes cuase the problem

Code

Private Sub TextBox6_Change()
Dim s As Currency
 s = "LYD"
  Me.TextBox6.Value = s & Format(CDbl(TextBox6.Value), "#,##0.00")
End Sub

Private Sub TextBox7_Change()
Dim s As Currency
 s = "LYD"
  Me.TextBox7.Value = s & Format(CDbl(TextBox7.Value), "#,##0.00")
End Sub

Display More

is there any idea to achieve that?

**royUK** · Oct 27th 2021

Your code is formatting as #,##0.00.

The error occurs because you declare s as currency but apply a String.

What is the numberformat for your local currency in Excel?

You can try using FormatCurrency, remove your code and replace with:-

Code

Private Sub TextBox6_Change()
  Me.TextBox6.Value = FormatCurrency(TextBox6.Value)
End Sub
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox7.Value = FormatCurrency(CDbl(TextBox6.Value) * CInt(Me.TextBox5.Value))
End Sub

What is the combobox supposed to be doing?

**royUK** · Oct 27th 2021

You only need this in TextBox6

Code

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me
        .TextBox6.Value = FormatCurrency(.TextBox6.Value)
        .TextBox7.Value = FormatCurrency(CDbl(.Value) * CInt(.Value))
    End With
End Sub

alhagag · Oct 27th 2021

I don't use this FormatCurrency . I want specifiy specific currency . not based on local currency in my PC . i would the code dynamic

LYD OR RM OR LB and so on for any currency I coose it

Quote

What is the numberformat for your local currency in Excel?

I'm not sure what you mean, but as I said when write the number LYD 1.00 OR LYD 100.00 OR LYD 10,000.00

Quote

What is the combobox supposed to be doing?

it searches based on column A and bring all data from the sheets and when show the values in textbox6,7 should show currency , number format

kirankumar_312 · Oct 27th 2021

Hi All,

In a sheet i am having data, In C-column i am having country name & In D-column i am having state wise data

I want VBA code i.e each country as a (indiviadual workbook-Column C ) and In each workbook sub sheets data (state-Column D) wise data to be arrived.

**royUK** · Oct 27th 2021

FormatCurrency will display the currency used by your International settings on your computer.

Did you try my code?

What is the your native currency?

Did you check what I said about the mismatch?

Also, as I said this is the format your code is set for - #,##0.00.

Quote

I'm not sure what you mean, but as I said when write the number LYD 1.00 OR LYD 100.00 OR LYD 10,000.00

If you don't know what a Number Format is Excel then I don't think you should be looking at VBA. Adding LYD to a number will make that number a String.

Quote

it searches based on column A and bring all data from the sheets and when show the values in textbox6,7 should show currency , number format

That works very inefficiently and it causes an error in the userform.

Quote

In a sheet i am having data, In C-column i am having country name & In D-column i am having state wise data

I want VBA code i.e each country is indiviadual workbook and In each country wise muliple states to be aarrives

Do you mean individual worksheets? Again, a very inefficient way of working with data.

alhagag · Oct 28th 2021

royUK based on post#3 it shows $ not LYD . despite of that you use function native cuurency .

what's the problem ?

Quote

What is the your native currency?

LYD

I selected it this when search my native currency from cell format > cuurency

Quote

Also, as I said this is the format your code is set for - #,##0.00.

yes it's

Quote

That works very inefficiently and it causes an error in the userform

I thought it's possible by vba !

**royUK** · Oct 28th 2021

I answered you with what is causing the mismatch.

I also asked what your system currency is.

Good luck solving this one, because I('ll leave it to you.

show specific cuurency and numberformat in textbox on userform

Participate now!

Share