Controlling Sequential Numbers

[littlegreenmen1]

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

[Jason Morin]

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" wrote:

>
> I have been researching using sequential numbers for invoices and
> purchase orders, but all of them seem to advance the number either
> every time the document is opened or saved. My purchase order is part
> of a larger file, and since I will be opening and saving the file
> without necessarily writing a purchase order I need more control over
> its advancement. I was wondering how I could create a button that when
> pressed or selected would advance the number by 1? Also, how could I
> make it create a unique number depending on who's creating the purchase
> order. For example, let's say the user is "John Smith" how could I have
> the P.O. number be somthing like "JS-0001"? Thank you very much in
> advance for your help.
>
>
> --
> littlegreenmen1
> ------------------------------------------------------------------------
> littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978
> View this thread: http://www.excelforum.com/showthread...hreadid=376479
>
>

[littlegreenmen1]

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