Users locking spreadsheet

[ashgaroth]

I have a spreadsheet that I use for merging for details to word forms on a regular basis.

My problem is that a colleague often opens the spreadsheet to obtain data and this then stops me from either using the spreadsheet to merge or from entering new data (using a user form).

Is there someway that I can allow him access to read the file whilst I can still use it to update/add information and also for merging?

 

[ashgaroth]

Thanks for the enormous effort put in by so many people to offer suggestions!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Phanmore]

Password protect the file when saving it so that you need to enter the password to modify it.

A simple google search will tell you how to do this.

 

[ashgaroth]

Thanks for the suggestion, Phanmore, but I have already tried that and it does not solve my problem it merely stops him from updating the spreadsheet but the spreadsheet remains locked for use by everyone else.

Myself and 2 other colleagues use the spreadsheet for merging purposes but cannot do so if my other colleague has it open for data extraction. Neither is it available for more than one user at a time for merging.

I am sure that this can be done using VBA but I cannot find the answer anywhere, If it can't then I can't see the point of VBA as a programming language.

 

[Phanmore]

How are you protecting the file? Are you using the tools function when saving the workbook?

Try this as follows:

Open the file, go to save as (F12). This will open the file save window. Then on the top right hand side you will see a drop down menu labelled tools. Click on this and go to General Options. Enter a password in the password to modify box. This will then only allow people with the password to open the document without it being read only. You should then be able to make changes, or merge from this document without isse, as any user who does not have this password is only able to open a copy of this document.

I currently use this on many spreadsheets and it has always allowed me to edit the file when another read only copy is open.

Let me know if this helps.

 

[ashgaroth]

I cannot see that anywhere in Word 2007. I did F12 to get the save as dialog box but the only things on the top right hand side are:
The back buttons
The delete button (greyed out)
New Folder
List View

 

[Charlie45]

Look for the Tools buttonon the lower left side after you hit the F12. Use the dropdown to get to General Options