Unable to locate code than runs on my worksheet

  • On my worksheet at column W i would enter a phone number & the code i am unable to locate "as i need to use elsewhere" would space it for me.


    Example i would enter 07899843147 & when i leave the cell in question i would see it change to 07899 843147

    It would enter a space.


    Now from memory the code had some kind of symbol like @@@@@/@@@@@@


    Ive looked on the worksheet & also in the modules but cant find this code.


    Do you have any advice of how i can track it down.

    Thanks

  • sounds like the cells are using a custom format


    maybe select one of the cells that has this formatting and press Ctrl + 1 key combo to get the format menu and see if there is a custom format

  • Morning,

    Gijsmo you were correct as it was a custom format so many thanks as i spent an hour again yesterday going through each module only not to find it.


    Like mentioned in post #1 i needed to find it as i wanted to use it again on another worksheet.

    So now ive found it i dont think i can use it because i insert a new row into a worksheet at various positions hence not knowing at the time which position it will be.


    So i need to make this code using vba only & place it in the selection change event.

    Please can you advise a vba code so when a number is inserted in column C the code will space it like 00000000000 to 00000 000000


    Have a nice day

  • Custom Number Formatting is way more efficient than using VBA. There is no advantage to using a worksheet event to change the format.


    If you are adding it from your userform then use


    Code
    1. Cells(1,1).value=Me.TextBox 1.value
    2. Cells(1,1).value.NumberFormat="00000 000000"
  • I assume i then add it into the comman button code /
    If so not sure where to insert it as it keeps changing to red.


  • I might of put it in the wrong code so now ive tried on the actual TextBo3 in the userform like so but second line of advised code changes yellow.


    So i have this shown below & when transfered to worksheet it will fit into column C


    Code
    1. Private Sub TextBox3_Change()
    2. TextBox3 = UCase(TextBox3)
    3. Cells(1, 1).Value = Me.TextBox3.Value
    4. Cells(1, 1).Value.NumberFormat = "00000 000000"
    5. End Sub
  • Try this


  • Can we not & would it be easier / quicker to have a change event code like,but obviously written correctly so,,,,


    Range = C

    for any cell in range

    if number is 00000000000

    then change it to 00000 000000


    using the case format code like case 5 6 etc


    Obviously above is miles out hence why im in this group

  • This works


    Code
    1. Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    2. Select Case Len(TextBox3)
    3. Case 5, 6
    4. With TextBox3
    5. .Text = .Text & " "
    6. .SelStart = Len(.Text)
    7. End With
    8. End Select
    9. End Sub
  • I don't think that will work properly.


    This works on the same priciple as entering the post code


  • This is what works for me apart from 2 things so im happy to use this.


    Ive lost the code that spaces the post code,

    I type BS296HD but on worksheet it stays the same and not BS29 6HD



  • That also does not add a space between phone number

    The same code works for the post code, so I don't see why it isn't working for this.


    I really can't see how your amended code works. It doesn't even use the Post code format.

  • The code below works fine BUT like i mentioned ive lost the post ocde part where a space is added Thus

    I type BS296HD but i need to then apply a space so on the worksheet its shown as BS29 6HD


    Please can you edit my code below as apart from that its working.


    The case number i use must be -1 otherwise i see 0 in a cell thats not required