Sequential numbers
 

Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals, which then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts, by each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put into the same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send to each
person.
This menu item (Contract Number) would be triggered to activate the use of
the next number.
I can't have it issue a number each time it is opened, or each time it is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey example that
many sites are directed too. I get a "feel" for what it is to do, but don't
understand how (and where) to set it all up and how to actually activate it.
Jasion Morin example of entering the salesman name works great, but does not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear. (not fair, but
what can I do)
thanks in advance.

Sequential numbers
 

Need some clarification and info

1. Are these contract numbers to be used in a single workbook or several
workbooks
(for each sales person)
2. What is the starting number (first number)
3. How do you want to tell excel to make a number
entry; button, ....
4. What ever else you can tell about what you want...
(the more details, the more we can do...)

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals, which then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts, by each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put into the
same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send to each
person.
This menu item (Contract Number) would be triggered to activate the use of
the next number.
I can't have it issue a number each time it is opened, or each time it is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey example that
many sites are directed too. I get a "feel" for what it is to do, but
don't
understand how (and where) to set it all up and how to actually activate
it.
Jasion Morin example of entering the salesman name works great, but does
not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear. (not fair,
but
what can I do)
thanks in advance.

Sequential numbers
 

Sorry I was not clear enough.
Each salesman will be having their own number sequence,
starting with SI (Salesman's Initials)0001
This sequence would be used ONLY for that salesman, but with up to 12
workbooks.
(Thats why I don't want to assign the numbering sequence to A work-book)
I would like to tell Excel when to activate the number sequence macro by a
menu item, which I will attach to my current custom menu.

For interest sake, the following is one way of setting up how to add the
salesmans initials to the contract number (thanks tyo Jason Morin). It works
great, but does not meet the rest of my requirements.
I would hesitate to get the name(s) or initials out of the registry (if that
were possible), since I see too often where computers are shifted around
without the names being changed.

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

I hope this is clearer.
============

"STEVE BELL" wrote:

Need some clarification and info

1. Are these contract numbers to be used in a single workbook or several
workbooks
(for each sales person)
2. What is the starting number (first number)
3. How do you want to tell excel to make a number
entry; button, ....
4. What ever else you can tell about what you want...
(the more details, the more we can do...)

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals, which then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts, by each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put into the
same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send to each
person.
This menu item (Contract Number) would be triggered to activate the use of
the next number.
I can't have it issue a number each time it is opened, or each time it is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey example that
many sites are directed too. I get a "feel" for what it is to do, but
don't
understand how (and where) to set it all up and how to actually activate
it.
Jasion Morin example of entering the salesman name works great, but does
not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear. (not fair,
but
what can I do)
thanks in advance.

Sequential numbers
 

Beejay,

I am sure that there are many simpler ways to do this. But my approach
would be
to create a "master workbook" for each sales person.
The user would open this workbook and initiate all the macros from there.

You can either make it a regular .xls or and add-in .xla

You can have the initial number = 1 stored in a cell or a name and than
changed it each time
it is required to change.

Than in your code:

ContractNum = "SPI" & Format(num + 1, "0000")

where "SPI" = sales persons initials (this could also be turned into a
stored variable
num is a reference to the stored value

ContractNum = SPI & Format(num + 1, "0000") ' if SPI is a stored variable

than
num = num + 1

than store num

let me know if this works for you or if you need further help...
--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Sorry I was not clear enough.
Each salesman will be having their own number sequence,
starting with SI (Salesman's Initials)0001
This sequence would be used ONLY for that salesman, but with up to 12
workbooks.
(Thats why I don't want to assign the numbering sequence to A work-book)
I would like to tell Excel when to activate the number sequence macro by a
menu item, which I will attach to my current custom menu.

For interest sake, the following is one way of setting up how to add the
salesmans initials to the contract number (thanks tyo Jason Morin). It
works
great, but does not meet the rest of my requirements.
I would hesitate to get the name(s) or initials out of the registry (if
that
were possible), since I see too often where computers are shifted around
without the names being changed.

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

I hope this is clearer.
============

"STEVE BELL" wrote:

Need some clarification and info

1. Are these contract numbers to be used in a single workbook or
several
workbooks
(for each sales person)
2. What is the starting number (first number)
3. How do you want to tell excel to make a number
entry; button, ....
4. What ever else you can tell about what you want...
(the more details, the more we can do...)

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals, which
then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts, by
each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put into the
same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send to each
person.
This menu item (Contract Number) would be triggered to activate the use
of
the next number.
I can't have it issue a number each time it is opened, or each time it
is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey example
that
many sites are directed too. I get a "feel" for what it is to do, but
don't
understand how (and where) to set it all up and how to actually
activate
it.
Jasion Morin example of entering the salesman name works great, but
does
not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear. (not
fair,
but
what can I do)
thanks in advance.

Sequential numbers
 

Steve: Thanks for your efforts, so far.
It might be best if I restate my situation.

I have 15 salesmen.
Each salesman gets multiple workbooks (12).
Each work-book is up-dated regularily.
Each work-book can be used as a QUOTE, or as a CONTRACT.
Each salesman will be getting an update version of PRICE SHEET.XLA
which puts a new (custom) menu on their screen.
This menu has various hide/unhide, print and help sub-levels.
I want to add to this menu:
1: Insert QUOTE Number
2: Insert CONTRACT Number
The numbers issued should be sequential, but seperate for each catagory.
ie: QUOTE Numbers could be prefixed by "Q"
Starting number Q-0001
Contract Numbers could be prefixed by "C"
Starting Number C-0001
I also need a method where the salesman enters his initials, which
then forms part of the Quote or Contract number: ie: JS-Q-0001 or JS-C-0001.

I can handle the menu modifications, but desperately need beginners
level help for the sequential numbering processes.
Any required macros will be added to PRICE SHEET.XLA
Can we use "ThisWorkBook" to store and save starting number(s) and Last
number used? (if this is required)

If we can work thru the process for QUOTEs, then it shouldn't take much to
save a copy as Contract, and modify as required.

I hope this is enough of a challenge that you haven't lost interest.
Looking forward to your response.

"STEVE BELL" wrote:

Beejay,

I am sure that there are many simpler ways to do this. But my approach
would be
to create a "master workbook" for each sales person.
The user would open this workbook and initiate all the macros from there.

You can either make it a regular .xls or and add-in .xla

You can have the initial number = 1 stored in a cell or a name and than
changed it each time
it is required to change.

Than in your code:

ContractNum = "SPI" & Format(num + 1, "0000")

where "SPI" = sales persons initials (this could also be turned into a
stored variable
num is a reference to the stored value

ContractNum = SPI & Format(num + 1, "0000") ' if SPI is a stored variable

than
num = num + 1

than store num

let me know if this works for you or if you need further help...
--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Sorry I was not clear enough.
Each salesman will be having their own number sequence,
starting with SI (Salesman's Initials)0001
This sequence would be used ONLY for that salesman, but with up to 12
workbooks.
(Thats why I don't want to assign the numbering sequence to A work-book)
I would like to tell Excel when to activate the number sequence macro by a
menu item, which I will attach to my current custom menu.

For interest sake, the following is one way of setting up how to add the
salesmans initials to the contract number (thanks tyo Jason Morin). It
works
great, but does not meet the rest of my requirements.
I would hesitate to get the name(s) or initials out of the registry (if
that
were possible), since I see too often where computers are shifted around
without the names being changed.

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

I hope this is clearer.
============

"STEVE BELL" wrote:

Need some clarification and info

1. Are these contract numbers to be used in a single workbook or
several
workbooks
(for each sales person)
2. What is the starting number (first number)
3. How do you want to tell excel to make a number
entry; button, ....
4. What ever else you can tell about what you want...
(the more details, the more we can do...)

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals, which
then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts, by
each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put into the
same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send to each
person.
This menu item (Contract Number) would be triggered to activate the use
of
the next number.
I can't have it issue a number each time it is opened, or each time it
is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey example
that
many sites are directed too. I get a "feel" for what it is to do, but
don't
understand how (and where) to set it all up and how to actually
activate
it.
Jasion Morin example of entering the salesman name works great, but
does
not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear. (not
fair,
but
what can I do)
thanks in advance.

Sequential numbers
 

Beejay,

Sounds like a tall order.

But the numbering part is relatively easy.

Your code needs to store the numbers somewhere in the workbook. Either as
workbook level names, or worksheet level names. Or in hidden cells.
You will need 2 of these - one for quote and one for contract.

Let's say the numbers are on a hidden sheet, with has the name "Num". Range
A1 has quote # and B1 has contract number.
Just to make things interesting - in cell A2 enter the formula =A1+1 and in
B2 enter = B1+1. Enter 1 is A1 & B1.
Some won't put a formula on row 2, but have the code do something like
Range("A1").Value = Range("A1").Value + 1

The code might look like the below.
Use similar code for contract number.
(you could also modify this code to do either type...)
And add provision for errors (like if no initials are returned.)
================================================== ======
Dim qtn as String, spi as String, qn as Integer

spi = Ucase(InputBox "Enter your initials")

qn = format(Sheets("Num").Range("A1"),"0000")

qtn = spi & "-Q-" & qn

Sheets("Num").Range("A1").Value = Sheets("Num").Range("A2").Value
================================================== ======

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Steve: Thanks for your efforts, so far.
It might be best if I restate my situation.

I have 15 salesmen.
Each salesman gets multiple workbooks (12).
Each work-book is up-dated regularily.
Each work-book can be used as a QUOTE, or as a CONTRACT.
Each salesman will be getting an update version of PRICE SHEET.XLA
which puts a new (custom) menu on their screen.
This menu has various hide/unhide, print and help sub-levels.
I want to add to this menu:
1: Insert QUOTE Number
2: Insert CONTRACT Number
The numbers issued should be sequential, but seperate for each
catagory.
ie: QUOTE Numbers could be prefixed by "Q"
Starting number Q-0001
Contract Numbers could be prefixed by "C"
Starting Number C-0001
I also need a method where the salesman enters his initials,
which
then forms part of the Quote or Contract number: ie: JS-Q-0001 or
JS-C-0001.

I can handle the menu modifications, but desperately need beginners
level help for the sequential numbering processes.
Any required macros will be added to PRICE SHEET.XLA
Can we use "ThisWorkBook" to store and save starting number(s) and Last
number used? (if this is required)

If we can work thru the process for QUOTEs, then it shouldn't take much to
save a copy as Contract, and modify as required.

I hope this is enough of a challenge that you haven't lost interest.
Looking forward to your response.

"STEVE BELL" wrote:

Beejay,

I am sure that there are many simpler ways to do this. But my approach
would be
to create a "master workbook" for each sales person.
The user would open this workbook and initiate all the macros from there.

You can either make it a regular .xls or and add-in .xla

You can have the initial number = 1 stored in a cell or a name and than
changed it each time
it is required to change.

Than in your code:

ContractNum = "SPI" & Format(num + 1, "0000")

where "SPI" = sales persons initials (this could also be turned into a
stored variable
num is a reference to the stored value

ContractNum = SPI & Format(num + 1, "0000") ' if SPI is a stored
variable

than
num = num + 1

than store num

let me know if this works for you or if you need further help...
--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Sorry I was not clear enough.
Each salesman will be having their own number sequence,
starting with SI (Salesman's Initials)0001
This sequence would be used ONLY for that salesman, but with up to 12
workbooks.
(Thats why I don't want to assign the numbering sequence to A
work-book)
I would like to tell Excel when to activate the number sequence macro
by a
menu item, which I will attach to my current custom menu.

For interest sake, the following is one way of setting up how to add
the
salesmans initials to the contract number (thanks tyo Jason Morin). It
works
great, but does not meet the rest of my requirements.
I would hesitate to get the name(s) or initials out of the registry (if
that
were possible), since I see too often where computers are shifted
around
without the names being changed.

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

I hope this is clearer.
============

"STEVE BELL" wrote:

Need some clarification and info

1. Are these contract numbers to be used in a single workbook or
several
workbooks
(for each sales person)
2. What is the starting number (first number)
3. How do you want to tell excel to make a number
entry; button, ....
4. What ever else you can tell about what you want...
(the more details, the more we can do...)

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals, which
then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts, by
each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put into
the
same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send to
each
person.
This menu item (Contract Number) would be triggered to activate the
use
of
the next number.
I can't have it issue a number each time it is opened, or each time
it
is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey example
that
many sites are directed too. I get a "feel" for what it is to do,
but
don't
understand how (and where) to set it all up and how to actually
activate
it.
Jasion Morin example of entering the salesman name works great, but
does
not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear. (not
fair,
but
what can I do)
thanks in advance.

Sequential numbers
 

Steve,
If I'm reading your info correctly, I would have to put a seperate sheet in
with each contract for your example to work. That is not an option.
If I'm understanding you incorrectly, could you please explain how each
contract is going to interact with the seperate file that stores the contract
and quote numbers AND how does it remember what the last used number is?

Perhaps you would be so kind as to re-read the criteria.
While the set-up I'm looking for is way beyond my level and capabilities,
(which I've tried to indicate all along), I didn't think it would be all
that difficult for an experienced VBA user.

I hope you can help me out, and/or perhaps someone else would like to have a
crack at this.
Thanks

"STEVE BELL" wrote:

Beejay,

Sounds like a tall order.

But the numbering part is relatively easy.

Your code needs to store the numbers somewhere in the workbook. Either as
workbook level names, or worksheet level names. Or in hidden cells.
You will need 2 of these - one for quote and one for contract.

Let's say the numbers are on a hidden sheet, with has the name "Num". Range
A1 has quote # and B1 has contract number.
Just to make things interesting - in cell A2 enter the formula =A1+1 and in
B2 enter = B1+1. Enter 1 is A1 & B1.
Some won't put a formula on row 2, but have the code do something like
Range("A1").Value = Range("A1").Value + 1

The code might look like the below.
Use similar code for contract number.
(you could also modify this code to do either type...)
And add provision for errors (like if no initials are returned.)
================================================== ======
Dim qtn as String, spi as String, qn as Integer

spi = Ucase(InputBox "Enter your initials")

qn = format(Sheets("Num").Range("A1"),"0000")

qtn = spi & "-Q-" & qn

Sheets("Num").Range("A1").Value = Sheets("Num").Range("A2").Value
================================================== ======

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Steve: Thanks for your efforts, so far.
It might be best if I restate my situation.

I have 15 salesmen.
Each salesman gets multiple workbooks (12).
Each work-book is up-dated regularily.
Each work-book can be used as a QUOTE, or as a CONTRACT.
Each salesman will be getting an update version of PRICE SHEET.XLA
which puts a new (custom) menu on their screen.
This menu has various hide/unhide, print and help sub-levels.
I want to add to this menu:
1: Insert QUOTE Number
2: Insert CONTRACT Number
The numbers issued should be sequential, but seperate for each
catagory.
ie: QUOTE Numbers could be prefixed by "Q"
Starting number Q-0001
Contract Numbers could be prefixed by "C"
Starting Number C-0001
I also need a method where the salesman enters his initials,
which
then forms part of the Quote or Contract number: ie: JS-Q-0001 or
JS-C-0001.

I can handle the menu modifications, but desperately need beginners
level help for the sequential numbering processes.
Any required macros will be added to PRICE SHEET.XLA
Can we use "ThisWorkBook" to store and save starting number(s) and Last
number used? (if this is required)

If we can work thru the process for QUOTEs, then it shouldn't take much to
save a copy as Contract, and modify as required.

I hope this is enough of a challenge that you haven't lost interest.
Looking forward to your response.

"STEVE BELL" wrote:

Beejay,

I am sure that there are many simpler ways to do this. But my approach
would be
to create a "master workbook" for each sales person.
The user would open this workbook and initiate all the macros from there.

You can either make it a regular .xls or and add-in .xla

You can have the initial number = 1 stored in a cell or a name and than
changed it each time
it is required to change.

Than in your code:

ContractNum = "SPI" & Format(num + 1, "0000")

where "SPI" = sales persons initials (this could also be turned into a
stored variable
num is a reference to the stored value

ContractNum = SPI & Format(num + 1, "0000") ' if SPI is a stored
variable

than
num = num + 1

than store num

let me know if this works for you or if you need further help...
--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Sorry I was not clear enough.
Each salesman will be having their own number sequence,
starting with SI (Salesman's Initials)0001
This sequence would be used ONLY for that salesman, but with up to 12
workbooks.
(Thats why I don't want to assign the numbering sequence to A
work-book)
I would like to tell Excel when to activate the number sequence macro
by a
menu item, which I will attach to my current custom menu.

For interest sake, the following is one way of setting up how to add
the
salesmans initials to the contract number (thanks tyo Jason Morin). It
works
great, but does not meet the rest of my requirements.
I would hesitate to get the name(s) or initials out of the registry (if
that
were possible), since I see too often where computers are shifted
around
without the names being changed.

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

I hope this is clearer.
============

"STEVE BELL" wrote:

Need some clarification and info

1. Are these contract numbers to be used in a single workbook or
several
workbooks
(for each sales person)
2. What is the starting number (first number)
3. How do you want to tell excel to make a number
entry; button, ....
4. What ever else you can tell about what you want...
(the more details, the more we can do...)

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals, which
then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts, by
each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put into
the
same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send to
each
person.
This menu item (Contract Number) would be triggered to activate the
use
of
the next number.
I can't have it issue a number each time it is opened, or each time
it
is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey example
that
many sites are directed too. I get a "feel" for what it is to do,
but
don't
understand how (and where) to set it all up and how to actually
activate
it.
Jasion Morin example of entering the salesman name works great, but
does
not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear. (not
fair,
but
what can I do)
thanks in advance.

Sequential numbers
 

Here are some snippets of code to store numbers in worksheet names.
(No special worksheets are needed). See if they can get you started.

ActiveWorkbook.Names.Add Name:="Quote", RefersToR1C1:="=10"
ActiveWorkbook.Names.Add Name:="Contract", RefersToR1C1:="=25"
[or you can just add the names manually to initiate the value]

Now here is a snippet to pull out the value of a name and add a one to it

x = Val(Right(Names("quote"), Len(Names("quote")) - 1)) ' with the above
x = 10
ActiveWorkbook.Names.Add Name:="Quote", RefersToR1C1:=x + 1 ' revised
quote = 11

For further help check out this site:
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

There are many ways to create an increasing number in a form (or worksheet)
The most common choice is to store the number in a hidden cell and have your
code add 1 to that cell each time it runs.

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Steve,
If I'm reading your info correctly, I would have to put a seperate sheet
in
with each contract for your example to work. That is not an option.
If I'm understanding you incorrectly, could you please explain how each
contract is going to interact with the seperate file that stores the
contract
and quote numbers AND how does it remember what the last used number is?

Perhaps you would be so kind as to re-read the criteria.
While the set-up I'm looking for is way beyond my level and capabilities,
(which I've tried to indicate all along), I didn't think it would be all
that difficult for an experienced VBA user.

I hope you can help me out, and/or perhaps someone else would like to have
a
crack at this.
Thanks

"STEVE BELL" wrote:

Beejay,

Sounds like a tall order.

But the numbering part is relatively easy.

Your code needs to store the numbers somewhere in the workbook. Either
as
workbook level names, or worksheet level names. Or in hidden cells.
You will need 2 of these - one for quote and one for contract.

Let's say the numbers are on a hidden sheet, with has the name "Num".
Range
A1 has quote # and B1 has contract number.
Just to make things interesting - in cell A2 enter the formula =A1+1 and
in
B2 enter = B1+1. Enter 1 is A1 & B1.
Some won't put a formula on row 2, but have the code do something like
Range("A1").Value = Range("A1").Value + 1

The code might look like the below.
Use similar code for contract number.
(you could also modify this code to do either type...)
And add provision for errors (like if no initials are returned.)
================================================== ======
Dim qtn as String, spi as String, qn as Integer

spi = Ucase(InputBox "Enter your initials")

qn = format(Sheets("Num").Range("A1"),"0000")

qtn = spi & "-Q-" & qn

Sheets("Num").Range("A1").Value = Sheets("Num").Range("A2").Value
================================================== ======

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Steve: Thanks for your efforts, so far.
It might be best if I restate my situation.

I have 15 salesmen.
Each salesman gets multiple workbooks (12).
Each work-book is up-dated regularily.
Each work-book can be used as a QUOTE, or as a CONTRACT.
Each salesman will be getting an update version of PRICE SHEET.XLA
which puts a new (custom) menu on their screen.
This menu has various hide/unhide, print and help sub-levels.
I want to add to this menu:
1: Insert QUOTE Number
2: Insert CONTRACT Number
The numbers issued should be sequential, but seperate for each
catagory.
ie: QUOTE Numbers could be prefixed by "Q"
Starting number Q-0001
Contract Numbers could be prefixed by "C"
Starting Number C-0001
I also need a method where the salesman enters his initials,
which
then forms part of the Quote or Contract number: ie: JS-Q-0001 or
JS-C-0001.

I can handle the menu modifications, but desperately need beginners
level help for the sequential numbering processes.
Any required macros will be added to PRICE SHEET.XLA
Can we use "ThisWorkBook" to store and save starting number(s) and Last
number used? (if this is required)

If we can work thru the process for QUOTEs, then it shouldn't take much
to
save a copy as Contract, and modify as required.

I hope this is enough of a challenge that you haven't lost interest.
Looking forward to your response.

"STEVE BELL" wrote:

Beejay,

I am sure that there are many simpler ways to do this. But my
approach
would be
to create a "master workbook" for each sales person.
The user would open this workbook and initiate all the macros from
there.

You can either make it a regular .xls or and add-in .xla

You can have the initial number = 1 stored in a cell or a name and
than
changed it each time
it is required to change.

Than in your code:

ContractNum = "SPI" & Format(num + 1, "0000")

where "SPI" = sales persons initials (this could also be turned into
a
stored variable
num is a reference to the stored value

ContractNum = SPI & Format(num + 1, "0000") ' if SPI is a stored
variable

than
num = num + 1

than store num

let me know if this works for you or if you need further help...
--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Sorry I was not clear enough.
Each salesman will be having their own number sequence,
starting with SI (Salesman's Initials)0001
This sequence would be used ONLY for that salesman, but with up to
12
workbooks.
(Thats why I don't want to assign the numbering sequence to A
work-book)
I would like to tell Excel when to activate the number sequence
macro
by a
menu item, which I will attach to my current custom menu.

For interest sake, the following is one way of setting up how to add
the
salesmans initials to the contract number (thanks tyo Jason Morin).
It
works
great, but does not meet the rest of my requirements.
I would hesitate to get the name(s) or initials out of the registry
(if
that
were possible), since I see too often where computers are shifted
around
without the names being changed.

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

I hope this is clearer.
============

"STEVE BELL" wrote:

Need some clarification and info

1. Are these contract numbers to be used in a single workbook or
several
workbooks
(for each sales person)
2. What is the starting number (first number)
3. How do you want to tell excel to make a number
entry; button, ....
4. What ever else you can tell about what you want...
(the more details, the more we can do...)

--
steveB

Remove "AYN" from email to respond
"BEEJAY" wrote in message
...
Require a process to obtain sequential numbers.
To run indepentantly on each salesmans machine.
To have a method where salesman has to input His/Her initals,
which
then
forms the prefix of the contract number.
The contract numbers to be used by about 12 different contracts,
by
each
salesman, so attaching to each workbook is not an option.
I would send each salesman a file called SeqNum.xls, to be put
into
the
same
directory on everyones machine: c:\Excel Add_Ins\SeqNum.xls.
I would attach a menu item to the special menu tree that I send
to
each
person.
This menu item (Contract Number) would be triggered to activate
the
use
of
the next number.
I can't have it issue a number each time it is opened, or each
time
it
is
closed.

My skill is so low, I cannot get anywhere with the McGimpsey
example
that
many sites are directed too. I get a "feel" for what it is to do,
but
don't
understand how (and where) to set it all up and how to actually
activate
it.
Jasion Morin example of entering the salesman name works great,
but
does
not
meet my other requirements, as far as I can tell.
Any detailed help would be greatly appreciated.
I'm at my wits end and the pressure is being brought to bear.
(not
fair,
but
what can I do)
thanks in advance.