Locking all cells in a workbook

I have been having problems protecting my worksheets, using the normal manner, mainly it does not seem to work, So i came up with another idea, but cant seem to work out how to do it.

I have a workbook with about 7 worksheets, this needs to be sent via email to our customers, and on two of the worksheets the customer can enter there comments and then email back the workbook, the problem is that i cant protect the data.

So i was thinking is it possible to use VBA to program a function i can enter in to the toolbar that the user can lock all the cells in the whole workbox except for two columns when they finish entering in data, maybe with a password for locking it and unlocking for the original user so that they can unlock the cells for editing.

G

Sorry didnt explain it to well, that is what im trying to do lock the cells that are previous unlocked, excpet for two columns.

I'm not sure if this is what you did using the 'normal' way but you should be able to just:

tools > protection > allow user to edit ranges
new > 'select the two columns the user can edit'
ok

Then, when you protect the sheet the only bit which can be edited will be the area you defined. If you want, you can password protect that area:

tools > protection > allow user to edit ranges
'select which range' > modify > password

Then protect the sheet.

Tried all those, the problem is that the sheets within the workbook have locked areas as these contain code and or formula.

The sheets contain formula that are locked out so that the user does not delete the information contained, So the user would enter int he required data in the given cells, i.e. the cells that are not locked, the sheets are protected at this point.

These sheets then need to be sent to our customers for comments, but the cells where the user has entered data need to be locked so that the customer cannot change any of the details. But they have a column that they can add the comments about the presented data.

This function needs to be controlled by the original user.

Does this explain, I dont think a exmaple work sheet would be helpfull...

G

Thank you for your responce, That may sound the easiest way, but the worksheet is protected, cos before users had a bad habit of deleting formula and also doing stuff that confused the calculations. So for protecting the data and calculations the sheet is protected, so the user can only enter certain data and also perform certain functions.

So i need to add to these functions a way of locking all the cells once they are ready to send it to a customer, but be able to unlock cells for alterations.

G

The problem here i think is that I may not be explaining it to well, I understand every thing you have explained and have used the functions.

I have thought about this and will try and explain it in a differnt way.

I designed a spreadsheet for my team of monitors to use, the sheet contains formule and other calculations and settings. The cell where the monitors have to enter data are unlocked, the cells that contain the formule are locked. The whole sheet is then protected so the monitor cannot delete stuff they shouldn't do.

Once the monitor has entered there data, the sheet is normal printed off and sent to the customer, Our boses want us to change so that we are able to send the spreadsheet via email. The only problem we have is that the customer could change the data that we eneterd.

So the monitor needs to be able to lock all the unlocked cells before sending it to the customer, well lock all unlocked cells except for two columns on different worksheets within the workbook, as these need to be availble for the customer to enter ther comments. The monitor then must be able to uinlock the workbook when it is sent back.

I hope that explains it better...

Thanks for all your help, but i dont think any of these will help. I have figure out in that what im trying todo is protect something that is already protected.

The only way i can think of doing, which is not reversible, is to us VBA code to unprotect the sheet, lock all the cells excpet for one column and the protect the sheet again, Again the only problem is that the monitor of that sheet would not be able to edit it again

So how would i go about doing that, i can work out how to put a menu option in to unprotect the sheet and reprotect it, its just locking the cells and leaving a column unlocked..

Oh thanks for the help so far, all answers ahve be saved and noted for futurte use

G

I give it a try and see what happens. Could i just set the range like from a1:h2000, would that work ???

Ok, that seems to work, Had to put in a warning message. Ok i have that working on one sheet, how do i do a global cell lock on all sheets in the workbook except for for two columns which are on seperate work books.. ?

Code im using already

Please Login or Register  to view this content.

And is there a easy way of undoing this .... ??

G