Locking Columns

[maccten2000]

Dear All,

I have a spreadsheet where i need to set that the user is unable to insert columns in one part of the spreadsheet but is able to insert columns in another part of the spreadsheet.

For example Columns A you are not allowed to insert the columns and anything after that you are

Does anyone know if the microsoft protection allows this?

I am using excel 2003

Cheers

 

[kjv1611]

Not sure on inserting columns, but you could try this:
[OL][LI]Select ALL cells in the sheet.[/LI]
[LI]Right-click, select Format Cells[/LI]
[LI]Go to the Protection Tab[/LI]
[LI]UNcheck LOCKED[/LI]
[LI]Press OK[/LI]
[LI]Now select the columns you don't want changed[/LI]
[LI]Right click, select Format Cells[/LI]
[LI]Go to the Protection Tab[/LI]
[LI]CHECK LOCKED[/LI]
[LI]Press OK[/LI]
[LI]Go to Format - Sheet - Protection[/LI]
[LI]There's a checkbox or two there, select Protect Worksheet I believe[/LI]
[LI]There are options after that, as to what to protect - there is one dealing with the structure of sheets (or maybe it's the whole workbook with this part, I forget..[/LI]
[LI]Try changing various settings if the first one doesn't work. And if you give it a password, be sure you remember it. ;0)[/LI]
[/OL]

At least that's the only way I can imagine that will work. The only other way would be with custom VBA code, maybe in the Sheet_OnUpdate event, or whatever the correct wording is.

--

"If to err is human, then I must be some kind of human!" -Me

 

[maccten2000]

Hi kjv1611

I have tried this. Unfortunatly once you select that the user cam insert columns, they are able to insert columns anywhere on the sheet not just in the unlocked cells. Once the Column has been inserted into the restricted section you are not allowed to edit it but i would prefer if they were not allowed insert it period as i use the sheet to be imported back into excel.

I was just wondering if there was a way lock them out of inserting columns in one section of the sheet but allowing others

Thanks very much for your help

 

[kjv1611]

Well, I can think of a way to do it in VBA, I believe, but that'd be for another forum if you want to go that route:
forum707

I can give you a hint, basically: it's not a "clean" approach, but I believe it should work... of course, it will need some tweaking, but here's the idea:

1. When someone inserts a column, it's updating the worksheet, so you'd use an On Update or On Change event of the sheet...
2. Then you'd look to see if the data in the first 4 columns has changed. My guess would be that you'd want to nail down the values that will be in one row, say the title row, and make sure no changes there. If there were changes there, make sure it was a column insertion by a little more conditional statements, and then delete that column.
3. If the sheet is protected, of course, you'll have to unprotect it in code, then re-protect it when finished.

It'd be a fun project to sort out, I think, but you may not be wanting to go that route. It's totally up to you.

--

"If to err is human, then I must be some kind of human!" -Me