Controlling Sequential Numbers

[littlegreenmen1]

I have been researching using sequential numbers for invoices an
purchase orders, but all of them seem to advance the number eithe
every time the document is opened or saved. My purchase order is par
of a larger file, and since I will be opening and saving the fil
without necessarily writing a purchase order I need more control ove
its advancement. I was wondering how I could create a button that whe
pressed or selected would advance the number by 1? Also, how could
make it create a unique number depending on who's creating the purchas
order. For example, let's say the user is "John Smith" how could I hav
the P.O. number be somthing like "JS-0001"? Thank you very much i
advance for your help

 

[Guest]

You could easily create a button from the control toolbox (go to view >
toolbars) on your sheet. Right-click the button, go to View Code, and paste
this into the window:

Private Sub CommandButton1_Click()

Dim strName As String 'name entered
Dim nSpacePos As Long 'position of space in name
Dim rngPOCell As Range 'location of PO number
Dim strNameIni As String 'name initials
Dim strNewPO As String 'new PO number
Dim strCurrPONum As String 'current PO number
Dim strInputMsg As String 'message to user

Set rngPOCell = ActiveSheet.[A1] 'Change to target cell

strInputMsg = "Enter your first and last name. " & Chr(10) & _
"Make sure to include a space between the names. " & Chr(10) & _
"If you have multiple first names and/or last " & Chr(10) & _
"names, use the first word only. For example, " & Chr(10) & _
"Mary Kate Van Gretten would be Mary Van."

strName = InputBox(strInputMsg)
If strName = "" Then Exit Sub
If Len(strName) < 3 Then
MsgBox "Invalid Name."
Exit Sub
ElseIf IsError(Application.Find(" ", strName)) Then
MsgBox "Invalid Name."
Exit Sub
End If

nSpacePos = InStr(strName, " ")
strNameIni = UCase(Left(strName, 1)) & _
UCase(Mid(strName, nSpacePos + 1, 1))

With rngPOCell
If .Value = "" Then
.Value = strNameIni & "-0001"
Else
strCurrPONum = Format(Right(.Value, 4) + 1, "0000")
.Value = strNameIni & "-" & strCurrPONum
End If
End With

End Sub

---
This will place a new PO # in A1.

HTH
Jason
Atlanta, GA

 

[littlegreenmen1]

Thank you so much. That worked perfectly. I really appreciate it