I have written an excel vb routine in the commandbutton_click() event. That routine saves some info. picked up from various cells in sheet1 in the this directory: \\LOGCALL\USERSTATS\userstats.log.
Many users will be doing the same thing over the network. It is possible for two people to save his/her work at the same time. It there some code I can add to prevent a comflict or a way I can lock the *.log file that needs updating and make the other wait in line until the file is unlocked?
make your workbook a shared Workbook. See 'Tools->Share Workbook' With a shared workbook Excel will handle conflicts and advise if 2 users attempt to update a value. If you have many users this may be problematic, then you may consider using an Access Database for your needs. You can not edit VB code in a shared workbook, you'll have to switch to non-shared mode or keep you code in a separate workbook from the UI book.
How do you do that? --> "..or keep you code in a separate workbook from the UI book." How do you make the User Interface common to all and the VB code in another workbook?
Code does not have to be in the same workbook as your data or the same workbook the user sees. You can refer to another worksheet that is not active, or another worksheet in another workbook or the active workbook for example ActiveSheet.Cells(2,3) works on the active sheet as long as you give the correct reference for the state of the application. Excel requires at least 1 worksheet in a workbook. If I wish to have a code workbook that works on other workbooks, I don't want the user to see the code book or be able to mess with it. You can hide the workbook from view close and save it in the hidden state, set the file properties to read only, since it is only code there's no reason to edit after deploying. attaching a toolbar to the code book can allow access to the procedures/functions.
Here's a senario: you have three workbooks one is a control panel file, One is a Working File, One is a Process or code file.
controlPnl.xls (code file has menu, and toolbar logic, code, hidden, Readonly)
workdata.xls (file users access to view edit data, data, visible, Read Write)
workdataProc.xls (Process for automation of workdata, code file, hidden, ReadOnly)
You load the controlPnl.xls at XL startup as an addin or put a shortcut in the users XLStart folder. This displays a custom menu and toolbar, showing all the stuff you use, the file is hidden so the user only sees the toolbar and menu, the code is available but transparent to the user.
When the user selects the menu item or TB button to "Open Work Data" the workdata.xls file opens and the workdataProc.xls file opens. Because workdataProc is hidden the user only sees workdata and any custom toolbar and menu you decide to show for access to automation.
In the code files you have a OnClose event that deletes the custom Toolbars and Menus. This way if you update the code file and place it in the public location they get all the new menus etc. when they open it without any old stuff lingering.
So you should get the idea. you can separate code from the data, you can make a modular structure where code for specific tasks are in separate workbooks, you can ease maintenance because you can mirror development files to the production files, you can update files in a public folder without having to distribute to the individual user machines, you can have a master control panel to give a menu for access to all the user files and utilities.