+ Reply to Thread
Results 1 to 3 of 3

work distribution through macros. Simultaneous users

  1. #1
    Registered User
    Join Date
    07-27-2011
    Location
    nottingham. england
    MS-Off Ver
    Excel 2007
    Posts
    2

    Unhappy work distribution through macros. Simultaneous users

    Hi all,

    I've got a bit of a conundrum. I'm fairly rusty with VBA and I need to create a work distribution system that allows users to run a macro on their worksheet which takes a row from a centralised spreadsheet. Each row is a unique piece of work and so must only be taken once.

    At this stage I will note that this is an interim process for work distribution and due to an abundance of restrictions placed down upon me, there is no option to use anything but excel. Not an ideal situation but hopefully you can help!

    So far I have set up the central spreadsheet and password protected it.
    I have created a front end worksheet that accesses the central sheet, grabs a unique piece of work, updates a column to ensure the same row doesn't get taken by subsequent runs and then saves.

    This works fine.

    The problem I encounter is when 2 network users simultaneously click to run the macro. One of them gets a spreadsheet busy error and a custom retry message, which I am comfortable with. The other user gets a password pop up followed by a 13 type mismatch error.

    I repeat that this only occurrs when running simultaneously. When a single user runs it, the password is not requested (as I set the password early on in my code) and it moves the data without a glitch.

    My desired result is that when both users click at the same time, one of the users successfully gets the piece of work as normal and the other gets the busy message.

    I'm perplexed

    Please help!
    Last edited by megapist; 07-27-2012 at 03:44 AM. Reason: typo and additional info

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: work distribution through macros. Simultaneous users

    Have you got an
    On Error GoTo ErrLabel

    line of code, and

    Exit Sub
    ErrLabel:
    MsgBox "Workbook is in use. Try later"
    End Sub

    as the last lines of code
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-27-2011
    Location
    nottingham. england
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: work distribution through macros. Simultaneous users

    Please Login or Register  to view this content.


    Here is the sub. There's a couple more in the module but they just identify the relevant source spreadsheet to copy from and attach it to a button.
    Last edited by Cutter; 07-27-2012 at 07:59 AM. Reason: Added code tags

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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