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

Posted on 2004-10-28
Last Modified: 2008-02-01

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

Question by:muntasirrahman
    LVL 1

    Assisted Solution

    This should work :

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

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

    Good Luck
    LVL 9

    Expert Comment

    Record a macro in Excel and you have the exact code you need!
    LVL 35

    Accepted Solution

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    5 Experts available now in Live!

    Get 1:1 Help Now