Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Currency Formatting (Excel 2000)

    Hallo all

    I use a lot of different currency calculations in my work. Many times I use the same layout with new information or a different currency. My problem is that I always have to reformat the cells to display the correct currency and the correct number of decimal places.

    I was wondering if it is possible to achieve this by having 2 drop down boxes, one where you choose the currency and the other to choose the number of decimal places to display. I have attached a small workbook for explanation. If you change your choice in the blue drop down boxes the output in the yellow boxes must change automatically.

    Is this possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Currency Formatting (Excel 2000)

    Right-click the sheet tab.
    Select "View code" from the popup menu.
    Copy the following code into the worksheet module:
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strFormat As String
    If Not Intersect(Target, Range("F4:F5")) Is Nothing Then
    strFormat = """" & Range("F4") & """* #,##0"
    If Range("F5") > 0 Then
    strFormat = strFormat & "." & String(Range("F5"), "0")
    End If
    Range("E10:F13").NumberFormat = strFormat & ";<!t>[Red]<!/t>(" & strFormat & ")"
    End If
    End Sub
    </code>
    Changing the value of F4 or F5 will have immediate effect.

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Currency Formatting (Excel 2000)

    Hans
    Thank you very much, it works perfect.

    If I may ask one more question, how can the code be change to accomodate rounding?

    Your help is greatly appreciated.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Currency Formatting (Excel 2000)

    See attached version

    (You had inserted a row, invalidating the code. Unlike formulas, code will not adjust itself if you insert or delete cells/rows/columns)

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Currency Formatting (Excel 2000)

    Hans

    Thank you for pointing out my error.

    Thank you also for the extra code, I really appreciate it.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •