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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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 *


"ExcelMonkey " wrote in message
...
I want to pass a number format to a variable. The following code is not
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 with
either a lack of quotes or brackes. Or my variable declaration is of
the wrong type.

Can anyone tell me what wrong here?

Thank-you.


---
Message posted from http://www.ExcelForum.com/

it's a string.. you'll have to pad it with double quotes.
Furthermo 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 wrote:

I want to pass a number format to a variable. The following code is
not
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 with
either a lack of quotes or brackes. Or my variable declaration is of
the wrong type.

Can anyone tell me what wrong here?

Thank-you.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

Thank

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
I tried that Rob. My cell has the value "$100" in it. When applying
the format I got the following in the cell:

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

Somethings wrong here.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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


ExcelMonkey wrote:

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

Thanks


---
Message posted from http://www.ExcelForum.com/

"ExcelMonkey " wrote in message
...
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 *

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

--
Message posted from http://www.ExcelForum.com

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 *


"ExcelMonkey " wrote in message
...
So this works Rob. But what happens if I now have two separate cells
which need to trigger two separate subroutines. That is, if the value
in TargetCurrencyType changes then the TargetCurrencyPick routine is
run (as outlined below). And if the HostCurrencyType value changes,
then another routine called HostCurrencyPick is run. I need to find a
way to incorporate the new cell (HostCurrencyType) into the test and
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 Sub


---
Message posted from http://www.ExcelForum.com/