Grant access to selective columns on shared worksheet (excel 2003)

**Mr. DJ** · 11-15-2012, 05:57 AM

Hi there everyone,

I'm new & this is my first post so please bear with me. As per the title, I'd like to grant access to selective columns on a shared worksheet. For example:
Columns A to D should be edited by only me
Columns E to G can be edited by anyone
Column H should only be edited by someone else

The worksheet has to be shared and has been shared by me. I know how to protect & hide locked cells etc but the problem I'm running into is this...
After locking & password protecting say columns A to D and then sharing the worksheet, I am unable to edit my own columns (columns A to D) without first entering the password.
However in order to enter the password I need to unprotect the sheet.
And in order to unprotect the sheet, I need to unshare it! Talk about frustrating!

Is there any way around this or perhaps a completely different way of approaching it?

Thanks in advance for your time.

DJ

**Marco-Kun** · 11-15-2012, 08:26 AM

Hello Mr. DJ, welcome to the forum.

That sounds frustrating indeed. This kind of thing is new for me too, so I'm not sure if this is the solution you want.

I added an example with a protected sheet. All cells in this sheet are locked. The password for this sheet is "password".

When the workbook opens it will check for a username.
First it will check for your username, which you will have to add to the code. I added notes to the codes which hopefully make it easier to understand.

Please Login or Register  to view this content.

The code will unlock the sheet. The password "password" is stated in the VBA code to unlock it. It will unlock the columns you provided and afterwards it will lock it again with this password. My suggestion is to put a password on the VBA project as well to avoid people seeing the sheet password in the code.

You have to edit these codes for unlocking and locking the sheet with your password

Please Login or Register  to view this content.

Please Login or Register  to view this content.

When it's not you who is opening the file, it will check for the username which can edit column H. If they match, it will unlock column E to H, since column E to G can be edited by anyone.

You have to edit that here:

Please Login or Register  to view this content.

When it's not you, nor the person who can edit column H, it will only unlock column E to G.

When you download it now, it could give you an error because of these parts:

Please Login or Register  to view this content.

You have to edit "Sheet1" to match with your sheet name.

I hope this helps.

Kind regards,

Marco

**Mr. DJ** · 12-05-2012, 09:34 AM

Seems I forgot my manners Marco-Kun... a million thanks for your help on this. Not too verse in VBA but your instructions were clear and concise! Thanks again for your help!