Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set Excel cell format to "Text" from VB.NET?
I'm looking for a code that I could use to format an excel cell dynamically
for an card account number field type with more than 15 digits. Here is the scenario. Column name: Card Account Card account number: xxxxxxxxxxxxxxxx I cannot format the cells as number so I have to format as text. However, when I run the process with the VBA code that I have telling to format as text (@) it gives me this result in scientific notation because of the length, I think. Here is the VBA .NET code: Select Case sFieldType Case "Text" objWorksheet.Range(objWorksheet.Cells(2, i + 1), objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@" I just need to display the acc. number as is...can someone please help me! I gladly appreciatted, Sincerely, Marlei Taylor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set Excel cell format to "Text" from VB.NET?
Hi,
The only way to solve this is ensure the cell is formatted as text BEFORE entering a value i.e. the card number has to treated as a string not a number. Search NG on "ccard 6 or 16 digits" for previous correspondence (I don't know how you reference a previous posting more directly!). HTH ".Net" wrote: I'm looking for a code that I could use to format an excel cell dynamically for an card account number field type with more than 15 digits. Here is the scenario. Column name: Card Account Card account number: xxxxxxxxxxxxxxxx I cannot format the cells as number so I have to format as text. However, when I run the process with the VBA code that I have telling to format as text (@) it gives me this result in scientific notation because of the length, I think. Here is the VBA .NET code: Select Case sFieldType Case "Text" objWorksheet.Range(objWorksheet.Cells(2, i + 1), objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@" I just need to display the acc. number as is...can someone please help me! I gladly appreciatted, Sincerely, Marlei Taylor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set Excel cell format to "Text" from VB.NET?
Alternatively you can try the following (copied and pasted from a
previous posting with exactly the same subject by someone else): <A simple way to force Excel to accept anything as text is to prepend an apostrophe to the text. e.g. to write an integer 123 as 00000123 just write: ActiveCell = "'" & Format(123, "00000000") Note that this cell is now definitively text - this precludes the use of formulae such as SUM on these cells, or further number formatting. If this is an issue consider setting the numberformat as described in other posts. HTH, Gareth Toppers wrote: Hi, The only way to solve this is ensure the cell is formatted as text BEFORE entering a value i.e. the card number has to treated as a string not a number. Search NG on "ccard 6 or 16 digits" for previous correspondence (I don't know how you reference a previous posting more directly!). HTH ".Net" wrote: I'm looking for a code that I could use to format an excel cell dynamically for an card account number field type with more than 15 digits. Here is the scenario. Column name: Card Account Card account number: xxxxxxxxxxxxxxxx I cannot format the cells as number so I have to format as text. However, when I run the process with the VBA code that I have telling to format as text (@) it gives me this result in scientific notation because of the length, I think. Here is the VBA .NET code: Select Case sFieldType Case "Text" objWorksheet.Range(objWorksheet.Cells(2, i + 1), objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@" I just need to display the acc. number as is...can someone please help me! I gladly appreciatted, Sincerely, Marlei Taylor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set Excel cell format to "Text" from VB.NET?
Sorry, I accidently placed the other post. I'm a newbie here. Thank you...
this helped a bunch! "Gareth" wrote: Alternatively you can try the following (copied and pasted from a previous posting with exactly the same subject by someone else): <A simple way to force Excel to accept anything as text is to prepend an apostrophe to the text. e.g. to write an integer 123 as 00000123 just write: ActiveCell = "'" & Format(123, "00000000") Note that this cell is now definitively text - this precludes the use of formulae such as SUM on these cells, or further number formatting. If this is an issue consider setting the numberformat as described in other posts. HTH, Gareth Toppers wrote: Hi, The only way to solve this is ensure the cell is formatted as text BEFORE entering a value i.e. the card number has to treated as a string not a number. Search NG on "ccard 6 or 16 digits" for previous correspondence (I don't know how you reference a previous posting more directly!). HTH ".Net" wrote: I'm looking for a code that I could use to format an excel cell dynamically for an card account number field type with more than 15 digits. Here is the scenario. Column name: Card Account Card account number: xxxxxxxxxxxxxxxx I cannot format the cells as number so I have to format as text. However, when I run the process with the VBA code that I have telling to format as text (@) it gives me this result in scientific notation because of the length, I think. Here is the VBA .NET code: Select Case sFieldType Case "Text" objWorksheet.Range(objWorksheet.Cells(2, i + 1), objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@" I just need to display the acc. number as is...can someone please help me! I gladly appreciatted, Sincerely, Marlei Taylor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I set Excel cell format to "Text" from VB.NET?
You're welcome.
Note, you can check the status of a cell using .PrefixCharacter e.g. debug.print ActiveCell.PrefixCharacter (check it out in help) It's ReadOnly so you don't write to it but you may find it useful ..Net wrote: Sorry, I accidently placed the other post. I'm a newbie here. Thank you... this helped a bunch! "Gareth" wrote: Alternatively you can try the following (copied and pasted from a previous posting with exactly the same subject by someone else): <A simple way to force Excel to accept anything as text is to prepend an apostrophe to the text. e.g. to write an integer 123 as 00000123 just write: ActiveCell = "'" & Format(123, "00000000") Note that this cell is now definitively text - this precludes the use of formulae such as SUM on these cells, or further number formatting. If this is an issue consider setting the numberformat as described in other posts. HTH, Gareth Toppers wrote: Hi, The only way to solve this is ensure the cell is formatted as text BEFORE entering a value i.e. the card number has to treated as a string not a number. Search NG on "ccard 6 or 16 digits" for previous correspondence (I don't know how you reference a previous posting more directly!). HTH ".Net" wrote: I'm looking for a code that I could use to format an excel cell dynamically for an card account number field type with more than 15 digits. Here is the scenario. Column name: Card Account Card account number: xxxxxxxxxxxxxxxx I cannot format the cells as number so I have to format as text. However, when I run the process with the VBA code that I have telling to format as text (@) it gives me this result in scientific notation because of the length, I think. Here is the VBA .NET code: Select Case sFieldType Case "Text" objWorksheet.Range(objWorksheet.Cells(2, i + 1), objWorksheet.Cells(CStr(mlRecordCount), i + 1)).NumberFormat = "@" I just need to display the acc. number as is...can someone please help me! I gladly appreciatted, Sincerely, Marlei Taylor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine text from multiple cells into one cell - =(A1&","&A2","&A3 | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
I have to double click a cell for the "text" format to take | Excel Discussion (Misc queries) | |||
How do I set Excel cell format to "Text" from VB.NET? | Excel Programming |