See inline comments...

Can we make the form float so that I can work on the sheet as well.
Along with error messages that you have put, if you can also include
messages for blank values like it should alert in case one or both the
text boxes are empty.

Whatever code you are showing your UserForm from, use this statement to show
it instead of the statement you have now (assuming your UserForm is still
named UserForm1; if not, use your own UserForm's name instead... the key is
the vbModeless argument)...

UserForm1.Show vbModeless


Also numbers starting with zero generate list without leading zero.

Start: 09871270
End: 09871277

Give this new code a try...

Private Sub CommandButton1_Click()
Dim X As Long
Dim LastColumn As Long
Dim Number1 As Variant
Dim Number2 As Variant
Dim TBox1 As String
Dim TBox2 As String
TBox1 = Trim(TextBox1.Text)
TBox2 = Trim(TextBox2.Text)
If TBox1 = "" Or TBox2 = "" Then
MsgBox "You must fill in both text boxes!"
ElseIf TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
Number1 = CDec(TBox1)
If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
Number2 = CDec(TBox2)
If Number2 < Number1 Then
MsgBox "TextBox2 must contain a larger number than TextBox1"
Else
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If LastColumn = 1 And Range("A1").Value = "" Then LastColumn = 0
For X = 0 To Number2 - Number1
Cells(X + 1, LastColumn + 1).Value = _
"'" & Format$(Number1 + X, String(Len(Trim(TBox1)), "0"))
Next
End If
Else
MsgBox "Bad entry in TextBox2"
End If
Else
MsgBox "Bad entry in TextBox1"
End If
End Sub

Rick