Converting  a number to text using FORMAT in VBA, using a cell number format

Posted on 2011-05-11
Last Modified: 2012-05-11
Hi there,

I am trying to populate a textbox in a user form with a number from a cell in a worksheet.  The format of the source cell will vary and can include some custom number formats.  I want to try and use the actual numberformat property of the cell when formatting the number as text in the textbox.  Is this possible?  I don't think the formats used in the VBA Format function are able to directly use the cell numberformat property (or am I wrong?), but is there something clever I can do that doesn't involve an inordinate amount of coding that will achieve this?

Many thanks,

Question by:Digittt
    LVL 7

    Accepted Solution

    If I'm understanding you correctly, instead of using the format() method, you could just use the text property of the cell.

    Let's say you have cell "A1" with the number 1234, formatted as shown there.
    Range("A1").text in VBA will produce the string "1234"

    Let's say you change the formatting in cell "A1" so it shows "1,234.00"
    Range("A1").text in VBA will produce the string "1,234.00"

    is that what you're looking for?

    Author Closing Comment

    Spot on and can't believe I hadn't come across the property before.

    Many thanks!


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    755 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

    24 Experts available now in Live!

    Get 1:1 Help Now