Format cell when new row is created

[ipbr21054]

Morning.

Column A is formatted so i can use the number foramt like 001 002 003 etc

I am using this code to insert a new row at my row choice.

Private Sub CommandButton2_Click()
MsgBox [MAX(0+1+Table20[I CODE SORT])] & " " & "IS THE NEXT ROW TO USE"

 Dim i As Integer
 Dim ControlsArr As Variant, ctrl As Variant
 Dim x As Long
 Dim z As Integer
 
 z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED INTO ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))

    With ThisWorkbook.Worksheets("DATABASE INFO")
    .Rows(z).EntireRow.Insert Shift:=xlDown
End With
End Sub

I selct to enter a new row at row 37
The new row is created but when i type say 037 in the cell at column A and then leave the cell i see it then change from 037 to 37.000

The format for column A is set to NUMBER.
Please see screen shot & please advise how i can add code to force it to stay as what i type 037 038 039 etc etc

Many thanks

 

[Alex Blakenburg]

If what you type is always a 9 digit number then you can format the column using a custom number format of 000 000 000. If they are of different lengths then you might be better off formatting the column as Text. Text will keep it exactly the way you type it in.

 

[ipbr21054]

9 digit ?
These are only 3 digit.
001
002
003
Etc etc

I added the two 00 as I thought when I did a sort in column A it would of been out of order.
like
1 followed by 11 followed by 2
Get me ?
If that is not necessary then I should be able to just add like.
1
2
3
4
Etc etc

 

[Alex Blakenburg]

If they are all just numbers and you don't need the leading zeroes, then just format the column using either general or the number format of your choice.

 

[ipbr21054]

Thanks will do

 

[Alex Blakenburg]

Let me know if you still have issues with this.

 

[ipbr21054]

All sorted by using single digits

Thanks

 

[Alex Blakenburg]

Great. Thanks for letting me know.