Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 11th, 2003, 10:08 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default Changing Cell-formating in Excel via VBA

Hi

I am creating an Excel-Worksheet threw VBA, but my problem is that 4 of the columns need to be formated as Text, but they end up as General. Does anyone know how to format all the cells in a column via an Excel Worksheet object?

Thanks in advance.

Gert
Reply With Quote
  #2 (permalink)  
Old November 11th, 2003, 11:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Gert,

The way I find out things like this is to go into Excel, record a new Macro, then do whatever it is I want to find out about (in this case format a column to text). Then I open up the Excel VB Editor and look at the VBA in the macro I just recorded.

In this case you get:
Columns("B:B").Select
Selection.NumberFormat = "@"

which can be condensed to
Columns("B:B").NumberFormat = "@"

hth
Phil
Reply With Quote
  #3 (permalink)  
Old November 11th, 2003, 11:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Gert, I should have added that "Columns" is a property of the Worksheet object, so you would probably need:
worksheetVariableName.Columns("B:B").NumberFormat = "@"

Additionally, you can refer to the column by number (starting at 1) instead of letter.

rgds
Phil
Reply With Quote
  #4 (permalink)  
Old November 11th, 2003, 06:20 PM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Phil. I haven't had time to try it out yet, but it looks like a good sugestion. I thanks for the Macro-tip.

Gert
Reply With Quote
  #5 (permalink)  
Old May 10th, 2005, 02:56 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have same problem, but I want to know how to change from text format to number format.

I am generating a report with vb .net in a .xls sheet but my numbers are stored as text. how can I fix this?

The number of hours is saved as text and not as number. Any ideas?
[vb]
Code:
...
While i < dsetTask.Tables("tblTask").Rows.Count
            drowData = Data.NewRow()
            drowData("Name") = dsetTask.Tables("tblTask").Rows(i)("Name")
            drowData("Hour") = dsetTask.Tables("tblTask").Rows(i)("Hour")
End While
...
[/vb]

any ideas?

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA Check When Cell Has Changed tonyrosen Excel VBA 13 March 24th, 2015 06:15 PM
Excel cell Formating Using C# veeruu C# 5 September 21st, 2011 02:56 PM
editing datagrid and changing cell color upon edit matoaka VS.NET 2002/2003 0 August 11th, 2006 12:04 PM
Capture cell value before changing it chp Excel VBA 2 March 15th, 2006 03:40 PM
Dynamically Changing the content of a table cell asdasd Javascript 4 February 18th, 2005 01:44 PM



All times are GMT -4. The time now is 03:42 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.