How to format cell depend on combo choice from another cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 22 Jan 2008 07:45 GMT

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

Reply to this Message

Pete_UK - 22 Jan 2008 09:28 GMT

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 I have tried to find out from earlier messages but failed.
>
[quoted text clipped - 15 lines]
> Bart
> Excel 2003

Reply to this Message

stew - 22 Jan 2008 12:52 GMT

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

Reply to this Message

AA Arens - 26 Jan 2008 14:27 GMT

> Hi,
>
[quoted text clipped - 18 lines]
>
> 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

Reply to this Message