+ Reply to Thread
Results 1 to 8 of 8

Only allow one user at a time

  1. #1
    Lynn
    Guest

    Only allow one user at a time

    We have an Excel spreadsheet that sits on the network.
    People need to open the file to be able to sign up for various duty rosters.
    We would like for the file to open for the first person.
    And then for any others after that, get a message that the file is in use
    WITHOUT the option to open a read-only copy.
    Our staff can't read and they keep opening additional copies of the file!
    I have read about sharing the file and I don't think that would make things
    any better.
    Thanks,
    Lynn

  2. #2
    Bob Phillips
    Guest

    Re: Only allow one user at a time

    Private Sub Workbook_Open()
    If ThisWorkbook.ReadOnly Then
    MsgBox "File already in use"
    ThisWorkbook.Close savechanges:=False
    End If
    End Sub

    'This is workbook event code.
    'To input this code, right click on the Excel icon on the worksheet
    '(or next to the File menu if you maximise your workbooks),
    'select View Code from the menu, and paste the code


    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    news:38A765B3-7C2C-4D19-861E-1B04E9D674CE@microsoft.com...
    > We have an Excel spreadsheet that sits on the network.
    > People need to open the file to be able to sign up for various duty

    rosters.
    > We would like for the file to open for the first person.
    > And then for any others after that, get a message that the file is in use
    > WITHOUT the option to open a read-only copy.
    > Our staff can't read and they keep opening additional copies of the file!
    > I have read about sharing the file and I don't think that would make

    things
    > any better.
    > Thanks,
    > Lynn




  3. #3
    Harald Staff
    Guest

    Re: Only allow one user at a time

    Hi Lynn

    You would need macros running to prevent that I think. Perhaps checking if
    the file is readonly in the workbook_open event and close it there, or some
    barrier in the before_save event.

    People can't read, that is true. If they expect to see a worksheet and they
    see a dialog instead (often containing a text they don't understand), they
    close the litle red X to make the dialog go away so that they hopefully can
    see what they wanted to see. That is human, even the smartest people do
    that, as developers we just have to live with it.

    HTH. Best wishes Harald

    "Lynn" <Lynn@discussions.microsoft.com> skrev i melding
    news:38A765B3-7C2C-4D19-861E-1B04E9D674CE@microsoft.com...
    > We have an Excel spreadsheet that sits on the network.
    > People need to open the file to be able to sign up for various duty

    rosters.
    > We would like for the file to open for the first person.
    > And then for any others after that, get a message that the file is in use
    > WITHOUT the option to open a read-only copy.
    > Our staff can't read and they keep opening additional copies of the file!
    > I have read about sharing the file and I don't think that would make

    things
    > any better.
    > Thanks,
    > Lynn




  4. #4
    Lynn
    Guest

    Re: Only allow one user at a time

    Bob, Thanks so much. This works great.
    Any chance you know how to change the macro security level on the fly?
    Lynn

    "Bob Phillips" wrote:

    > Private Sub Workbook_Open()
    > If ThisWorkbook.ReadOnly Then
    > MsgBox "File already in use"
    > ThisWorkbook.Close savechanges:=False
    > End If
    > End Sub
    >
    > 'This is workbook event code.
    > 'To input this code, right click on the Excel icon on the worksheet
    > '(or next to the File menu if you maximise your workbooks),
    > 'select View Code from the menu, and paste the code
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    > news:38A765B3-7C2C-4D19-861E-1B04E9D674CE@microsoft.com...
    > > We have an Excel spreadsheet that sits on the network.
    > > People need to open the file to be able to sign up for various duty

    > rosters.
    > > We would like for the file to open for the first person.
    > > And then for any others after that, get a message that the file is in use
    > > WITHOUT the option to open a read-only copy.
    > > Our staff can't read and they keep opening additional copies of the file!
    > > I have read about sharing the file and I don't think that would make

    > things
    > > any better.
    > > Thanks,
    > > Lynn

    >
    >
    >


  5. #5
    Bob Phillips
    Guest

    Re: Only allow one user at a time

    You can't. Think about it, it would be pretty useless if you could.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    news:3278C27D-C261-401F-BBA6-ABE90255BD83@microsoft.com...
    > Bob, Thanks so much. This works great.
    > Any chance you know how to change the macro security level on the fly?
    > Lynn
    >
    > "Bob Phillips" wrote:
    >
    > > Private Sub Workbook_Open()
    > > If ThisWorkbook.ReadOnly Then
    > > MsgBox "File already in use"
    > > ThisWorkbook.Close savechanges:=False
    > > End If
    > > End Sub
    > >
    > > 'This is workbook event code.
    > > 'To input this code, right click on the Excel icon on the worksheet
    > > '(or next to the File menu if you maximise your workbooks),
    > > 'select View Code from the menu, and paste the code
    > >
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (replace somewhere in email address with gmail if mailing direct)
    > >
    > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message
    > > news:38A765B3-7C2C-4D19-861E-1B04E9D674CE@microsoft.com...
    > > > We have an Excel spreadsheet that sits on the network.
    > > > People need to open the file to be able to sign up for various duty

    > > rosters.
    > > > We would like for the file to open for the first person.
    > > > And then for any others after that, get a message that the file is in

    use
    > > > WITHOUT the option to open a read-only copy.
    > > > Our staff can't read and they keep opening additional copies of the

    file!
    > > > I have read about sharing the file and I don't think that would make

    > > things
    > > > any better.
    > > > Thanks,
    > > > Lynn

    > >
    > >
    > >




  6. #6
    Harlan Grove
    Guest

    Re: Only allow one user at a time

    Bob Phillips wrote...
    >Private Sub Workbook_Open()
    > If ThisWorkbook.ReadOnly Then
    > MsgBox "File already in use"
    > ThisWorkbook.Close savechanges:=False
    > End If
    >End Sub

    ....

    Easily defeated by disabling macros.

    There are times when an indirect approach works best. Using one
    workbook or a script to open a different workbook means you can insist
    the user run macros to get at the workbook of interest, and those
    macros could check for the existence of temporary marker files to see
    if the workbook of interest were already in use or not.


  7. #7
    Harald Staff
    Guest

    Re: Only allow one user at a time

    Right you are, Harlan. This is one of the better places to use a
    "switchboard workbook". If not save the data externally that is.

    The problem in these cases is to hide the real file good enough, so that
    users can't open it within Word :-)

    Best wishes Harald

    "Harlan Grove" <hrlngrv@aol.com> skrev i melding
    news:1156197693.820570.162480@m79g2000cwm.googlegroups.com...

    > There are times when an indirect approach works best. Using one
    > workbook or a script to open a different workbook means you can insist
    > the user run macros to get at the workbook of interest, and those
    > macros could check for the existence of temporary marker files to see
    > if the workbook of interest were already in use or not.
    >




  8. #8
    Harlan Grove
    Guest

    Re: Only allow one user at a time

    Harald Staff wrote...
    >Right you are, Harlan. This is one of the better places to use a
    >"switchboard workbook". If not save the data externally that is.
    >
    >The problem in these cases is to hide the real file good enough, so that
    >users can't open it within Word :-)

    ....

    More secure using compiled EXEs. Files on Windows file servers can be
    executable but not listable. Further, they could be executable under
    different credentials than the users. It's not that difficult to make
    files effectively invisible to and unexecutable by users except via
    specific EXE or script interfaces. 'Not that difficult' in the context
    of someone as comfortable manipulating file system permissions, that
    is, but IT staff should be able to figure that out.


+ 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