| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting numbers
Hello,
When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
Not sure how you want to implement this, but the following should give you
an idea how to proceed... Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" -- Rick (MVP - Excel) "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
Thank you, Rick
When I try this: moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####" there is a compile error: "Expected: identifier or bracketed expression" I wonder, if there is way to "preformat" the whole column, so when the numbers are entered into it they will be automatically formatted as the phone numbers? Thanks, Fred "Rick Rothstein" wrote in message ... Not sure how you want to implement this, but the following should give you an idea how to proceed... Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" -- Rick (MVP - Excel) "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
Sorry, my fault. Syntax error.
When I try this: moExcelWS.Cells(3,3).NumberFormat = "[<=9999999]###-####;(###) ###-####" the response is (True/False) which indicates to me that it is comparing the formats not introducing it. Fred "fred" wrote in message ... Thank you, Rick When I try this: moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####" there is a compile error: "Expected: identifier or bracketed expression" I wonder, if there is way to "preformat" the whole column, so when the numbers are entered into it they will be automatically formatted as the phone numbers? Thanks, Fred "Rick Rothstein" wrote in message ... Not sure how you want to implement this, but the following should give you an idea how to proceed... Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" -- Rick (MVP - Excel) "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####"
First off, I assume the missing "dot" that should be in front of "NumberFormat" is a typo. What is moExcelWS... a variable containing the worksheet name or a variable containing a reference to the worksheet? If the former, you would need to make it an argument to the Worksheet object call... Worksheets(moExcelWS).Cells(3,3).NumberFormat=.... etc I wonder, if there is way to "preformat" the whole column, so when the numbers are entered into it they will be automatically formatted as the phone numbers? Do you mean in code (as opposed to selecting the column and using Cell/Format on the worksheet directly)? If so, you should be able to do something like this in code... Columns("G").NumberFormat = "[<=9999999]###-####;(###) ###-####" Where you would change the column reference to suit your needs and preface it with a worksheet reference. -- Rick (MVP - Excel) "fred" wrote in message ... Thank you, Rick When I try this: moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####" there is a compile error: "Expected: identifier or bracketed expression" I wonder, if there is way to "preformat" the whole column, so when the numbers are entered into it they will be automatically formatted as the phone numbers? Thanks, Fred "Rick Rothstein" wrote in message ... Not sure how you want to implement this, but the following should give you an idea how to proceed... Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" -- Rick (MVP - Excel) "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
I'm not sure what you mean by "the response". If you execute that line, as
written, it should perform the format operation... if you include as part of a larger line of code, well, then I'd have to see exactly how you are trying to use it in order to make a guess at what is happening. -- Rick (MVP - Excel) "fred" wrote in message ... Sorry, my fault. Syntax error. When I try this: moExcelWS.Cells(3,3).NumberFormat = "[<=9999999]###-####;(###) ###-####" the response is (True/False) which indicates to me that it is comparing the formats not introducing it. Fred "fred" wrote in message ... Thank you, Rick When I try this: moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####" there is a compile error: "Expected: identifier or bracketed expression" I wonder, if there is way to "preformat" the whole column, so when the numbers are entered into it they will be automatically formatted as the phone numbers? Thanks, Fred "Rick Rothstein" wrote in message ... Not sure how you want to implement this, but the following should give you an idea how to proceed... Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" -- Rick (MVP - Excel) "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
Thank you, Rick
moExcelWS.Columns("c").NumberFormat = "[<=9999999]###-####;(###) ###-####" does work! It formats all values in that column. Fred "Rick Rothstein" wrote in message ... moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####" First off, I assume the missing "dot" that should be in front of "NumberFormat" is a typo. What is moExcelWS... a variable containing the worksheet name or a variable containing a reference to the worksheet? If the former, you would need to make it an argument to the Worksheet object call... Worksheets(moExcelWS).Cells(3,3).NumberFormat=.... etc I wonder, if there is way to "preformat" the whole column, so when the numbers are entered into it they will be automatically formatted as the phone numbers? Do you mean in code (as opposed to selecting the column and using Cell/Format on the worksheet directly)? If so, you should be able to do something like this in code... Columns("G").NumberFormat = "[<=9999999]###-####;(###) ###-####" Where you would change the column reference to suit your needs and preface it with a worksheet reference. -- Rick (MVP - Excel) "fred" wrote in message ... Thank you, Rick When I try this: moExcelWS.Cells.(3,3)NumberFormat="[<=9999999]###-####;(###) ###-####" there is a compile error: "Expected: identifier or bracketed expression" I wonder, if there is way to "preformat" the whole column, so when the numbers are entered into it they will be automatically formatted as the phone numbers? Thanks, Fred "Rick Rothstein" wrote in message ... Not sure how you want to implement this, but the following should give you an idea how to proceed... Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" -- Rick (MVP - Excel) "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
One more question:
I am loading very long list of numbers, (one column, loading in the loop row by row). For the optimum speed: should I preformat the column before loading the numbers or format the whole column after the loading is done? What do you recommend? Thanks, Fred "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting numbers
I've never really thought about it... my gut feeling says it probably
doesn't matter which order you do those in. -- Rick (MVP - Excel) "fred" wrote in message ... One more question: I am loading very long list of numbers, (one column, loading in the loop row by row). For the optimum speed: should I preformat the column before loading the numbers or format the whole column after the loading is done? What do you recommend? Thanks, Fred "fred" wrote in message ... Hello, When using Excel automation, how to programmatically format all numbers in the specified column as the phone numbers. I can see it in the Excel menu there is possible to do FormatSpecialPhone Numbers. How to do that programmatically? Thanks, Fred |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formatting numbers as 1st 2nd | Excel Worksheet Functions | |||
| Formatting numbers | Excel Worksheet Functions | |||
| formatting numbers | Excel Discussion (Misc queries) | |||
| formatting numbers | Excel Programming | |||
| formatting numbers | Excel Worksheet Functions | |||
Linear Mode
