 |
| Excel VBA Discuss using VBA for Excel programming. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
August 12th, 2004, 03:11 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
How To Force Column To Text
Hi.
I have a column in an Excel 2002 spreadsheet that will contain a 2 character code e.g. "AB" or "09" or nothing. The problem is that the first row of the column has nothing (which is ok), the second has "9" and the third is ok at "AB". I want the "9" to be "09".
The columns are populated programmatically. I tried to set the NumberFormat Property to "Text" but that didn't work. How do I make sure that the column will have two characters and not drop any leading zeros from a string?
TIA.
Rita
|
|
August 12th, 2004, 04:58 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I preceded my column with a tick (single quote) and that worked - I'm now getting "09" in my column.
The problem now is that when I open up the spreadsheet, there's a warning message for that cell telling me that a number is stored as text.
How can I programmatically suppress any error/warning messages.
TIA.
Rita
|
|
August 13th, 2004, 12:25 PM
|
|
Authorized User
|
|
Join Date: Aug 2004
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Try selecting he column and using Data/Text to columns/finish
-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.
|
|
August 13th, 2004, 12:45 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi Brian.
Thanks for your response but I don't understand what you're getting at.
Rita
|
|
August 13th, 2004, 01:05 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Try this one for the column:
Application.ErrorCheckingOptions.NumberAsText = False
I got the code with the macroeditor, changing the errorcontrol settings of Excel. Check it out!
|
|
August 13th, 2004, 01:11 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Seem as if it only works for the application as a whole. Can't be set for a single column.
|
|
August 13th, 2004, 01:18 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks Birger. That's what I was looking for.
|
|
August 13th, 2004, 03:10 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I used "Application.ErrorCheckingOptions.BackgroundChecki ng = False" and that stopped the warning messages I was getting. The only problem is as you said, it's for the whole spreadsheet.
|
|
August 13th, 2004, 03:14 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
True, however, you should be able to define any columns you want as numbers so that they'll act as numbers. Does that solve your problem?
|
|
August 13th, 2004, 03:28 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
As far as i can see, my suggestion only affects columns/cells EXPLICITLY defined as text. Cells, which have not been defined as any special format (=standard) still behaves like standard formatting. Likewise, cells formatted as numbers, still acts as numbers.
Are you sure you want/need to disable all errorchecking, which you say you've done? (.BackgroundChecking = False)?
|
|
|
