Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Controlling Sequential Numbers
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Thank you so much. That worked perfectly. I really appreciate it. -- littlegreenmen1 ------------------------------------------------------------------------ littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978 View this thread: http://www.excelforum.com/showthread...hreadid=376479 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a list of sequential numbers using Excel or Acces. | Excel Discussion (Misc queries) | |||
How do I assign sequential numbers in an Excel 2003 PO template? | Excel Worksheet Functions | |||
How can i print sequential page numbers 1 to 10, if i have one wo. | Excel Discussion (Misc queries) | |||
list of sequential numbers | Excel Discussion (Misc queries) | |||
How do I take two columns of sequential numbers and insert spaces | Excel Discussion (Misc queries) |