+ Reply to Thread
Results 1 to 5 of 5

"Select method of worksheet class failed" w/ multiple workbooks open

  1. #1
    Registered User
    Join Date
    05-26-2010
    Location
    Connecticut, USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    "Select method of worksheet class failed" w/ multiple workbooks open

    Hello everyone,

    I have a workbook I use to keep statistics (Excel 2007, attached) that I'm continually tinkering with and trying to improve (so you may have seen it before). Clicking each button adds one to a specific cell pertaining to the desk transaction type for a particular hour. The workbook sits open on the desktop at all times, and work fine as long as it's the only workbook open. However, if a user wishes to work on a different Excel workbook, it and my Tallybot will bang heads.

    I know where the problem is springing from: I added a module that checks the time once a minute, and updates a cell to display the correct time, then saves itself. It's a feature I'd like to keep, because it reduces confusion as to when data (a copy of the "Results Sheet" worksheet) has last been saved to a shared drive, and whether or not to save the master file to the hard drive at the end of the day. The code in the problem module is:

    Please Login or Register  to view this content.
    As I said, it works fine if it's the only workbook open, but when any other workbook is open, once the update minute rolls around, I get "Run-time error '1004': Select method of worksheet class failed." When I click debug, the problem line is:
    Please Login or Register  to view this content.
    I've tried a couple of other commands in place of that line, and the closest I've come to success was:
    Please Login or Register  to view this content.
    The problem there being if you're working in the other workbook you're forced over to the Tallybot and left there.

    Is there a command that will tell the VBA module to run the TimeUpdate subroutine only on the Tallybot workbook, and to leave any other open Excel file alone?

    Thanks so much,

    -Hester's Dad
    Attached Files Attached Files
    Last edited by Hester's Dad; 10-15-2010 at 10:22 AM. Reason: marked solved

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: "Select method of worksheet class failed" w/ multiple workbooks open

    No need to select at all:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    05-26-2010
    Location
    Connecticut, USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: "Select method of worksheet class failed" w/ multiple workbooks open

    Aha...with instead of select. Thanks romperstomper. This works much better.

    One thing's still puzzling me, though: I get a flicker when the Tallybot updates its time and I'm in another workbook. Turning off screen updating helped -- it's just the briefest flicker and a fraction of a second of hourglass, and then all is back to normal -- so if it can't be eliminated entirely I think it can be lived with. What's odd, though, is the flicker is happening every 30 seconds or so instead of once a minute like I'd expect. Is it because the macro is running in both workbooks, and each is calling over to the tallybot on its own once per minute cycle? Can the macro be told to run only on the tallybot and not on any other workbooks?

    -Hester's Dad

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: "Select method of worksheet class failed" w/ multiple workbooks open

    The macro should only be running on that workbook. It maybe that the Save is also triggering the hourglass.

  5. #5
    Registered User
    Join Date
    05-26-2010
    Location
    Connecticut, USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: "Select method of worksheet class failed" w/ multiple workbooks open

    For grins I added the new code to the workbooks on the actual service desk machines, and monitored their behavior with additional workbooks open for an hour. They all worked as they were supposed to, updating once per minute and no more. Flicker and hourglass were minimal to barely noticeable. When I returned to my office and opened the demonstrator plus an additional workbook, it also behaved properly, updating and saving only the intended workbook once per minute. I don't know what sort of time warp I was caught in earlier this morning, but it's passed now.

    Thanks again. I appreciate the help.

    -H.D.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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