PDA

View Full Version : Very Beginner Question Excel 2003 or 2007



FinEnergyMan
02-24-2009, 02:42 PM
Excel 2003 or 2007.
Couldn't quite find the answer to this already posted. When I click the currency button, it formats the cell to $ aligned to the left with 2 decimals. I would like it to have the currency symbol with no decimals and no space between the dollar sign and the cell value. I tried messing with regional settings but this doesn't get rid of the $ sign left aligned. What is the best way to setup a button that has currency formatting with no decimals and not the accounting format.

Thank you in advance for your help.

Simon Lloyd
02-24-2009, 03:03 PM
Excel 2003 or 2007.
Couldn't quite find the answer to this already posted. When I click the currency button, it formats the cell to $ aligned to the left with 2 decimals. I would like it to have the currency symbol with no decimals and no space between the dollar sign and the cell value. I tried messing with regional settings but this doesn't get rid of the $ sign left aligned. What is the best way to setup a button that has currency formatting with no decimals and not the accounting format.

Thank you in advance for your help.Have you tried Right click the cell/column Format Cells>Currency and select how many decimals there, figures are always right aligned unless you are using custom formatting!

mdmackillop
02-24-2009, 03:04 PM
Look at Format Cells/Number/Custom. You can set $0 or $#,##0 if you want commas.

FinEnergyMan
02-24-2009, 03:11 PM
Thanks for the posts.
How do I create a button that does this, essentially I would like a button that you press like the $ button that uses currency formatting not the standard accounting formatting.

Bob Phillips
02-24-2009, 04:15 PM
There is already such a button on the Formatting toolbar, it is called Currency.

FinEnergyMan
02-24-2009, 04:18 PM
The currency button ($) results in formatting that looks like this : $ 55
Which is using the "Accounting" formatting. I would like it to use currency formatting, $55. file:///C:/DOCUME%7E1/jimin/LOCALS%7E1/Temp/moz-screenshot.jpg

Bob Phillips
02-24-2009, 04:27 PM
Create a macro that sets that format



Public Sub MyFormat()
Selection.NumberFormat = "$#,##0"
End Sub


and re-assign the currency button to that macro



application.CommandBars.FindControl(id:=1643).OnAction="MyFormat"

FinEnergyMan
02-24-2009, 05:01 PM
Thanks for the post XLD. Where do I place peice of code to re-assign the currency button ?

Bob Phillips
02-25-2009, 01:09 AM
I would put it in a workbook open event for that workbook.

FinEnergyMan
02-25-2009, 10:02 AM
I have the following code
Private Sub Workbook_Open()
Application.CommandBars.FindControl(ID:=1643).OnAction = "MyFormat"
End Sub
Public Sub MyFormat()
Selection.NumberFormat = "$#,##0"
End Sub

When I click the "$" button it still puts the number in format of $ 5

Bob Phillips
02-25-2009, 10:13 AM
The Workbook_Open code should be in ThisWorkbook, the other should be in a standard code module.

FinEnergyMan
02-25-2009, 10:24 AM
Moved it to ThisWorkbook. Now receiving the following message:
Run-time error '91':
Object variable or With block variable not set

Bob Phillips
02-25-2009, 10:34 AM
Best to post your workbook, so we can see what's what.

FinEnergyMan
02-25-2009, 10:39 AM
Attached.

Kenneth Hobs
02-25-2009, 10:41 AM
You could just put the Sub in a Module and assign it to a key combination or a toolbar button or whatever.
Sub MyFormat()
Selection.NumberFormat = "$#,##0"
End Sub

Bob Phillips
02-25-2009, 10:56 AM
Attached.

As I said earlier ... the other (MyFormat) should be in a standard code module ...

Bob Phillips
02-25-2009, 03:07 PM
As requested

FinEnergyMan
02-25-2009, 03:29 PM
When opening the attachment still receing the above mentioned error message. When I choose debug it takes me to the following line :
Application.CommandBars.FindControl(ID:=1643).OnAction = "MyFormat"

Bob Phillips
02-25-2009, 03:55 PM
What Excel version?

FinEnergyMan
02-25-2009, 04:25 PM
2003

Simon Lloyd
02-25-2009, 06:30 PM
Have you made the currency button visible? by default in 2003 it does not show in the menubar unless you go to toolbar options and choose it from there.

Bob Phillips
02-26-2009, 01:26 AM
2003

Works fine for me in 2003