1,105,288 Community Members

Worksheet_Change Event to change currency symbol

Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
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
Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
0
 

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.

Member Avatar
demon916
Newbie Poster
13 posts since Sep 2009
Reputation Points: 13 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
demon916
Newbie Poster
13 posts since Sep 2009
Reputation Points: 13 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
0
 

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
Member Avatar
demon916
Newbie Poster
13 posts since Sep 2009
Reputation Points: 13 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
0
 

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

Attachments Currency_Format_Setter.zip (12.37KB)
Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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!)?

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
0
 

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
Member Avatar
demon916
Newbie Poster
13 posts since Sep 2009
Reputation Points: 13 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Attachments Currency_Format_Setter.zip (20.15KB)
Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Thanks a lot!

Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
demon916
Newbie Poster
13 posts since Sep 2009
Reputation Points: 13 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
demon916
Newbie Poster
13 posts since Sep 2009
Reputation Points: 13 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
vivek.vivek
Newbie Poster
16 posts since May 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article