Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi loungers...I have forgotten how to do a custom format for entering serial numbers with 11 characters....in the past, when I entered a 7-digit number, I used a custom format on the cell of 0000000 (7 zeros).......so that even if the # was 555, it would show as 0000555

    I am trying to do the same with an 11-digit set, so that the number 555 will show as 00000000555 or 555A1 will show as 000000555A1.......but each time I choose 00000000000 (11 zeros), for some reason it formats the number as a Polish phone number...??..??...what am I doing wrong? Thank you.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You won't get the padding if you use a non numeric character in the formatting. You can use a formula to do the padding for you or use a Change Event macro to add the padding after you've entered the data.

    The formula would be =right("00000000000"&A1,11)

    If you don't want to to show as Number PESEL, which is Poland's national Identification Number, use a 0000000000#.

    If you want to use the macro you can use:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target = Right("00000000000" & Target, 11)
    End If
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    So, I would enter the macro as a WS change event on the sheet in question, and use the range as shown (??) if the data is going into c13:c751? ..see below..or is that the right way to express the target range?


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Range("c13:c751") Then
    Target = Right("00000000000" & Target, 11)
    End If
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [s]Exactly[/s]. - not anymore since you've changed your post

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ...hmmm...I tried that code but it gets stopped and highlights the line If Target.Range("c13:c751") Then

    ......what would cause that? An incorrect definition of the Range..??..??

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use the following:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("c13:c751")) Is Nothing Then
        Target = Right("00000000000" & Target, 11)
    End If
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    OK...thank you.....now, I notice that it takes a long time for the WS to become 'active' again (in the sense that I have to wait for a while before I can enter new data, or select another cell or even move to another worksheet..........I assume that this is b/c the macro is running thru the whole range of c13:c751 as part of the change event.......does it do that on each sheet on the workbook, or just the one where I entered the code (eg: sheet 1) but doesn't do it on sheet 2 or sheet 3?...........and, if it does it for every shjeet in the WB, can I confine it to 1 sheet?

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The macro will only adjust the cell where you've just entered the data, not the whole range. I'm not sure why it would be taking a long time, unless you have heavy calculations going on based on the cell you're changing or another change event that is being triggered by this event.

    It will only affect the sheet containing the Change Event no other sheet.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ...hmm...thanks for that info...not sure why it takes so long to run thru that range c13:c751, whihc is not a big range...and there are no other calcs based on that data (I have a sort mechanism, but that only runs if you hit a 'sort' button).....I will look at it again, and perhaps come back here in a bit if you don't mind?...thanks.again

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would recommend something more like (warning air-code beware of typos):

    Dim rCell as range
    If Not Intersect(Target, Range("c13:c751")) Is Nothing Then
    for each rCell in Intersect(Target, Range("c13:c751"))
    application.enableevents = false
    rcell = Right("00000000000" & Target, 11)
    application.enableevents = true
    next
    set rcell = nothing
    End If

    Disabling the events prevents recursive calls to the routine. Looping through all the cells in the range that are changed prevents problems if more than 1 cell is changed (like when a group of cells is copied into the range specified

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve...not sure where this code goes---does it supplement the code from mbarron or replace all of it? Is it a worksheet event code?.....and what does (warning air-code beware of typos) mean..?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It replaces the code and would be in the same event (if you look closely, you can see the lines from the original still there in the code.

    "Air code" means that I did not test it in any way. I entered it directly into the reply box.

    Steve

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi folks,

    FWIW, I don't have any trouble using either:
    00000000000A1
    or
    00000000000A0
    as a Custom number format. Entering 555 with the:
    . former produces 00000000555A1; and
    . latter produces 00000000055A5
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    macropod,
    The OP wants to have whatever is entered in the cell to be a 11 character field padded with zeros. The A character will not be present in all of the entries.

Posting Permissions

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