Results 1 to 3 of 3

Thread: Follow-up to "Excel Number Format: Indian Style Comma Separation"

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    Follow-up to "Excel Number Format: Indian Style Comma Separation"

    In my previous article on this subject (see "Excel Number Format: Indian Style Comma Separation"), I provided event code that would automatically format cells containing constant values (that is, numbers that were not the result of a formula). Of course, there are occasions when you might want to use a formula to reference other cells (perhaps as part of a calculation) to produce your number which you would then want to format using the Indian Stype Comma Separation. Okay, with a somewhat large "BUT", here is code to do that (note... this code automatically handles both constant numbers and numbers that are the result of a formula)...

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim R As Range, Intersection As Range, Cell As Range, Pattern As String
      ' You set the range of cells that will be formatted with the Indian Comma Style here
      ' For this example, any cell in Column A or in the cells in E3:H10 will be formatted
      Set R = Range("A:A,E3:H10")
      On Error GoTo NoDependents
      If Not Intersect(Target, R) Is Nothing Then
        Set Intersection = Intersect(Target, R)
      ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
        Set Intersection = Intersect(Target.Dependents, R)
      End If
      For Each Cell In Intersection
        If WorksheetFunction.IsNumber(Cell.Value) Then
          Pattern = Trim(Replace(Format(String(Len(Int(Cell.Value)) - 1, "#"), _
                    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", ""))
          If Len(Pattern) = 0 Then Pattern = "0"
          Cell.NumberFormat = Pattern & ".00"
        Else
          Cell.NumberFormat = "General"
        End If
      Next
    NoDependents:
    End Sub
    Now, about that somewhat large "BUT"... the formula part of this code works only if the cell(s) being referenced by the formula is on the same worksheet as the formula itself; that is, if a formula references a cell on another worksheet, this code will not react to changes to that cell. The problem has to do with the way Excel implements its Precedents/Dependents object model across worksheets (which, in my view, it does quite poorly). I do have an idea on how to code around the limitations for the Precedents/Dependents object model, but it would (to my mind's eye) be very, very cumbersome. If I can figure out how to make it more efficient, I will post a "Part 3" article to this particular subject, but in the meantime, you have a method to use for the case when your formulas are referencing local cells. Just to point out, though, if you have formulas referencing cells on other worksheets, you could place a formula pointing to that "foreign" cell on the worksheet with the formula and then change the formula's cell reference from that foreign cell to the local cell contain the formula which is pointing to that foreign cell... if you do that, the code I posted will work fine.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Last edited by DocAElstein; 09-22-2023 at 05:18 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    Code:
    Cell.NumberFormat = "General"
    I think this can be excluded as it forces the cell's format to 'General' even if the user doesn't want
    Yeah, actually, that line is not doing what I had hoped it would do. Here is one of the problems I was trying to overcome... without that line, if you enter a date into one of the monitored cells, then that cell becomes formatted as Date. Now, if you go back to that cell and try to type in a number, either that number becomes a date if, as a date serial number, it is within range or, if not in range, it becomes a series of # signs because it overflows the Date format. I had thought I solved the problem with the line of code you cited, but actually that turns dates into date serial numbers (which are not the digits that were typed in). My main thinking for trying to use that line of code was that if the cells are being monitored in order to convert numbers to the Indian Comma Style Format, then the intention is that only numbers should be in the cells, so making them General Format when not a number should not be a problem, right? Anywhat, the main problem seems to be that Excel converts the cell value according to its Cell Format before it gives it to VB to process... I'm not sure if there is a way to beat this problem in some way or not, but I am working on it.


    https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg. 9k3ShckGnhv9k89LsaigoO
    https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg. 9k-vfnj3ivI9k8B2r_uRa2
    https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
    https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-13-2023 at 10:34 PM.

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  3. Excel Number Format: Indian Style Comma Separation
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 09-18-2013, 11:38 AM
  4. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 AM
  5. Excel showing "Not responding"
    By Rasm in forum Excel Help
    Replies: 8
    Last Post: 07-05-2011, 04:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •