Format cell when new row is created

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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
 

Attachments

  • 2317.jpg
    2317.jpg
    77.5 KB · Views: 4

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,657
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Thanks will do
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,071
Office Version
  1. 2007
Platform
  1. Windows
All sorted by using single digits

Thanks
 

Forum statistics

Threads
1,141,062
Messages
5,704,048
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top