show specific cuurency and numberformat in textbox on userform

  • 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
    1. s = "LYD"


    theses codes cuase the problem

    is there any idea to achieve that?

    Files

    • CRR.xlsm

      (49.41 kB, downloaded 21 times, last: )
  • 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
    1. Private Sub TextBox6_Change()
    2. Me.TextBox6.Value = FormatCurrency(TextBox6.Value)
    3. End Sub
    4. Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    5. Me.TextBox7.Value = FormatCurrency(CDbl(TextBox6.Value) * CInt(Me.TextBox5.Value))
    6. End Sub

    What is the combobox supposed to be doing?

  • You only need this in TextBox6


    Code
    1. Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    2. With Me
    3. .TextBox6.Value = FormatCurrency(.TextBox6.Value)
    4. .TextBox7.Value = FormatCurrency(CDbl(.Value) * CInt(.Value))
    5. End With
    6. End Sub
  • 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

  • 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.

  • 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.

  • 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 !

  • 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.