PDA

View Full Version : Updating shared workbook



mdmackillop
03-17-2005, 03:56 PM
Based on the KB item here http://www.vbaexpress.com/kb/getarticle.php?kb_id=351 it would seem to be more useful the Workbook was simply a shared workbook. I've yoinked a snippet of Johnske's code to write the invoice number to the "Sales" sheet.
The problem is, if the book is being shared and is open on more than one PC, although users get the correct invoice number, the info saved to the Sales sheet is not updated and a conflict occurs on saving the workbook.
Any suggestions?

Grade4.2
12-09-2022, 05:39 AM
If you are using a shared workbook in Microsoft Excel and multiple users are editing the same file at the same time, you may run into conflicts when saving the workbook. This is because each user has their own copy of the file on their own computer, and when they save their changes, the changes are only saved to their own copy of the file.


One way to avoid this issue is to use a different method for storing the invoice number. Instead of using a cell in the "Sales" sheet to store the invoice number, you could use a custom property in the workbook itself. Custom properties are pieces of information that are associated with the workbook and can be accessed by all users who have access to the file.


Sub AddCustomProperty()
' Declare variables
Dim wb As Workbook
Dim cp As CustomProperty


' Set the variable to the active workbook
Set wb = ActiveWorkbook


' Add a custom property to the workbook
Set cp = wb.CustomProperties.Add("InvoiceNumber", _
False, msoPropertyTypeNumber, 1)
End Sub





Press F5 to run the code. This will add a custom property called "InvoiceNumber" to the workbook.
You can then use the following code to update the value of the custom property whenever a new invoice is created:


Sub UpdateInvoiceNumber()
' Declare variables
Dim wb As Workbook
Dim cp As CustomProperty


' Set the variable to the active workbook
Set wb = ActiveWorkbook


' Get the custom property
Set cp = wb.CustomProperties("InvoiceNumber")


' Update the value of the custom property
cp.Value = cp.Value + 1
End Sub




This code will increment the value of the "InvoiceNumber" custom property by 1 each time it is run. You can then use this value as the invoice number in your code. Because the custom property is associated with the workbook, all users who have access to the file will see the same value for the invoice number, and conflicts should be avoided.


I hope this helps. Let me know if you have any other questions.