Passing Number Format to a Variable

[ExcelMonkey]

I want to pass a number format to a variable. The following code is no
working:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = [($"US" #,##0_);[Red]($#,##0)]

I am getting a type mismatch error. I think there is a problem wit
either a lack of quotes or brackes. Or my variable declaration is o
the wrong type.

Can anyone tell me what wrong here?

Thank-you

 

[ExcelMonkey]

Ok the format was wrong is should be

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = "$US #,##0_);[Red]($#,##0)"

However when I try to do this:

Range("IncomeStatement").NumberFormat = CurrencyFormat

I get an error stating:

"Unable to set number format property on Range class. "

What am I doing wrong

 

[Rob Bovey]

Try it like this:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = "[($""US"" #,##0_);[Red]($#,##0)]"
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[keepitcool]

it's a string.. you'll have to pad it with double quotes.
Furthermore: in vba the [] are shortnotation for the evaluate method

CurrencyFormat = "[$$-409]#.##0,00;[Red][$$-409]#.##0,00"

afaik the languageID(409) is not used with excel97

Following will always give you "US$", the \ denotes "literal
interpratation of the following character.
Else the character is interpreted agains local settings (
and for example the U means Hour(s) in Dutch)

CurrencyFormat = "\U\S\$#.##0,00;[Red]\U\S\$#.##0,00"


See VBA help on NumberFormatting







keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[ExcelMonkey]

I tried that Rob. My cell has the value "$100" in it. When applyin
the format I got the following in the cell:

US #,##0_);[Red($100)]

Somethings wrong here

 

[ExcelMonkey]

yeah that works.Thanks. How do I get rid of the decimal points?

Thank

 

[Rob Bovey]

I neglected to notice that your original number format was invalid. Me
bad. As keepitcool demonstrated, you need to escape the U and S characters
somehow because otherwise Excel tries to interpret them as formatting
tokens. You can either do it with backslashes as he demonstrated or you can
surround the US in double quotes as you were trying to do originally. For
example, this will work:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = "$""US"" #,##0_);[Red]($#,##0)"
Sheet1.Range("A1").NumberFormat = CurrencyFormat
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[ExcelMonkey]

So something really odd is happening here. When I use the code that yo
both submitted "$""US"" #,##0_);[Red]($#,##0)", it will not work fo
the US dollar option. See below. I have 5 currencies options than ca
be picked from a cell called "CurrencyType". They all work except th
US dollar option. Also note that the CDN dollar option has the exac
same code as the US Dollar option but it works. Can't figure out wh
one would work while the other would not. When I use the US Dollar
option and I pass the cursor over CurrencyFormat is say
CurrencyFormat = "False". but it does not do this for the CDN Dolla
option????? See Below.



Sub CurrencyPick()
Dim Currencies As String
Dim CurrencyFormat As String

Currencies = Range("CurrencyType")


Select Case Currencies
Case Is = "USD"
CurrencyFormat = CurrencyFormat = "$""US"" #,##0_);[Red]($#,##0)"

Case Is = "CDN"
CurrencyFormat = "$""CDN"" #,##0_);[Red]($#,##0)"

Case Is = "GBP"
CurrencyFormat = "£ #,##0_);[Red]($#,##0)"

Case Is = "Euros"
CurrencyFormat = "€ #,##0_);[Red]($#,##0)"

Case Is = "Yen"
CurrencyFormat = "¥ #,##0_);[Red]($#,##0)"

End Select

Range("IncomeStatement").NumberFormat = CurrencyFormat
Range("BalanceSheet").NumberFormat = CurrencyFormat
Range("CashflowStatement").NumberFormat = CurrencyFormat


End Su

 

[ExcelMonkey]

I found the error on my end:

Select Case Currencies
Case Is = "USD"
CurrencyFormat = CurrencyFormat = "$""US"" #,##0_);[Red]($#,##0)"


Thanks again for your brilliance!!!! Works fine now

 

[ExcelMonkey]

One more question. If I want to trigger this procedure when the cel
value in Range("CurrencyType") change how do I do this? I know it is
Worksheet_Change event. But how do I get it to trigger only when tha
specific cell changes?

Thank

 

[keepitcool]

ah.. oops... copied that the dialogbox, so it's the NumberFormatLocal
string... and since I live in Holland it's not what YOU want.


Just reverse the , and . and you should be fine.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Rob Bovey]

One more question. If I want to trigger this procedure when the cell
value in Range("CurrencyType") change how do I do this? I know it is a
Worksheet_Change event. But how do I get it to trigger only when that
specific cell changes?

You don't. The Worksheet_Change event fires when the value of any range
on the worksheet changes. The event passes you a Target argument that is the
range that changed. You have to determine whether this is the cell you're
looking for. An example of how this works is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTest As Range
On Error Resume Next
Set rngTest = Intersect(Target, Range("CurrencyType"))
On Error GoTo 0
If Not rngTest Is Nothing Then
''' The value is Range("CurrencyType") changed.
Range("CurrencyType").NumberFormat = "SomeFormat"
End If
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[ExcelMonkey]

So this works Rob. But what happens if I now have two separate cell
which need to trigger two separate subroutines. That is, if the valu
in TargetCurrencyType changes then the TargetCurrencyPick routine i
run (as outlined below). And if the HostCurrencyType value changes
then another routine called HostCurrencyPick is run. I need to find
way to incorporate the new cell (HostCurrencyType) into the test an
the new routine (HostCurrencyPick) if that test is true.

Thanks again,




Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTest As Range
On Error Resume Next
Set rngTest = Intersect(Target, Range("TargetCurrencyType"))
On Error GoTo 0
If Not rngTest Is Nothing Then
''' The value is Range("CurrencyType") changed.
Call TargetCurrencyPick
End If
End Su

 

[Rob Bovey]

If you need to react to changes in multiple cells you just string a
series of checks, one after the other. Note that if you're going to be doing
anything that will change a value of a cell on that same worksheet you need
to disable events so you don't end up calling the change event recursively.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngTest As Range

''' Disable Excel events.
Application.EnableEvents = False

''' Determine if the first cell changed.
On Error Resume Next
Set rngTest = Intersect(Target, Range("TargetCurrencyType"))
On Error GoTo ErrorHandler
If Not rngTest Is Nothing Then
Call TargetCurrencyPick
End If

''' Determine if the second cell changed.
On Error Resume Next
Set rngTest = Intersect(Target, Range("HostCurrencyType"))
On Error GoTo ErrorHandler
If Not rngTest Is Nothing Then
Call HostCurrencyPick
End If

ErrorHandler:
''' Error handling is set up like this so events
''' get enabled no matter what.
Application.EnableEvents = True
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *