Solved

Disable read only

Posted on 2011-02-23
17
282 Views
Last Modified: 2012-05-11
Hi,

I want to be able to disable read only, is this possible?

So multiple users can be in the spreadsheet at the same time without having to be in as read only

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 8
  • 3
  • 3
  • +1
17 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959553
You need to turn on the Read Only file attribute.

However, this will not allow anyone to save changes. If you want multiple users to be able to update the workbook at the same time you have to turn on workbook sharing.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959582
The following Excel features are unavailable when a workbook is shared:

Insert or delete blocks of cells, merge cells or split merged cells, or delete or copy worksheets. (Entire columns and rows can be inserted.)

Add or change conditional formats or data validation.

Insert automatic subtotals, create data tables, or change or delete array formulas.

Create or change charts, PivotChart, and PivotTable reports.

Insert or change pictures or other objects, or use drawing tools.

Insert or change hyperlinks.

Assign, change, or remove passwords, or protect or unprotect worksheets or the workbook.

Create, change, or view scenarios, or group or outline data. (Existing outlines created at the time the workbook was shared can be used.)

Write, record, change, view, or assign macros, or add or change Excel 4 dialog sheets. (Existing macros that don't access unavailable features can be run. Macros operating on a shared workbook can be recorded into another non-shared workbook.)

If any of the above actions need to be taken, unshare the workbook (Tools->Share Workbook, uncheck "Allow changes..."), make the changes, and the reshare the workbook (Tools->Share Workbook, check "Allow changes...").

Kevin
0
 

Author Comment

by:Seamus2626
ID: 34959586
Turn it on?

The reason i need to the spreadsheet not to be read only is so i can track mulitple users on the spreadsheet. The code to capture that data wont work if the file is in read only mode. So i need read only to be constantly off.

users dont update these reports, they are strictly for viewing

Thanks
Seamus
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959595
Then set the read only file attribute on. This allows the workbook to be opened by multiple people without getting the warning dialog. Sounds counter intuitive but it works.

Kevin
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34959600
Hello Seamus,

as Kevin says, you need to enable workbook sharing if you want multiple users to change a workbook simultaneously.

Be warned, though, that Shared Workbooks are not very reliable and it is only a matter of time until they become corrupt, don't work as expected, throw errors, show different things to different users, etc.

Have a google for "Excel shared workbook site:experts-exchange.com" and read what the common consensus is about shared workbooks.u

Don't. Use. Them.

If you need a solution that allows multiple users to enter data into a scenario, use a true multi-user application, i.e. a multi-user database like Access, MySql or similar.

Shared Excel workbooks will fail you in the long run.

OK.

Now you've read it and you can't say you haven't been warned.

cheers, teylyn
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959610
Do not use workbook sharing. You don't need this feature.

Kevin
0
 

Author Comment

by:Seamus2626
ID: 34959627
I see what you guys are saying but what im looking for is to be able capture the usernames and time in the spreadsheet of different users. i cant do this if the spreadsheet is read only. The message box is irrelevant for me.

Could i share the workbook while not allowing any changes to be made?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959649
>Could i share the workbook while not allowing any changes to be made?

Yes. You will have to lock down the workbook using workbook and worksheet security.

The problems associated with workbook sharing will not be an issue for you.

A worksheet can be protected in such a way that only cells that need to be changed can be changed. Protecting a worksheet involves two different settings or attributes: worksheet protection and cell locking. When a worksheet is protected, all cells that are locked become uneditable and those cells that are not locked remain editable. To lock or unlock a cell, select the cell or cells and choose the menu command Format->Cells (in Excel 2007 press ALT, O, E), navigate to the Protection tab, and check the Locked check box on to lock the cell or cells or off to unlock. Note that on all new worksheets all cells default to being locked.

To prevent worksheets from being added and deleted the workbook structure needs to be protected. Choose the menu command Tools->Protection->Protect Workbook and click OK.

Kevin
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:teylyn
ID: 34959654
Seamus,

what are you trying to achieve? In your initial question you say you want to "disable read only, ... So multiple users can be in the spreadsheet at the same time without having to be in as read only"

Now you say you want to "be able capture the usernames and time in the spreadsheet of different users"


These are quite different things.


Can you please try to lay out the full requirements without sending us off on a wild goose chase on a  off topic?

cheers,

0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 34959678
You say you are trying to capture who is looking at the workbook. Where is this information stored? How will you save the workbook?

Kevin
0
 
LVL 50

Expert Comment

by:teylyn
ID: 34959717
Thanks, but I'm not quite sure I deserve points for asking for clarification.

You may want to click Request for Attention and have the mods re-open the question, so you can assign points to the real solution.

cheers, teylyn
0
 

Author Comment

by:Seamus2626
ID: 34959767
Have done, thanks Teylyn

Thanks Kevin

Seamus
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34959854
Here are my notes on shared Excel file:

Shared Excel files:

Excel does not handle 'Shared' workbooks reliably. They are often corrupted at random and apparently without cause. Clearly there is a cause but it has not yet been isolated and fixed by MS. There is nothing you can do to avoid shared workbooks corrupting. The ONLY thing that can be done is to make them single user.

To help you with the single user file system try adopting this approach instead. It does work rather well so it's worth doing. First make the file(s) single user. Second edit everyone's user profile in Excel - Tools/Options/General/UserName and in there put the person's name and telephone extension number. Then whenever you attempt to open a file that someone else is using, a dialogue box will pop-up telling you who is the current user - and it tells you their telephone extension number! The new user can opt to either call the current user or open the file as read-only.

I realise it seems odd that MS would have an unreliable feature in Excel but I'm afraid that's the way it is. If you must have simultaneous access then it would be wise to use a proper database.

For Office 2007:  Office Button > Excel Options >Popular tab >User name
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959870
Patrick,

Sorry bud, but you completely missed the point of the question.

The asker is not interested in notifying the users as to which user is using the workbook, rather he wants to log in the workbook who has/is looking at the report while allowing all users to view the report at the same time.

Kevin
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34959889
Kevin,

That's OK fellar.

I didn't miss point of the question. He wrote "So multiple users can be in the spreadsheet at the same time without having to be in as read only" - and that must mean 'sharing' Excel files - which in my opinion is not a good idea.

Patrick

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34959906
Patrick,

Correct, but in this case sharing is OK because he is not allowing the users to update the workbook nor does he care about notifying any particular user which users are already using it. He is trying to log in the workbook who is/has viewed the workbook. You can't do this with your solution hence you are misleading the asker.

Kevin
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now