• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2843
  • Last Modified:

How to format a cell or a Range in Excel From vb6.0


I would like to format a cell or Range of Excel from vb6.0 Code,

As the way we could do in
 (Excel -> Right click->  Format cells-> Then Number tab->Then Under category List box-> Number)

My sample program is given Below:

    Dim objExcel As Excel.Application
    Dim objWorkBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet

    Set objExcel = New Excel.Application
    objExcel.Visible = True


    Set objWorkBook = objExcel.Workbooks.Add
    Set objSheet = objWorkBook.Worksheets(1)

    With objSheet

        .Cells(1, 3) = CboProject.Text
        .Cells(1, 3).Font.Bold = True
        .Cells(3, 3) = CboReportFor.Text
        .Cells(3, 3).Font.Bold = True
        .Cells(4, 3) = "Date From : " & cboUsage_DateFrom & " to " & cboUsage_DateTo

        .Cells(4, 3).Font.Bold = True

        .cells(X,Y).Format (?)

    End With


Can you help me on that?
Thanks in Advance

2 Solutions
This should work :

.cells(X, Y).NumberFormat = "$##,###.00"

Ofcourse you can change the # and fill in the format you want to have.

Good Luck
Record a macro in Excel and you have the exact code you need!
If you wanted to show the actual window at runtime you could use

DeltaDaan has the right syntax with the .NumberFormat, using a "0" will force Excel to show that digit, even if its a zero. "#" will display the number if it exists
If the value is 1234.56 then
 "#,###" will show 1,235
 "#,###.#" will show 1,234.6
 "0,000,000.000" will show 0,001,234.560

p_sie is right though, you can just record a macro, format the number as you want it to show, then look at the code to see what the numberformat is.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now