+ Reply to Thread
Results 1 to 3 of 3

Need help managing multiple open workbooks to give the focus to the correct workbook

  1. #1
    Registered User
    Join Date
    02-18-2017
    Location
    Dallas
    MS-Off Ver
    Windows 10
    Posts
    17

    Need help managing multiple open workbooks to give the focus to the correct workbook

    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.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Need help managing multiple open workbooks to give the focus to the correct workbook

    When setting ranges or worksheets, it is best to specify the workbook as well, which many of us often omit. I see you often use "Excel." which in a line like this I don't know it helps:
    Please Login or Register  to view this content.
    I assume Excel is equivalent to Application, but have never used it in that context (not saying it is wrong).

    However, if another book has focus at the time I suspect either the wrong sheet is activated, or if it doesn't exist then you get a subscript out of range error. By adding a workbook reference, that issue is resolved:

    Please Login or Register  to view this content.
    you could put Excel. in front of ThisWorkbook if needed.

    The key here is that the workbook object, like the worksheet object, is assumed to be the Active one unless specified. For example, when we say Range("A1") in code, what we really tell the program is ActiveSheet.Range("A1"). The same is true for workbooks, when not specified, it is as if we typed ActiveWorkbook....
    Last edited by Arkadi; 10-12-2017 at 01:45 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Need help managing multiple open workbooks to give the focus to the correct workbook

    Using names can solve that problem: Something like this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-02-2017, 01:58 PM
  2. [SOLVED] Set Focus on form at workbook open
    By rob_h in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-17-2017, 12:14 PM
  3. [SOLVED] Open Multiple Workbooks Copy Sheet1 To Master Workbook
    By Tellm in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-11-2016, 12:08 PM
  4. [SOLVED] Open Multiple Workbooks, Record names, Copy paste to Active Workbook, Close the Workbooks
    By vba_madness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 06:09 AM
  5. Add data from multiple workbooks that are open and save it in a new workbook
    By harry_b83 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2012, 03:14 AM
  6. Workbook doesn't open (Lost Focus)
    By Marc2010 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-24-2010, 01:44 PM
  7. How to open additional workbooks WITHOUT taking focus?
    By Phil_V in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-07-2008, 09:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1