Autogenerate purchase order number and input to Userform.

[scrawltome]

[h=2]I have a worksheet for creating and storing purchase orders. I have several different sheets and the main 'front page' sheet with command buttons. The command buttons are linked with userforms that are working perfectly, and inputting data into the appropriate sheet where and when I want.
However, on the worksheet that stores all the purchase orders, it is setup on some sort of Excel table. So if I'm manually putting in data, I just tab over the edge of the table and it automatically starts a new row and I have arranged for it to auto generate the next number.[/h]

What I want to do it:
When I click on the command button to create new purchase order, I would like the userform to go to the sheet where the purchase orders are recorded and find the next purchase order number. Therefore, going to the sheet ("Purchase Orders") and finding the last line of data +1. Then finding the auto-generated next invoice number from column A.
I'm not sure if it's possible, as the next invoice number is not actually generated yet, so the code may have to include allowing the table on sheet "Purchase Orders" to generate the number, then place it at the top of the userform.
I don't know if I'm making sense - hopefully. I've been working on this for hours! TIA.​

 

[HalfAce]

Hi,
I'm not too sure I understand your exact question, but if you're just looking for how to reference the last used cell in column A of sheet 'Purchase Orders', (and populate (for example, UserForm1 TextBox1), with that number + 1), you can use something like this.

UserForm1.TextBox1.Value = Sheets("Purchase Orders").Cells(Rows.Count, "A").End(xlUp).Value + 1

To enter that value from the userform to the next cell in column A of the Purchase Orders sheet, you can use this.

Sheets("Purchase Orders").Cells(Rows.Count, "A").End(xlUp)(2).Value = UserForm1.TextBox1.Text

Does this help, or did I misunderstand what you're asking?

 

[scrawltome]

Hi HalfAce,

This is the code for the userform:

Private Sub SaveEntry_Click()
    'Copy input values to sheet.
        
    Dim RowCount As Long
    Dim ctl As Control
    
    'Dim ws As Worksheet
    'Set ws = Worksheets("Purchase Orders")
    
    If Not IsDate(Me.txtDate.Value) Then
        MsgBox "The date box must contain a date.", vbExclamation, "Purchase Orders"
        Me.txtDate.SetFocus
        Exit Sub
    End If
    
    If Me.txtName.Value = "" Then
        MsgBox "Please enter the name of the person who will benefit from this purchase order.", vbExclamation, "Purchase Orders"
        Me.txtName.SetFocus
        Exit Sub
    End If
    
    If Me.txtPayTo.Value = "" Then
        MsgBox "Please enter the name of the business this purchase order is written to.", vbExclamation, "Purchase Orders"
        Me.txtPayTo.SetFocus
        Exit Sub
    End If
    
    If Me.cboPOTypes.Value = "" Then
        MsgBox "Please enter what the purchase order is paying for.", vbExclamation, "Purchase Orders"
        Me.cboPOTypes.SetFocus
        Exit Sub
    End If
    
    If Not IsNumeric(Me.txtPOAmount.Value) Then
        MsgBox "The amount box must contain a number.", vbExclamation, "Purchase Orders"
        Me.txtPOAmount.SetFocus
        Exit Sub
    End If
    
        Sheets("Purchase Orders").Unprotect
    
    RowCount = Worksheets("Purchase Orders").Range("A4").CurrentRegion.Rows.Count
    
    With Worksheets("Purchase Orders").Range("A4")
        .Offset(RowCount, 2).Value = DateValue(Me.txtDate.Value)
        .Offset(RowCount, 1).Value = Me.txtName.Value
        .Offset(RowCount, 3).Value = Me.txtPayTo.Value
        .Offset(RowCount, 4).Value = Me.cboPOTypes.Value
        .Offset(RowCount, 5).Value = Me.txtPOAmount.Value
        .Offset(RowCount, 8).Value = Me.txtNotes.Value
    End With
        
    'Clear input controls.
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
    Next ctl
    
    Sheets("Purchase Orders").Protect
    
    MsgBox "Purchase order has been saved."
    
End Sub


Private Sub CloseForm_Click()
    'Close UserForm.
    Unload Me
End Sub

This is the code for the command buttons on the 'main page':

Private Sub AddNewIndividual_Click()
    If MsgBox("This requires Board authorisation. Has the Board approved this addition?", vbYesNo) = vbNo Then
Exit Sub
End If
    frmNewIndividual.Show
End Sub


Private Sub AddNewPO_Click()
        frmNewPO.Show
End Sub
        
        
Private Sub PrintPO_Click()


  With Worksheets("Print PO")
    .PageSetup.BlackAndWhite = False
    .PrintOut
  End With
    
With Worksheets("Print PO")
    .PageSetup.BlackAndWhite = True
    .PrintOut
  End With


End Sub

Hope that helps to clarify a bit.

 

[scrawltome]

UserForm1.TextBox1.Value = Sheets("Purchase Orders").Cells(Rows.Count, "A").End(xlUp).Value + 1

I tried this code, as I thought it may work, but I'm getting an error. Run time error 13: Type mismatch. I must have adjusted the code wrongly for my workbook. I'll keep trying.

 

[scrawltome]

I have typed the code:

frmNewPO.txtPONumber.Value = Sheets("Purchase Orders").Cells(Rows.Count, "A").End(xlUp).Value + 1

However, do you think I should put the code within the userbox code or in the command button code?

 

[scrawltome]

SOLVED! A genius helped me to solve it with this code:

<code>Private Sub AddNewPO_Click()
    With Sheets("Purchase Orders")
       frmNewPO.txtPONumber.Text = "POER" & Right(.Range("A" & .Rows.Count).End(xlUp).Value, 4) + 1
    End With
    frmNewPO.Show
End Sub</code>