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.


  • FYI i removed all the code for the worksheet,right click tab & cut / save and still the code runs.

    So narrowing it down but where is it ?

  • 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

  • Is it in the WorkBook module? Probably the Workbook_SheetChange event.

    In the VB Editor double click ThisWorkBook to access the workbook module.

  • 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

    Cells(1,1).value=Me.TextBox 1.value
    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

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

  • It should do. You can adapt the code for the Post Code, using 5 instead of 3.

    It would probably be good to build in a check that the number of characters is correct

  • 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

    Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case Len(TextBox3)
    Case 5, 6
    With TextBox3
    .Text = .Text & " "
    .SelStart = Len(.Text)
    End With
    End Select
    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!