How to format cell depend on combo choice from another cell

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

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
 
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
 
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,

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
Click to expand...

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?
Click to expand...

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
 
Back
Top