Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.il.hebrew.vb,microsoft.public.vb.syntax
|
|||
|
|||
Formatting a cell in Excel, using pure VB code
This is one place where the Macro Recorder could have helped you.
When I recorded a macro setting a cell to Text format: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/02/2003 by J.E. McGimpsey ' ' Selection.NumberFormat = "@" End Sub In article , "Jean Claude" wrote: Hi all, I have a problem with excel cell formatting. One of the columns should contain phone numbers (a number that starts with zero ... sometimes) The VB program puts a phone number lets say - "03555555" When I open the file from Excel - I see the number without the zero. I saw that the format of the cell is "General" however I think I should set the format of the cell to "Text" I tried several code lines but none helped Like: xlsSheet.Cells(1 + I, 3).NumberFormat="Text" - This did not work xlsSheet.Cells(1 + I, 3)=Chr$(34) & sPhoneNumber & Chr$(34) - With this , the cells contained "" xlsSheet.Cells(1 + I, 3).NumberFormat="00000000" - In this case, when I put numbers shorter than the number of 0 , example - "031234", the cell contained "00031234" Please someone show me the right way ... Thank you Frustrated Guy |
#2
Posted to microsoft.public.excel.programming,microsoft.public.il.hebrew.vb,microsoft.public.vb.syntax
|
|||
|
|||
Formatting a cell in Excel, using pure VB code
Jean Claude
xlsSheet.Cells(1+I,3).NumberFormat = "@" will format as text. xlsSheet.Cells(1+I,3).Value = "'" & sPhoneNumber will enter as a text string regardless of the formatting. That is doublequote singlequote doublequote. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jean Claude" wrote in message ... Hi all, I have a problem with excel cell formatting. One of the columns should contain phone numbers (a number that starts with zero ... sometimes) The VB program puts a phone number lets say - "03555555" When I open the file from Excel - I see the number without the zero. I saw that the format of the cell is "General" however I think I should set the format of the cell to "Text" I tried several code lines but none helped Like: xlsSheet.Cells(1 + I, 3).NumberFormat="Text" - This did not work xlsSheet.Cells(1 + I, 3)=Chr$(34) & sPhoneNumber & Chr$(34) - With this , the cells contained "" xlsSheet.Cells(1 + I, 3).NumberFormat="00000000" - In this case, when I put numbers shorter than the number of 0 , example - "031234", the cell contained "00031234" Please someone show me the right way ... Thank you Frustrated Guy |
#3
Posted to microsoft.public.excel.programming,microsoft.public.il.hebrew.vb,microsoft.public.vb.syntax
|
|||
|
|||
Formatting a cell in Excel, using pure VB code
Another option
Dim sVal as String sVal = "03555555" xlsSheet.Cells(1 + I,3).Value = "'" & sVal preceding the number with a single quote will cause it to be treated as a string. -- Regards, Tom Ogilvy "Jean Claude" wrote in message ... Hi all, I have a problem with excel cell formatting. One of the columns should contain phone numbers (a number that starts with zero ... sometimes) The VB program puts a phone number lets say - "03555555" When I open the file from Excel - I see the number without the zero. I saw that the format of the cell is "General" however I think I should set the format of the cell to "Text" I tried several code lines but none helped Like: xlsSheet.Cells(1 + I, 3).NumberFormat="Text" - This did not work xlsSheet.Cells(1 + I, 3)=Chr$(34) & sPhoneNumber & Chr$(34) - With this , the cells contained "" xlsSheet.Cells(1 + I, 3).NumberFormat="00000000" - In this case, when I put numbers shorter than the number of 0 , example - "031234", the cell contained "00031234" Please someone show me the right way ... Thank you Frustrated Guy |
#4
Posted to microsoft.public.excel.programming,microsoft.public.il.hebrew.vb,microsoft.public.vb.syntax
|
|||
|
|||
Formatting a cell in Excel, using pure VB code
Hi all,
I have a problem with excel cell formatting. One of the columns should contain phone numbers (a number that starts with zero ... sometimes) The VB program puts a phone number lets say - "03555555" When I open the file from Excel - I see the number without the zero. I saw that the format of the cell is "General" however I think I should set the format of the cell to "Text" I tried several code lines but none helped Like: xlsSheet.Cells(1 + I, 3).NumberFormat="Text" - This did not work xlsSheet.Cells(1 + I, 3)=Chr$(34) & sPhoneNumber & Chr$(34) - With this , the cells contained "" xlsSheet.Cells(1 + I, 3).NumberFormat="00000000" - In this case, when I put numbers shorter than the number of 0 , example - "031234", the cell contained "00031234" Please someone show me the right way ... Thank you Frustrated Guy |
#5
Posted to microsoft.public.excel.programming,microsoft.public.il.hebrew.vb,microsoft.public.vb.syntax
|
|||
|
|||
Formatting a cell in Excel, using pure VB code
Thank you all
I was looking for this : xlsSheet.Cells(1+I,3).NumberFormat = "@" Its workinggggggggggggggg Guy ??"Dick Kusleika" כתב בהודעה ... Jean Claude xlsSheet.Cells(1+I,3).NumberFormat = "@" will format as text. xlsSheet.Cells(1+I,3).Value = "'" & sPhoneNumber will enter as a text string regardless of the formatting. That is doublequote singlequote doublequote. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Jean Claude" wrote in message ... Hi all, I have a problem with excel cell formatting. One of the columns should contain phone numbers (a number that starts with zero ... sometimes) The VB program puts a phone number lets say - "03555555" When I open the file from Excel - I see the number without the zero. I saw that the format of the cell is "General" however I think I should set the format of the cell to "Text" I tried several code lines but none helped Like: xlsSheet.Cells(1 + I, 3).NumberFormat="Text" - This did not work xlsSheet.Cells(1 + I, 3)=Chr$(34) & sPhoneNumber & Chr$(34) - With this , the cells contained "" xlsSheet.Cells(1 + I, 3).NumberFormat="00000000" - In this case, when I put numbers shorter than the number of 0 , example - "031234", the cell contained "00031234" Please someone show me the right way ... Thank you Frustrated Guy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Formatting Code for SI Units in Excel | Excel Worksheet Functions | |||
conditional formatting w/ more than 3 conditionas, color code to a different cell | About this forum | |||
R6025 pure virtual funtion call | Excel Discussion (Misc queries) | |||
Runtime Error! R6025 -pure virtual function call | Excel Discussion (Misc queries) |