Macro to not allow opening of file only on certain computer if it is already opened

Good morning all;

I have a computer that users are regularly opening a read only copy of a file because it is opened elsewhere. The users then proceed to edit the file. I need a macro that will not allow the excel file to be opened (at all) on that particular workstation if it is already opened by any other workstation. However, if the file is opened already, I do want other workstations to be able to open a read only copy of the file.

Thank you

I copied/pasted this code into the Open event in the workbook and replaced the "workstationname" in quotes to the computer name of the computer that I don't want to be able to open a read only copy of the file. I compiled the code and saved the file.

I then opened the workbook on my laptop and then tried to open it on the computer whose name was mentioned above. There was the usual message about someone having the file open, and the dialog box asked if I wanted to open a read only copy. I selected yes and it opened without further fanfare.

I'm going to look further but did I mis-read your post and do something wrong? Doesn't seem that the file is "seeing" that code.

I did leave the quotes. I had tried it without the quotes also. I am using Win7 and XP as test machines. I do get the notice that the file is in use, but can select the open read only option and the file opens.

Here is a screen clip of the VBA code. Maybe you can see something wrong there?
http://i686.photobucket.com/albums/v...xcel/Excel.jpg

hmmm;

I did have both computers Excel macro settings set to "Low" security, so that it runs any and all macros. But this solution is not a macro, right? I can set the macro security to "disable all macros with notification" and the file opens without any mention of a macro.

In other files I've created macros in I would normally get a message about whether or not to allow the macros. I don't get any such warnings on this file.

Is this supposed to be a macro? I just plugged in the code where you described earlier and compiled it and saved the file.

well this has got me stumped as well. I've tried turning off macros, setting the various macro security levels, etc. I have it ask me to enable macros and when the file opens it asks to enable macros like it should.

For whatever reason the macro just isn't firing. Is there anything you can think that I've missed here or is there a diag tool to see why the macro isn't firing?

Originally Posted by davegugg

The code is considered a macro. If you set the security to give you a notification when macros are detected, Excel should notify you. Any time you run vba code it is considered a macro (for our purposes). The fact that you are not getting a warning is confusing to me. Why not try adding a message box in the beginning of the procedure to make sure it is firing when the workbook opens?

I will try this.

I also copied the file from my network location over to my local hard drive. Same "non" results. I'll let you know about the message box idea.

Dave;

I created a message box and when I open the file, I DO see my message box open up so something is not quite right with how my system is interacting with the code. Can you take a look at the screenshot in my above post to make sure I plugged that code in where/how I should have?

Dan;

I added msgboxes to all of the comments in the code. See below. The last message box that shows on the screen when the file is opened elsewhere is the one saying "Check if the computer opening the file is the workstation........" so it appears the code is not running past that point.

Please Login or Register  to view this content.