vba Handling error when multiple users are using cell value in procedure

0
votes

Excuse me if I am asking already handled problem. I am trying to automate the invoicing of our company. We have an excel workbook with all the information about the clients, monthly payments and etc. I ve made the system and it works great. I even sent email with specific text and the invoice attached. The problem comes when multiple users are using the system, so the invoice numbers duplicate. Invoice number is stored in the excel workbook and after every invoice it becomes + 1.

The system works like this: Login form - login with username. The macro copies from the master table/modified protected/ only the info for the users clients and paste it into new wb template. The user checks the info, approves it and clicks send button. Then the macro make the invoices using the number from the first wb and send the email to the client. Do you have any tips how to protect the invoice number cell when someone is using the macro? I was thinking of making a list of users and when someone logs in his variable becomes 1, so if the sum of all users is greater than 1 you cant issue an invoice. But thats lame. Hope there is a way like- when busy ..... But how can user 2 know that user1 is issuing. Appreciate if you can help.

2
excelvba
Am I correct to assume that multiple users can open and use one and the same Excel file at once? If so, I am furthermore assuming that the file is hosted on a file server and that people open it from this network share. If so, then Excel is essentially creating a local copy of the file and people are working on that. Only when the file is saved these changes are written back to the file server. Isn't there a database server around (i.e. MS SQL) to help you with multi-user-access? Otherwise, I'd suggest the use of an MS Access database file. – Ralph

2 Answers

0
votes

I'm not entirely clear on the exact process you're using to get the invoice number here, but it sounds like the number is being retrieved from another sheet and then incremented by one, correct?

If so, the best option might be to record each invoice as you go - as soon as the macro grabs an invoice number, it records a new line of data with the new invoice number on it. That way the next person to use the macro, even if it's only seconds later, would get the next number in the sequence, rather than the same number. The process would be something like:

lngLast = wbkOtherWorkbook.range("A" & rows.count).end(xlup).row
lngInvoiceNum = wbkOtherWorkbook.range("A" & lnglast).value +1
wbkOtherWorkbook.range("A" & lnglast+1).value = lngInvoiceNum

I'd imagine you'll want to keep a list of all your invoices somewhere anyway - just include this step in the recording process.

0
votes

thank you guys, i ll try to explain better. So only I can edit the master table, everybody is opening a read only copy of the wb. The invoices after issued are saved in the coresponding folder - one for each client. The number for the invoice is the tricky part. Today I made two possible solutions: 1. I made a function, when someone is issuing, first his own macro counts the invoices he is going to make. Then opens different /third wb/ with the invoice number, takes the value and adds the invoices count which will be made. So I only lose 1 sec or something. Also made a loop while isworkbookopen(numberInvoice)=true do events wend. This way the number is always correct. 2. The other option I havent tested yet is sharing the trird wb /numberInvoice/ so anyone can use and manipulate it simultaniously. Dont understand if sharing a wb is going to work, but thats diffrent topic.

So I am going to take Ralph advice and I am looking forward to learn some Access database stuff. Wish you all the best!

P.S. working with vba for like a month or two, so Im kind a newb. Thank you for understanding.