Starting number

[rlink_23]

This is going to be quick and easy for you all. On my Userform I have sequential numbers moving over to my spreadsheet But I want the number in A4 to start at 1000. I have tried putting 1000 in every spot I could to get it to work but to no avail. If I put 1000 in Cell A4 then it works and goes to A5 with 1001 and so on. If I leave A4 blank then it starts at 1 and goes up. I have tried putting a number in the textbox and that doesn't work. I have searched in these forums and online and cannot fire out what I am missing.. Hopefully someone can help.. Other than that one small detail everything works fine....

The Command button click

Private Sub CommandButton5_Click()    
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Quotes")
     iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        quotenumber.Text = Application.Max(ws.Range("A:A")) + 1
        If Trim(Me.quotenumber.Value) = "" Then
       Range("QuoteNumber").Value = Range("QuoteNumber").Value + 1
    
  Exit Sub
End If
        
ws.Cells(iRow, 1).Value = quotenumber.Value
ws.Cells(iRow, 2).Value = Date1.Value
ws.Cells(iRow, 3).Value = Year.Value + " " + Make.Value + " " + Model.Value
ws.Cells(iRow, 4).Value = size.Value
ws.Cells(iRow, 5).Value = ComboBox1.Value
ws.Cells(iRow, 6).Value = Cost.Value
ws.Cells(iRow, 7).Value = custnumber.Value
ws.Cells(iRow, 8).Value = company.Value
ws.Cells(iRow, 9).Value = FirstName.Value + " " + Me.LastName.Value
ws.Cells(iRow, 10).Value = Phone1.Value
ws.Cells(iRow, 11).Value = City.Value
ws.Cells(iRow, 12).Value = State.Value
ws.Cells(iRow, 13).Value = ZipCode.Value
ws.Cells(iRow, 14).Value = Email.Value
ws.Cells(iRow, 16).Value = Initals.Value




      End Sub

The Userform initialize

Private Sub UserForm_Initialize()
quotenumber.Value = Format(Application.Max(Sheets("Quotes").Range("A:A")) + 1)
End Sub

 

[mumps]

What if you set the value of A4 when the userform initializes:

Private Sub UserForm_Initialize()
    Sheets("Quotes").Range("A4").Value = 1000
End Sub

 

[rlink_23]

That works but it puts 1000 in cell before save is clicked. I guess what I need is a lil different after seeing it. I would need the textbox to start at 1000 and once saved it shaves to excel and moves into the next number in the sequence. Really whatever number I put in the textbox would be the starting number which woukd be 1000 at this point. (It will be locked when other people use it

 

[mumps]

Try:

Private Sub UserForm_Initialize()
    quotenumber.Value = 1000
End Sub

Private Sub CommandButton5_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Quotes")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    quotenumber.Text = quotenumber.Value + 1
    ws.Cells(iRow, 1).Value = quotenumber.Value
    ws.Cells(iRow, 2).Value = Date1.Value
    ws.Cells(iRow, 3).Value = Year.Value + " " + Make.Value + " " + Model.Value
    ws.Cells(iRow, 4).Value = Size.Value
    ws.Cells(iRow, 5).Value = ComboBox1.Value
    ws.Cells(iRow, 6).Value = Cost.Value
    ws.Cells(iRow, 7).Value = custnumber.Value
    ws.Cells(iRow, 8).Value = company.Value
    ws.Cells(iRow, 9).Value = FirstName.Value + " " + Me.LastName.Value
    ws.Cells(iRow, 10).Value = Phone1.Value
    ws.Cells(iRow, 11).Value = City.Value
    ws.Cells(iRow, 12).Value = State.Value
    ws.Cells(iRow, 13).Value = ZipCode.Value
    ws.Cells(iRow, 14).Value = Email.Value
    ws.Cells(iRow, 16).Value = Initals.Value
End Sub

 

[rlink_23]

That puts 1000 in the quote number textbox but whem yu click save it starts over back at one and found a up from there 2,3,4 and so on

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.