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
0
lynn1 (103)
8/21/2006 6:07:02 PM
excel 39879 articles. 2 followers. Follow

7 Replies
245 Views

Similar Articles

[PageSpeed] 26

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


0
bob.NGs1 (1661)
8/21/2006 6:29:51 PM
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


0
innocent (844)
8/21/2006 6:35:13 PM
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
> 
> 
> 
0
lynn1 (103)
8/21/2006 7:10:02 PM
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
> >
> >
> >


0
bob.NGs1 (1661)
8/21/2006 8:15:27 PM
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.

0
hrlngrv (1990)
8/21/2006 10:01:33 PM
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.
>


0
innocent (844)
8/22/2006 3:27:18 PM
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.

0
hrlngrv (1990)
8/22/2006 5:33:06 PM
Reply:

Similar Artilces:

transfer one stock to another broker account
How do I transfer only one stock from one broker's account to another broker's account ?? All I can find is directions to transfer ALL the stocks in one account to another account. Have Money 2003 Deluxe, Version 11.0 Thanks In microsoft.public.money, Karl S wrote: >How do I transfer only one stock from one broker's account >to another broker's account ?? All I can find is >directions to transfer ALL the stocks in one account to >another account. Have Money 2003 Deluxe, Version 11.0 Enter a TransferOut transaction for the stock in the investment acc...

Only one customer in letter writing assistant
Great Plains version 8 - When I run the letter writing assistant to create a Collection Letter or a Customer Letter and I select All customers and All customer types I only get one customer in the customer list to choose from. It doesn't matter what letter I am trying to create I only get one customer to pick from. Does anyone know what I need to do to get all my customers to show up? This is normal behavior if you are launching the letter assistant from the customer maintenance window. Launch it from the reports menu to perform mass mailings. I have tried launching the letter ...

Automatically insert date and time in a side note
I know I can insert the current date and time in a note or side not by using sft+alt+f but would like to automate this so that the current date and time are automatically inserted into a side note when I create one by pressing the windows key + n. ...

Phone field should allow for 5 digit extension number.
Many companies, including Microsoft, have 5 digit extensions for their phone numbers. i.e xxx-xxx-xxxx ext. xxxxx Dynamics is limited to 4. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/...

Mailbox creation Times
Hi everyone, Is there anyway to speed up mailbox creation in Exchange 2003? It takes at least a Half a day from the time i click Create new mailbox in outlook until i actually see the new mailbox in ESM. Does anyone have any ideas? I have around 200 mailboxes on a single server. 4 storage groups with 4 message stores in each group. Server is not even breaking a sweat.... the mailbox will not appear in "mailbox resources" until the user logs into it, or a piece of email is sent to it... "Jeff" <jeff.chase@bepco.com> wrote in message news:0f4e01c4a661$8511...

Timing of payroll year end tax update installation
I am trying ot find out if the 2007 year end tax update for GP v9 can be applied prior to running the final payroll for the year. If this is just a form update can it be done early to avoid so much stress at the end of the year? Has anyone tried this? (I do not mean the new tax table update for 2008) -- Diane Burbank Dynamics GP Consultant Yes, it can be applied at any time. However, it is not just a form update. It includes service pack 3. The installation will require all users be out of GP during the procedure. All workstations must be updated. Make backups of the databases and the...

Convert Time "12:00:00 AM" into "12:00"
I have a table with time in this format "12:00:00 AM". I 'd like to extract the time in these two formats 1) 12:00 (12-hour) 2) 12:00 (24-hour) Thanks a lot, dear experts ! -- Allen Phailat Wongakanit "ali" <ali@discussions.microsoft.com> wrote in message news:991B1441-9E1B-42C8-A3E5-AF8061D4EDD7@microsoft.com... >I have a table with time in this format "12:00:00 AM". > > > I 'd like to extract the time in these two formats > > 1) 12:00 (12-hour) > 2) 12:00 (24-hour) Tables don't store formatted values. Th...

Favicon
Hi, I think I have found a small bug in IE 8 and I was wondering if anyone could confirm this was happening to them. Steps to reproduce: 1) You need HTTP Watch, or IE Watch, or Fiddler running. 2) Pick a site and add it to you list of sites for compatibility mode. 3) Visit the site with your http watcher running. Bug: If you look at your user agent, it will be "compatible; MSIE 7.0 (etc.)" for all of your requests, EXCEPT for the request to favicon.ico, which will be "compatible; MSIE 8.0 (etc.)" This bug is more annoying than it seems, because ...

how to force user not to enter "%" sign into a TextBox.
I need to force user not to enter "%" sign into a text box. OnExit event of text box, which code sould i use, so that I will make the CANCEL=True? I can do that by placing ComboBox and defining a criteria on the query where combobox is bounded. But i want to learn how to do in VBA. There are lots of information about it on this group but i failed to understand. Thanks mezzanine1974 wrote: > I need to force user not to enter "%" sign into a text box. OnExit > event of text box, which code sould i use, so that I will make the > CANCEL=True? > I can do that by pla...

display sums from multiple worksheets on one worksheet
I am working on a budget plan. So I have multiple totals from various months that I would like to display on one "totals" page so i can track each month. for example, I have the totals from bikes, cars, and transit on the november worksheet. on the "totals" page I have the totals from bikes, cars, and transit for every month of the year. i kept the names consistent from each page hoping that would simplify things. so far it has not. is there a way to display the totals from each month on the "totals" without having to type a similar formula in eac...

Mail-Enabled User Not Appearing in GAL
Hey folks. Strange query.. using Win2K3Ent and Exch2k3Ent, with latest SPs and hotfixes installed on both. Trying to create a mail-enabled user (giving impression of internal user actually aliased to an external SMTP address). I'm under the impression (confirmed by checking 70-284) that a mail-enabled user should appear in the GAL after a short while, and be available for clients such as Outlook to select directly (yet be aliased at the postoffice level). No matter what I try, I cannot get the user to appear in the GAL. Using ExchSysMgr, if I query the GAL, the user appears happ...

Some users cannot have replied E-Mail activity? Why?
Hi, It is really strange, in our company, some users's replied e-mail activity will not be put into CRM system by that router. I tested everybody by my internet mailbox. But meanwhile, some other users without problem. Anybody know that? Thank you very much. Raymond From what I understand... if you reply using outlook - it will not go through the CRM router and be placed in CRM... you must reply outside of outlook.... (i.e. hotmail, external account)... I think there is a TK on this... "Raymond" <xin421@yahoo.com> wrote in message news:015d01c3dc66$e19ca420$a401...

Time formulae
Hi I use these formulae to insert the date and time in separate cells on a worksheet : A14 =TODAY() B14 =NOW()-TODAY() They update automatically when I open the sheet. It would be better however if they didn't do this until I use the Refresh All function. Is it possible to stop these functions updating until I need them to do so? I know I can switch global options off in the Options section of the program , but I'm loathe to do this for the sake of this one sheet. Grateful for any advice. ...

Date time picker control's format in resource editor
This problem relates to the resource editor in Visual Studio .NET 2002/2003 If you create a date time picker control in resource editor and set its format to “time”, save and close the resource editor, when you open the resource editor next time, the format of that date time picker control will always be reset to “short date”. In a word, it cannot keep the “time” format. Antbody know how to get around it Thanks! >This problem relates to the resource editor in Visual Studio .NET 2002/2003: > >If you create a date time picker control in resource editor and set its format to �time�, ...

One IP-address
Our exchange 2003-server hosts two domains A.com and B.com. So, the mx-record for BOTH domains point to the same host (mailhost.A.com) and resolves the corresponding IP-addres. The PTR-record for this IP-address obviously points back to the host mailhost.A.com. All seems to work fine for both domains for the moment, but will this not present a problem in the future for mail from the B.com domain when more and more receiving hosts do reverse lookup ? Or is reverse lookup just verifying the FQDN in the mail-header, and not the senders domain ? Regards Rudy Steyaert HI, NO i don't think ...

how can I allow others to post comments on a PDF document?
i sent out a publisher document in PDF and one of my collegues wanted to add some edits electronically (kind of like electronic post it notes) - but apparently I have to allow that first? How does this all work? You need Acrobat or some third party software that allows comments. Office's add-in doesn't do that. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "pulseyouth" <pulseyouth@discussions.microsoft.com> wrote in message news:3F3ABA73-4B99-4BC4-A914-7732F3BB122D@microsoft.com... >i sent out a ...

Update one Worksheet from Another
Hi, I want to be able to update a worksheet by looking up data from another worksheet. in other words; If Servername in Worksheet A column A equals Servername in Worksheet B Column C then copy serial number from Worksheet B column D to Worksheet A column D. Thanks in advance for any help. OldDog Hi you can use the VLOOKUP function to achieve this. in Worksheet A column D type =VLOOKUP(A1,WorksheetB!$C$1:$D$1000,2,0) this will return the information from column D of Worksheet B where there is an exact match between the data in column A of the current sheet and column C of worksheet B....

Printing just one page?
I would like to print just one page in my publication, at times. When I hit file, print, I do not have a print option. Print merge, print preview are all that is available. Can anyone help? Please?? What version Publisher? When you are ready to print one page, there should be check off for "current page". Are you expanding the File menu? Right-click any toolbar (Publisher 2002), customize, toolbars tab, check *Always show full menus." What happens if you ctrl+p or click the print icon on the toolbar? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msau...

System Time Range on WIndows
Hi All, Can any body please tell me what is the system time range on windows system. what i could get it start is as per UTC since midnight (00:00:00), January 1, 1970. Please correct if i am wrong? rgds, Ritu >Can any body please tell me what is the system time range on windows system. There are several time formats - which one are you referring to? The main one is FILETIME - which is an unsigned 64-bit value that represents the number of 100-nanosecond units since the 1'st January 1601. The other commonly used format is SYSTEMTIME, which is a time structure limited s...

not user if this is a bug that was fixed or my memory gone bad...
I year or two ago I created a mail merge into Publisher from MS Access that has text and photos. It's a directory with photos and text below... What I ended up with then, that I want to do again is have the pages lay out like the following. Photo1 photo 2 Photo1 photo 2 Photo3 photo 4 Photo3 photo 4 Photo5 photo 6 Photo5 photo 6 Photo7 photo 8 Photo7 photo 8 Photo9 photo 10 Photo9 photo 10 The page layout is landscape with two columns (portrate) This worked great as I could print off all 30+ pages,...

Getting 552 Requested mail action aborted: (from one user on Hotmail)
I have a home user that is using a Hotmail account to communicate with our company. She can email us perfectly fine when she composes a new message...however, when she attempts to reply to a message sent from us, she is getting back and undeliverable message as follows: name@domain.com SMTP error from remote mailer after end of data: host mail.domain.com [xx.x.xxx.xx]: 552 Requested mail action aborted: exceeded storage allocation She gets this message even with no attachements. The host domain name and IP address are both correct as well as the email address (obviously since...

User Manager -> Adding Users
Has anyone else seen the case where each Active Directory user is listed twice in the Import Users Wizard in the Deployment manager? Rick. ...

Run Time Error 7
I have been using Excel for a long time now , but today I just received an error message when trying to open any Excel file I have.... I t get an Visual Memory Basic box that pops up and it says "Run-Time Error 7 - Out of Memory".... I don't quite jknow what this means can anyone help???? I am not running any other program at the time and only want to run Excel...My OS is Win XP...... Any ideas on this, let me know...... ...

Close command taking long time
My form has a Exit button which takes excessively long time to close the form. I have tried both macro and VBA code such as following without any success. Private Sub Exit_Click() On Error GoTo Err_Exit_Click DoCmd.RunCommand acCmdSaveRecord DoCmd.Close Exit_Exit_Click: Exit Sub Err_Exit_Click: MsgBox Err.Description Resume Exit_Exit_Click End Sub What could be the problem? Matuag, The only thing I can think of is have you used the Compact and Repair Database Utility lately? Hunter57 http://easymsaccess.blogspot.com (Simple Access Tips) http://www.ChurchManageSo...

Changing Multiple Users Details
We are using Exchange 2003 SP2 and I need to change all our users company info and addresses, as we have moved physical locations... Is there a way to change all users information in one simple procedure? Not sure if this is an Exchange thing or a AD thing... Thanks in advance guys... I think ADModify.net might do what you want http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=f5cbbfa9-e46b-4a7a-8ed8-3e44523f32e2 "Chris H" <ChrisH@discussions.microsoft.com> wrote in message news:76A45444-7337-4C50-B536-34C4F6F9EBA5@microsoft.com... > We are using Exchange 2003...