Format Numbers

[praf007]

Is there any way I can add this format permanently in Excel/Format/Custom instead of manually adding open/close brackets

 

[Micron]

Maybe I don't understand the question, because I typed what you show and chose OK, then applied the format to a cell. When I open the dialog again, what I entered is still there.

 

[Joe4]

Maybe I don't understand the question, because I typed what you show and chose OK, then applied the format to a cell. When I open the dialog again, what I entered is still there.
View attachment 120106

Micron,

I did some testing, and what you said is true, but only within that particular workbook.
It does not apply/show in other workbooks you may have open (or if you create a new one).
I think maybe they are looking for a permanent solution that is always available in all workbooks.

praf007,

If that is indeed the case, one way would be to create a short little macro that does that, and save it in your Personal Macro Workbook.
The code is a real simple one-line procedure, like this:

Sub MyCustomFormat()
    Selection.NumberFormat = "#,##0.00;[Red](#,##0.00)"
End Sub

So all you would have to add after you add it to your Personal Macro Workbook is first select the range you want to apply it to, and then call/run it from the Macro Listing (View -> Macros -> View Macros), i.e.

(as you can see, I have a lot of other macros in there - if this is your first, it would be the only one listed).

Macros stored in your Personal Macro Workbook are always available to you while you are logged in to that particular computer.

See here for information on the Personal Macro Workbook: Excel Personal Macro Workbook: how to create, use and share

 

[Micron]

It seems I don't have a personal.xls (xlsb or otherwise). I was wondering if you put the format in that wb would it be available to all open wb's? Can't test it though. At one time I thought it was automatic to have one but it seems that now it isn't.

 

[Micron]

What is the difference in formatting between the format string in the OP
#,##0.00;[Red](#,##0.00)
vs what's already there (other than to say there is an underscore)
#,##0.00;_[Red](#,##0.00)

I take it that Excel comes with the latter format but I don't know if there is any difference between how those two would work.

 

[Joe4]

It seems I don't have a personal.xls (xlsb or otherwise). I was wondering if you put the format in that wb would it be available to all open wb's? Can't test it though. At one time I thought it was automatic to have one but it seems that now it isn't.

If you do not have one, you can easily create one by doing the following steps:
Go to View -> Macros -> Record Macro
and then select the "Personal Macro Workbook" option, as shown below:

Then you can go back to View -> Macros -> Record Macro and click "Stop Recording".

Then if you view the VB Editor, you should be able to see the Personal Macro Workbook.

 

[Micron]

Yes, I saw that, thanks. Later I will try to learn the difference between the various types. I suppose personal.xls is deprecated. Then there seems to be .xlsm (which is what I think you're referring to) xlsb and xlb.

 

[Joe4]

Yes, just like all the defaults were changed from "xls" to "xlsx", "xlsm", "xlsb" many years ago, the same is true for the Personal Macro Workbook.
Note that the default extension for Personal Macro Workbooks is "xlsb".
If you have one, all the code in that is always available to you whenever you are logged into Excel on that computer.

See here for the various file types: File formats that are supported in Excel - Microsoft Support

 

[praf007]

Maybe I don't understand the question, because I typed what you show and chose OK, then applied the format to a cell. When I open the dialog again, what I entered is still there.
View attachment 120106

Hi Micron, As Joe4 mention the format only stays in the same workbook. I want that specific format permanent when i go into Customs so i do not have to amend manually. Hope this helps. thank you everyone who replied, much appreciated. Will try some of the macros as well

 

[praf007]

Micron,

I did some testing, and what you said is true, but only within that particular workbook.
It does not apply/show in other workbooks you may have open (or if you create a new one).
I think maybe they are looking for a permanent solution that is always available in all workbooks.

praf007,

If that is indeed the case, one way would be to create a short little macro that does that, and save it in your Personal Macro Workbook.
The code is a real simple one-line procedure, like this:

Sub MyCustomFormat()
    Selection.NumberFormat = "#,##0.00;[Red](#,##0.00)"
End Sub

So all you would have to add after you add it to your Personal Macro Workbook is first select the range you want to apply it to, and then call/run it from the Macro Listing (View -> Macros -> View Macros), i.e.
View attachment 120107

(as you can see, I have a lot of other macros in there - if this is your first, it would be the only one listed).

Macros stored in your Personal Macro Workbook are always available to you while you are logged in to that particular computer.

See here for information on the Personal Macro Workbook: Excel Personal Macro Workbook: how to create, use and share

Hi Joe4, yes you are correct, i am looking for a permanent solution as you rightly say it does not come up on new work book