Disable read only

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
Seamus2626Asked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Seamus2626Author Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Do not use workbook sharing. You don't need this feature.

Kevin
0
 
Seamus2626Author Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
>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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Seamus2626Author Commented:
Have done, thanks Teylyn

Thanks Kevin

Seamus
0
 
patrickabCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
patrickabCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.