none
Getting a list of custom number formats in Excel programmatically. RRS feed

  • Question

  • I need to get the list of custom number formats to have a quick access to it. I can find it in "Format Cells" dialog (Select some cell -> click mouse right button -> Format Cells... -> Number tab -> Custom category). But I need to get it in programmatic way. 

    How can I do this if it is possible?

    Monday, July 1, 2013 1:35 PM

Answers

  • Hi Denys, as said James Cone, a procedure by Leo Heuser provides a workaround for manipulating custom number formats. To do this, it hacks into the Number Format dialog box with SendKeys. Here it is the original link: List of Custom Formats

    I just modify the original procedure to obtain only the list of custom number formats.

    Option Explicit

    Sub ListCustomNumberFormats()
        Dim SaveFormat As Variant
        Dim Counter As Long
        Dim sCell As String
        
        sCell = "B1"        'Buffer cell
        If MsgBox("Get a list of custom formats", vbOKCancel) _
            = vbCancel Then Exit Sub
        With Worksheets.Add
            .Move after:=Worksheets(Worksheets.Count)
            .Name = "CustomFormats"
            .Activate
        End With
        With Range("A1")
            .Value = "List of Custom formats"
            .Font.Bold = True
            Range(sCell).Select
            .Offset(1, 0).Value = Range(sCell).NumberFormatLocal
            Counter = 2
            Do
                SaveFormat = Range(sCell).NumberFormatLocal
                SendKeys "{tab 3}{down}{enter}"
                Application.Dialogs(xlDialogFormatNumber).Show SaveFormat
                .Offset(Counter, 0).Value = Range(sCell).NumberFormatLocal
                Counter = Counter + 1
            Loop Until Range(sCell).NumberFormatLocal = SaveFormat
            .Offset(Counter - 1, 0).Value = ""
        End With
        With Range("A1:A" & Counter - 1)
            .EntireColumn.AutoFit
            .HorizontalAlignment = xlLeft
            .NumberFormat = "@"
        End With
        MsgBox "#" & Counter - 2 & " custom formats"
    End Sub


    Best Regards,
    Pedro Wave


    Please do not forget to click “Vote” if this post helps you and "Propose as answer" if it answer the question.

    Friday, July 12, 2013 10:38 AM

All replies

  • Quick access you mean as soon as you press some short cut key then you directly taken to the Number tab -> Custom category screen??

    Vish Mishra

    Monday, July 1, 2013 1:39 PM
  • Not exactly... I want to build my own comboBox that contains custom formats and add it on my ribbon bar.
    Monday, July 1, 2013 1:44 PM
  • Sorry, I do not know how to get the list of custom format programmatically. 

    Any one knows? Please guide on this


    Vish Mishra

    Monday, July 1, 2013 2:23 PM
  • You can use a simple way.

    Below statement will directly launch you in Custom number.

    application.Dialogs(42).Show 0

    42 referes to Number format dialog box.

    create a a macro and then assign shortcut key to that macro.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, July 1, 2013 2:41 PM
    Answerer
  • You can use whatever custom format strings that you want - you are not limited by what is currently in the custom category, so it isn't necessary to read them.
    Monday, July 1, 2013 2:58 PM
  • You are right, but it seems that topic starter wants to show list of existence formats in his own control. It looks like common task and, in any case, it`s important to have an ability to get list of custom formats programmatically.

    I have the same task now and unfortunatelly I don`t have an answer to this question. it seems there are no properties in Office object model for receiving custom formats.

    Monday, July 1, 2013 3:26 PM
  • Getting a list of number formats (using vba) can be done, but it's messy.
    Do a search for "Leo Heuser" (rip) and "custom number formats" for the code.

    If you want a list of unused styles nicely listed on your worksheet, my Formats & Styles add-in will do that.
    (it can also remove the unused ones).
    Free from...  https://goo.gl/IUQUN2 (Dropbox)

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 3:45 AM update link
    Monday, July 1, 2013 3:42 PM
  • You can use whatever custom format strings that you want - you are not limited by what is currently in the custom category, so it isn't necessary to read them.

    I agree with Bernie. You can assign to Range.NumberFormat property whatever custom format strings you want.

    Selection.NumberFormat = "0.00"
    
    Selection.NumberFormat = "#,##0_);[Red](#,##0)"
    
    Selection.NumberFormat = "m/d/yyyy"

    Also, if you want to show list of formats somewhere, you can directly show the format strings you want.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 2, 2013 12:47 PM
    Moderator
  • Hi Denys, as said James Cone, a procedure by Leo Heuser provides a workaround for manipulating custom number formats. To do this, it hacks into the Number Format dialog box with SendKeys. Here it is the original link: List of Custom Formats

    I just modify the original procedure to obtain only the list of custom number formats.

    Option Explicit

    Sub ListCustomNumberFormats()
        Dim SaveFormat As Variant
        Dim Counter As Long
        Dim sCell As String
        
        sCell = "B1"        'Buffer cell
        If MsgBox("Get a list of custom formats", vbOKCancel) _
            = vbCancel Then Exit Sub
        With Worksheets.Add
            .Move after:=Worksheets(Worksheets.Count)
            .Name = "CustomFormats"
            .Activate
        End With
        With Range("A1")
            .Value = "List of Custom formats"
            .Font.Bold = True
            Range(sCell).Select
            .Offset(1, 0).Value = Range(sCell).NumberFormatLocal
            Counter = 2
            Do
                SaveFormat = Range(sCell).NumberFormatLocal
                SendKeys "{tab 3}{down}{enter}"
                Application.Dialogs(xlDialogFormatNumber).Show SaveFormat
                .Offset(Counter, 0).Value = Range(sCell).NumberFormatLocal
                Counter = Counter + 1
            Loop Until Range(sCell).NumberFormatLocal = SaveFormat
            .Offset(Counter - 1, 0).Value = ""
        End With
        With Range("A1:A" & Counter - 1)
            .EntireColumn.AutoFit
            .HorizontalAlignment = xlLeft
            .NumberFormat = "@"
        End With
        MsgBox "#" & Counter - 2 & " custom formats"
    End Sub


    Best Regards,
    Pedro Wave


    Please do not forget to click “Vote” if this post helps you and "Propose as answer" if it answer the question.

    Friday, July 12, 2013 10:38 AM