How to programmatically access Number Format String in the Format

  • Thread starter Thread starter xiao
  • Start date Start date
X

xiao

Hi,
We know that Excel allows us to format a cell in the “Format Cells Dialogâ€.
Then in the program, we can get the format string using
((Range)ActiveSheet.Cells[1, 1]).NumberFormat.
My question is:
Is there a way to programmatically access all the Number Format String in
the Format Cells Dialog without selecting each format manually?
Thanks,
 
If you mean pull up some kind of menu, the answer is no. The dialogs method
is the only way to do that. However, you can use NumberFormat to set the
format to whatever format is available in the dialog box by simply specifying
the format name for certain ones such as "General", "Accounting", etc. Or you
can set cutom formats such as $##,##0; ( $##,##0) to get even dollars
displayed. NumberFormat is one of the more versatile of the VBA facilities.
 
JLGWhiz,
Thanks a lot for your reply.
My question is:
can we list all the available NumberFormat in the Format Cells Dialog? In
other words, can we use a foreach() loop to display all the default
NumberFormat supported by Excel without openning the Format Cells Dialog? If
yes, could you show me some code to do so?
Thx.


JLGWhiz said:
If you mean pull up some kind of menu, the answer is no. The dialogs method
is the only way to do that. However, you can use NumberFormat to set the
format to whatever format is available in the dialog box by simply specifying
the format name for certain ones such as "General", "Accounting", etc. Or you
can set cutom formats such as $##,##0; ( $##,##0) to get even dollars
displayed. NumberFormat is one of the more versatile of the VBA facilities.

xiao said:
Hi,
We know that Excel allows us to format a cell in the “Format Cells Dialogâ€.
Then in the program, we can get the format string using
((Range)ActiveSheet.Cells[1, 1]).NumberFormat.
My question is:
Is there a way to programmatically access all the Number Format String in
the Format Cells Dialog without selecting each format manually?
Thanks,
Click to expand...
Click to expand...
 
No, you cannot access the NumberFormat options using a loop. The Dialogs box
is the only way that I know of to display and select options. Otherwise, you
have to explicitly assign the NumberFormat as a specific type.

xiao said:
JLGWhiz,
Thanks a lot for your reply.
My question is:
can we list all the available NumberFormat in the Format Cells Dialog? In
other words, can we use a foreach() loop to display all the default
NumberFormat supported by Excel without openning the Format Cells Dialog? If
yes, could you show me some code to do so?
Thx.


JLGWhiz said:
If you mean pull up some kind of menu, the answer is no. The dialogs method
is the only way to do that. However, you can use NumberFormat to set the
format to whatever format is available in the dialog box by simply specifying
the format name for certain ones such as "General", "Accounting", etc. Or you
can set cutom formats such as $##,##0; ( $##,##0) to get even dollars
displayed. NumberFormat is one of the more versatile of the VBA facilities.

xiao said:
Hi,
We know that Excel allows us to format a cell in the “Format Cells Dialogâ€.
Then in the program, we can get the format string using
((Range)ActiveSheet.Cells[1, 1]).NumberFormat.
My question is:
Is there a way to programmatically access all the Number Format String in
the Format Cells Dialog without selecting each format manually?
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top