Announcement

Collapse
No announcement yet.

Retain Leading Zero in Custom Format

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Retain Leading Zero in Custom Format

    Hello people

    I have a number format: 000-000-00

    This format is a eight lenght code like : 123-456-78

    But i have a problem sometimes when i enter a code that begins with zero it show it on the format but not on actual code the zero disappears.

    This there a way i can keep the zeros from disappearing from the code like this: 00012345 not 12345.

    Thanks

    Mini12

  • #2
    Re: Retain Leading Zero in Custom Format

    hi Mini,

    try custom format the number to : 00000000

    hth
    Last edited by Dave Hawley; November 24th, 2006, 14:23.
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

    Comment


    • #3
      Re: Retain Leading Zero in Custom Format

      xlite, Mini's custom number format is already set to 000-000-00 for display in the cell. As I've found out in another thread, its hard to return the format information in VB code for a custom format such as this.

      Enter the following values in the Range A1:A8 on a sheet to test the two types of VB codes shown below.
      1
      12
      123
      1234
      12345
      123456
      1234567
      12345678

      VB CODE1: Using the .text qualifier to grab the cell contents and retain its custom format.

      Code:
      Sub ReadCustomString()
      
      'Reads in values in range A1:A8
      For Row = 1 To 8
      mycode = Cells(Row, 1).Text  '.text keeps custom format
      MsgBox mycode
      Next
      
      End Sub
      VB CODE2: Using the .value qualier to grab the unformatted number, then reapply the custom format 000-000-00. This might be the safest bet if you want to make sure you can compare your mycode variable to something later in your code, though it is a bit lenghty. If you should change your format on the sheet, it will not change the custom format applied by this code.

      Code:
      Sub WriteCustomString()
      
      'Reads in values in range A1:A8
      For Row = 1 To 8
      mycode = Cells(Row, 1).Value  '.value does not keep custom format
      mycode = CStr(mycode)         'converts value to string
      
      Select Case Len(mycode)       'gets length of string
      
      'applies a custom format: 000-000-00 to the string
      Case 1   '000-000-01
      block1 = Mid(mycode, 1, 1)
      mycode = "000-000-0" & block1
      Case 2   '000-000-12
      block1 = Mid(mycode, 1, 2)
      mycode = "000-000-" & block1
      Case 3   '000-001-23
      block1 = Mid(mycode, 1, 1)
      block2 = Mid(mycode, 2, 2)
      mycode = "000-00" & block1 & "-" & block2
      Case 4   '000-012-34
      block1 = Mid(mycode, 1, 2)
      block2 = Mid(mycode, 3, 2)
      mycode = "000-0" & block1 & "-" & block2
      Case 5   '000-123-45
      block1 = Mid(mycode, 1, 3)
      block2 = Mid(mycode, 4, 2)
      mycode = "000-" & block1 & "-" & block2
      Case 6   '001-234-56
      block1 = Mid(mycode, 1, 1)
      block2 = Mid(mycode, 2, 3)
      block3 = Mid(mycode, 5, 2)
      mycode = "00" & block1 & "-" & block2 & "-" & block3
      Case 7   '012-345-67
      block1 = Mid(mycode, 1, 2)
      block2 = Mid(mycode, 3, 3)
      block3 = Mid(mycode, 6, 2)
      mycode = "0" & block1 & "-" & block2 & "-" & block3
      Case 8   '123-456-78
      block1 = Mid(mycode, 1, 3)
      block2 = Mid(mycode, 4, 3)
      block3 = Mid(mycode, 7, 2)
      mycode = block1 & "-" & block2 & "-" & block3
      End Select
      MsgBox mycode
      Next
      
      End Sub

      Comment


      • #4
        Re: Retain Leading Zero in Custom Format

        Hi,

        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column <> 1 Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub
        If Len(Target) > 8 Then Exit Sub
        Dim x As Byte
        x = Len(Target)
        Application.EnableEvents = False
        Target = Application.WorksheetFunction.Text(Application.WorksheetFunction.Rept(0, _
        8 - x) & Target.Value, "000-000-00")
        Application.EnableEvents = True
        End Sub


        HTH
        Kris

        ExcelFox

        Comment

        Working...
        X