I have an application that relies on two .xlsm files. The files are pbcadmin.xlsm (this is the user front end that reads and writes data from pbcdb.xlsm) and pbcdb.xlsm (This is the master database file where all data lives.).
The problem I am having is when users have additional Excel workbooks open at the same time they are logged into my application. It appears their workbook sometimes gets the focus rather than my workbook. They have a couple of problems:
1. Their workbook is opened as a Read Only file.
2. Their workbook is saved and protected with the password from my application. This means when they try to enter data in their workbook, they get an error that the sheet is protected and asked for my password which they don't know.
3. They will get a Subscript Out of Range error because my application cannot find the worksheet or variable it is looking for because their workbook has the focus.
I have tried to manage my application by activating whichever of my workooks are open and needed at the moment. You will see an example in the code below. The function for GetCompensationDatabase is used everytime data is written to or pulled from the main pbcdb.xlsm file. If I am reading and writing back and forth, I leave the pbcdb.xlsm file open and switch back and forth between the pbcadmin.xlsm file until I am done and then I close the pbcdb.xlsm file.
This is a large application with about 125,000 lines of code, which means I use this functionality a lot. It works fine within my application, but the problem is when users have additional Excel workbooks open at the same time they are logged in.
Any assistance in how to manage open workbooks to ensure the correct workbook has the focus will be greatly appreciated.
Please Login or Register to view this content.
Bookmarks