Using value in a cell as part of formating ?

[SHEETMAGIC]

Hello,
I would like to know if there is anyway to include the value in a cell in my CUSTOM formating.

What I have is a cell that show a currency value. I need it to be in the standard format of " $ 5.00 " yet I need the $ to show the correct currency symbol as specifid in another cell. So let's say cell J15 has the value in it, like "US$" or the euro symbol, etc. I need that value to be used as the currency symbol in my cell with "$ 5.00".


Can this be done?

With thanks,

Jonathan

 

[erik.van.geit]

Hello, Jonathan,

without ever having done this, it's not so difficult to find a solution
record a macro changing the symbol
then replace "$" with " & CS & "
where CS is the symbol, retreived from a cell (A1)
in fact it's just playing a bit with some strings

Sub change_currencysymbol()
'Erik Van Geit
'051118 0035
Dim CS As String    'currency symbol

CS = Range("A1")
Range("data").NumberFormat = _
"_-[$" & CS & "-409]* #,##0.00_ ;_-[$" & CS & "-409]* -#,##0.00 ;_-[$" & CS & "-409]* ""-""??_ ;_-@_ "

End Sub

kind regards,
Erik

 

[erik.van.geit]

couldn't resist
using this your format will be changed automatically

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'change_currencysymbol
'Erik Van Geit
'051118 0035
Dim CS As String    'currency symbol
Dim CurrencyCell As Range

Set CurrencyCell = Range("A1")

If Target <> CurrencyCell Then Exit Sub

CS = CurrencyCell
Range("data").NumberFormat = _
"_-[$" & CS & "-409]* #,##0.00_ ;_-[$" & CS & "-409]* -#,##0.00 ;_-[$" & CS & "-409]* ""-""??_ ;_-@_ "

End Sub

 

[SHEETMAGIC]

Oh Eric.
How often have you come through not only with the basic answer but then with a super-duper solution that goes beyond what I had imagined getting.

I will test this out shortly and let you know the result.

Many many thanks. I greatly appreciate your great assistance and generous attitude.

Blessings,

Jonathan

 

[erik.van.geit]

You're welcome Jonathan,
(one of my sons has same name )


this might be not clear:

then replace "$" with " & CS & "

in fact it has to be
then replace $ (no quotes) with " & CS & " (quotes included !)

playing with strings is a thing we have to do carefully

Range("A1:A25") = 123

C = 1
Range("A" & C & ":A25") = 123

best regards,
Erik

 

[SHEETMAGIC]

Hi Eric,
I have at long last sat down to look into this and apply it to my worksheet.

I think perhaps I should explain my situation a little better, as this doesn't quite fit.

What I have is

A          B               C             D          E
ID        Ord Cur       Prod $     Ship $ ....
----------------------------------------	
1         US$	         78.00       44
2         €	           104.85     44
3         US$	         624.00     33

Off to the right are three other currency value fields.
What I'd like is to be able to get any one of them to show the correct currency identifier (US$, €, etc) as set in B on that same row.

Is that possible or too difficult?

I will use the code you have given me so far on my other INVOICE sheet, as I only have one instance of everything there (i.e. one currency type set, and one currency value that needs to use it). Whereas on the ORDERS sheet I have an instance on every row.

Blessings,

Jonathan

 

[erik.van.geit]

Jonathan,

there is no "numberformat-change-event"
so this code will fail if there is no real change in the cells
edit the range: Range(Cells(cell.Row, "D"), Cells(cell.Row, "F")).NumberFormat = cell.NumberFormat

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG As Range
Dim cell As Range
Dim CurrencyRange As Range

Set CurrencyRange = Columns(2)

Set RNG = Intersect(Target, CurrencyRange)
If RNG Is Nothing Then Exit Sub

For Each cell In RNG
Range(Cells(cell.Row, "D"), Cells(cell.Row, "F")).NumberFormat = cell.NumberFormat
Next cell
End Sub

another problem is the undo is not anymore available when a change is made in column 2

therefore I would allow currency changes only using a dropdown changing the selected range-numberformat
this is nice:

Private Sub listbox1_Click()
'change_currencysymbol
'Erik Van Geit
'051118 0035
Dim CS As String    'currency symbol
Dim RNG As Range

Set RNG = Intersect(Selection, Columns(2))
    If RNG Is Nothing Then
    MsgBox "Please select range in column 2", 48, "SELECTION ERROR"
    Exit Sub
    End If
        If RNG.Areas.Count > 1 Then
        MsgBox "Please only select one area", 48, "SELECTION ERROR"
        Exit Sub
        End If

CS = listbox1
RNG.NumberFormat = _
"_-[$" & CS & "-409]* #,##0.00_ ;_-[$" & CS & "-409]* -#,##0.00 ;_-[$" & CS & "-409]* ""-""??_ ;_-@_ "
RNG.Offset(0, 2).Resize(RNG.Count, 3).NumberFormat = RNG.NumberFormat
listbox1 = ""
End Sub

edit the blue part
RNG.Offset(0, 2).Resize(RNG.Count, 3).

I hope you will enjoy this
kind regards,
Erik