ExcelBanter - Controlling Sequential Numbers

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