Prevent other users from selecting next available auto-populate cell
 

I have a user form that auto-populates a text box, the data to populate
the text box comes from a separate workbook, we'll call this workbook
Quotelog.xls.
When the user form is opened, I select the next available quotation
no., from Quotelog.xls in column A I have the quotation no. example in
A1 Quote-001, in cell A2 Quote-002, A3 = Quote-003 etc. etc.
The next available quotation number is selected by going to the last
record in column B then I offset the row by 1 and column by -1. After
completing the user form inputs the data on the form is then copied to
the Quotelog.xls workbook, again I search on column B for the last
record then offset by 1 row down. In principle this scheme works fine
if only one user was to use the user form, unfortunately this is not
the case.
It suddenly ocurred to me, that if I open the user form, which would
auto-populate the text box, how would I know that some other user
hasn't already selected that quotation no., or another user select the
quotation no. I'm currently working with, remember a quotation number
is available until such time that the user form data is copied over to
Quotelog.xls This is possible because multiple user forms exist but all
pull from the same Quotelog.xls for the quotation no. Lastly I would
need to make sure that the data I'm copying over to Quotelog.xls
actually goes into the correct row.

Any ideas.

Prevent other users from selecting next available auto-populate cell
 

Hi Burl,

See J.E. McGimpsey's suggestions at:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html


---
Regards,
Norman


"burl_rfc_h" wrote in message
ups.com...
I have a user form that auto-populates a text box, the data to populate
the text box comes from a separate workbook, we'll call this workbook
Quotelog.xls.
When the user form is opened, I select the next available quotation
no., from Quotelog.xls in column A I have the quotation no. example in
A1 Quote-001, in cell A2 Quote-002, A3 = Quote-003 etc. etc.
The next available quotation number is selected by going to the last
record in column B then I offset the row by 1 and column by -1. After
completing the user form inputs the data on the form is then copied to
the Quotelog.xls workbook, again I search on column B for the last
record then offset by 1 row down. In principle this scheme works fine
if only one user was to use the user form, unfortunately this is not
the case.
It suddenly ocurred to me, that if I open the user form, which would
auto-populate the text box, how would I know that some other user
hasn't already selected that quotation no., or another user select the
quotation no. I'm currently working with, remember a quotation number
is available until such time that the user form data is copied over to
Quotelog.xls This is possible because multiple user forms exist but all
pull from the same Quotelog.xls for the quotation no. Lastly I would
need to make sure that the data I'm copying over to Quotelog.xls
actually goes into the correct row.

Any ideas.