User form to assign an autonumber to spreadsheet - Any Ideas pleas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On my user form I have text boxes for "JobNo", "Job Description", and Date.
To prevent "JobNo" duplication I need to restrict the user from allocating
job numbers to different jobs instead, I want Excel to assign an auto number
to the spreadsheet . Any ideas on how to achieve this?
 
The way I get around this is to give a cell a range name like "AutoNum". Set
this cell to the last job number used + 1. Whenever the user clicks the
button to add a job, you have your new job number then just have your code
increase the range by 1 for the next available job number. When you load the
form, or refresh it after an addition, I load the "AutoNum" into a label
rather than a text box. This prevents the user from changing it. You can
hide it if necessary, or if it's none of their business, you don't even need
to load it, just use it in your calculations. HTH. - Z
 
If you keep track of the issued job numbers in a sorted list then you can
make it issue a job number one higher than the last.

RBS
 
I see where you are goming from Z, except I have no predefined job numbers
since my projects have a different number of jobs per project. Some projects
have three tasks while others have twenty individual tasks. Your help is
valued,
Cheers, JM
 
How are the job numbers being generated? Are the users just making them up
as they go? The only way I can think of avoiding duplicate job numbers is
for you to control where the are coming from. Either store and generate them
yourself, or dump them into a database that will not allow dupes. Need more
info to help you.
 
I will try to explain by way of example.
The project estimator go to customer and assess the project. The project
will then have an ID number assigned to it. The project alone will dictate
how many seperate sub tasks or "Task Numbers" are involved. The user form
that that the project manager face has TextBoxes for "TaskNo",
"TaskDescription" and "TaskStartDate". At the moment he allocates the task
number himself, which is not a problem if the project consists of 5 tasks,
but when it has 20 or more it is easy to duplicate numbers. I am trying to
avoid this by removing the "TaskNo" TextBox from the user form and assigning
a number to a task automatically.
 
I don't understand why the project manager is assigning these numbers. If
there are 20 tasks involved, why can't you just issue a sequential number to
the task field. What is to keep one project estimator from call this project
6 and another doing the same. If the tasks are always 1-20, you could assign
them to the project ID-task number like 6-1, 6-2, 6-3, 6-4, etc. It still
seems easier to me to just assign a sequential transaction number for every
task to avoid a number being used twice. I don't quite see how those are
coming into play. I'm sorry I'm not being much help. Is this data
eventually being posted to a database?
 
Back
Top