Worksheet_Change Event to change currency symbol

[vivek.vivek]

Hello,

I have an excel where I will select a Currency dropdown (USD,EUR,GBP,SGD). On selecting any one of the currencies, I need to change the currency symbol in multiple cells where the amount is entered.

My Drop down column & Amount column looks like this;

CELL    LABEL               CURRENCY
B5      Currency            SGD
B23     Item amount (net!)  45.00
B29     Item amount (net!)  10.00



Thanks,
Vivek

[TnTinMN]

Use the ActiveX ComboBox. It has a change event that you can handle. Also the changes that it makes to the linked cell also triggers the WorkSheet_Change event. The Forms drop down does not do this.

[demon916]

Are you just wanting to display the currency symbol depending on the currency chosen from your dropdown in Cell 'B5'? Could you show the layout, as it would be in Excel, the way you want it displayed?

[demon916]

If you only want the symbols to change you could alway just place the below formula in an adjacent cell and format to hide the divide between them.
=IF($B$5="USD","$",IF($B$5="EUR","€",IF($B$5="GBP","£",IF($B$5="SGD","S$",""))))
Using this will change the symbol to what ever is selected in your dropdown in cell 'B5'.

[vivek.vivek]

Hello All,

Thanks for the response.
But my cells B23 & 29 are generated using another macro( To Add rows). So I cannot add a formula to the cell.

So I need a macro which can look for the call with "Item amount (net!)" contect and change symbol in the cell next right to it.

Thank You,
Vivek

[TnTinMN]

I tend to use named ranges a lot when doing spreadsheet programming so I decided to attach a sample file for you to look at. I named the dropdown cell "SelectedCurrency" and the search text is "CurrencyTag" on the Data tab.

The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intersection As Range
    Set intersection = Intersect([SelectedCurrency], Target)
    If intersection Is Nothing Then Exit Sub

    If intersection = [SelectedCurrency] Then
        With ActiveSheet.Range("b:b")
            Dim current As Range
            Dim first As String
            Set current = .Find([CurrencyTag].Value, LookIn:=xlValues)
            If (Not (current Is Nothing)) Then
                first = current.Address
                Do
                    current.Offset(0, 1).NumberFormat = "[$" & [SelectedCurrency].Value & "] #,##0.00"
                    Set current = .FindNext(current)
                Loop While (Not (current Is Nothing)) And (first <> current.Address)
            End If 'Not current Is Nothing
        End With 'ActiveSheet.Range("b:b")

    End If 'Intersect([SelectedCurrency], Target) = [SelectedCurrency]
End Sub 'Worksheet_Change

[demon916]

Are you wanting the symbol in the same cell as the dollar values or in a cell next to it?
Could we see where in the code, and at what point, or when you would expect to see the symbol associated to the cell? Maybe a visual layout of what you have or a sample of what you currently have. Are you using a dropdown listbox object or a cell validation rule? If it is a validation in a cell on the sheet are you using a range referenced list or a typed list, in your dropdown validation?

[TnTinMN]

I just realized that the file that I said I would attach did not get upload. Will try again.

[vivek.vivek]

Thank You, I will check this and get back to you!

[vivek.vivek]

I checked it. Is it possible to keep the dropdown as USD,EUR,SGD,GBP and change currency symbol to $,€ etc...for Item amount (net!)?

[TnTinMN]

Yes. Just change:

current.Offset(0, 1).NumberFormat = "[$" & [SelectedCurrency].Value & "] #,##0.00"

to a Select Case block.

    Select Case [SelectedCurrency].Value
        Case "USD"
            current.Offset(0, 1).NumberFormat = "$ #,##0.00"
        Case "EUR"
            current.Offset(0, 1).NumberFormat = "€ #,##0.00"

        etc.

    End Select

[demon916]

Thank you for the upload of your file "vivek.vivek", it makes things a bit more clear as to what your trying to get.
If you are still looking for alternative methods...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = [SelectedCurrency] Then
        Dim current As Range
        Dim FirstAddress As String
        Dim cFound As Range
        With ActiveSheet.Range("b:b")
            Set current = .Find(What:=[CurrencyTag], LookIn:=xlValues)
            If current Is Nothing Then Exit Sub
            FirstAddress = current.Address
            Do
                Set cFound = [Currencies].Find([SelectedCurrency], LookIn:=xlValues)
                If cFound Is Nothing Then Exit Do
                current.Offset(0, 1).NumberFormat = [CurrencyFormat].Cells(cFound.Row)
                Set current = .Find(What:=[CurrencyTag], After:=current, LookIn:=xlValues)
            Loop While Not current Is Nothing And current.Address <> FirstAddress
        End With
    End If
End Sub

You will have to change the layout of your "Data" sheet, and redefine "Currencies" as the entire column. As well as define the column which holds the formats which you want applied as "CurrencyFormat".

This code should allow you to Add, Delete or Modify your currencies list as well as the associated formats, with out having to redo or include new select Case currencies in your code. (See the attached / revised version of your example for reference.)

In the Revised Workbook Only Colunms 'B' ("Currencies") and 'C' ("CurrencyFormat") are important to the code the rest are purely informational. (I tend to get a little carried away...)

[vivek.vivek]

awesome...u worked a lot for the Data Sheet.ah!

Thanks a lot!

[vivek.vivek]

i run into some other problem now...I have some other dropdown in the same sheet to add or delete rows. So if i change the drop down , it is getting stuck and couldnot perform the action.

[demon916]

Does your second dropdown move or effect the currency selection dropdown at all?
What type of error do you get and what is actually happening when the other dropdown is used?
You may wish to try this slight code change:

From:
If Target = [SelectedCurrency] Then

To:
If Target.Address = [SelectedCurrency].Address Then

Not sure how much this will help... but until I know what is going on during the other drop down... But this should allow a line insert or a 'Target' object other than a single cell evaluation, (IE. Range or Cell).

[vivek.vivek]

I tried with .Address. Now it is affecting the currency change.
Error: Unable to set the NumberFormat property of Range Class

[demon916]

iAnyway to send a more complete sample of your workbook? You only changed it to include '.Address' in that one line of code, correct?

[vivek.vivek]

my sheets are password protected. That is why I am getting the error. I got it corrected.
Thank you for all your assistance.