Update number formatting automatically

[myfocus4sale]

Hi
I have tried to find information on this using the search feature but
so far no luck. I have a expense spreadsheet that needs to be able to
display numbers in different currencies. The currency used in each
worksheet is selected from a drop down list. Is it possible to write
a macro that will update a particular range of cells to match the
currency selected from the drop down list? The list is generated from
a separate worksheet which contains the currency formatting in the
cell to the right. I was thinking a macro using a lookup function
could possibly do the trick.

Any help would be greatly appreciated.

Regards

Marcello

 

[Bernie Deitrick]

Marcello,

A lot depends on what exactly you meant by:

"The list is generated from a separate worksheet which contains the currency formatting in the cell
to the right."

Let's say that you have a list of currency format names in one column, and the format string as text
in the next (to the right). And let's say that you have three named ranges: one with the currency
values that need to be formatted (CurrVal), one with the format name and format strings (CurrFormat)
and a single cell with the dropdown (CurrFormSel)

Your macro could be as easy as:

Sub SetCurrFormat()
Range("CurrVal").NumberFormat = Application.Vlookup(Range("CurrFormSel").Value, _
Range("CurrFormat"),2,False)
End Sub

You could also fire this macro through the change event, so that when the value of CurrFormSel
changes, the formats are updated. Copy this code, and put it into the sheet's codemodule.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Name.Name <> "CurrFormSel" Then Exit Sub
SetCurrFormat
End Sub


HTH,
Bernie
MS Excel MVP

 

[myfocus4sale]

Bernie,

Thanks for the information. The code works well but I have hit upon a
couple of problems. When changing other cells on the worksheet the
change event gives an error, Run-time error '1004': Application-
defined or object-defined error.

Secondly the format strings contain letters that Excel seems to
interpret. For example the Algerian Dinar has the string DZD. Excel
changes the format of the cells to a date format. Is there any way
around this?

Thanks again.

Marcello

 

[myfocus4sale]

Further to my recent message, I have change the value of the strings
to 0" DZD". This seems to have corrected the problem with the formats
and allows a space between the string and the number. Would you
recommed using this method?