How to format cell depend on combo choice from another cell

[AA Arens]

Hi I have tried to find out from earlier messages but failed.

I am looking for the following

I need to fill in payments from our customers. In the left column I
choose via combo box the currency of the invoice. How to get the cell
account formatting (format cell -> Number ->Accounting) from all cells
right of it in the currency that I choose from the combo?

So:
A1 B1 C1 etc.
Combo: Cell format:
US Dollar USD USD
Euro EUR EUR

Thank you

Bart
Excel 2003

 

[Pete_UK]

You can't change the format of a cell with a function or formula - you
would need some VBA to do that.

The closest I could suggest is to use the TEXT function like this:

=IF(B1="USD",TEXT(A1,"$#,##0.00"),IF(B1="GBP",TEXT(A1,"£#,##0.00"),TEXT(A1,"E#.##0.00")))

where E is the Euro symbol. The problem with this is that it produces
text values, so you will have to align to the right, and you can't use
them directly in arithmetic operatons (but you have A1, anyway, which
you can use for that).

Hope this helps.

Pete

 

[stew]

Hi,

Only pseudo code I'm afraid as I'm not an expert on reading values
from Combo boxes, but using "Tools", "Macro", "Record New Macro" and
then changing the format of a cell suggests that something along the
following lines will work:

Private Sub ComboBox1_Change()

Range("B1").Select 'or which ever cell you want formatting

if combo value = usd
Selection.NumberFormat = _
"_-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]*
""-""??_ ;_-@_ "
elseif combo value = EUR
Selection.NumberFormat = _
"_-[$EURO-410] * #,##0.00_-;-[$EURO-410] * #,##0.00_-;_-[$EURO-410] *
""-""??_-;_-@_-"
end if

End Sub

 

[AA Arens]

Hi,

Only pseudo code I'm afraid as I'm not an expert on reading values
from Combo boxes, but using "Tools", "Macro", "Record New Macro" and
then changing the format of a cell suggests that something along the
following lines will work:

Private Sub ComboBox1_Change()

Range("B1").Select 'or which ever cell you want formatting

if combo value = usd
Selection.NumberFormat = _
"_-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]*
""-""??_ ;_-@_ "
elseif combo value = EUR
Selection.NumberFormat = _
"_-[$EURO-410] * #,##0.00_-;-[$EURO-410] * #,##0.00_-;_-[$EURO-410] *
""-""??_-;_-@_-"
end if

End Sub

Hi,

Thank you both for the input. I wanted to try both solutions but don't
know under which place I have to paste the code in the VB editor. I
know the VB section in excel.
Under worksheet Selection Change? And how to define the combo box name
in my sheet (Combobox1). Is that the name under (Menu) Insert -> Name -

Define?

And, how to have the currency assigned to a range off cells (B5 - B10)
in stead of one (B1)? (for both given solutions)

Both solutions should work in my opinion.

Thank you from Jakarta.

Bart