Inserting Rows + ScrollArea + Protection

[tools]

Hi all,

After protecting my sheet I am not able to insert or delete any rows.
The problem is with the ScrollArea property that I am using in my code.

If I remove the ScrollArea property and protect my sheet I am able to insert or delete rows.

Is there any solution to this ?



Sub Protectsheet()


Worksheets(1).ScrollArea = "A:Z"
Worksheets(1).Unprotect
Worksheets(1).range("A1:B15").Locked = True
Worksheets(1).Protect DrawingObjects:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFiltering:=True

End Sub



Thanks & Regards

[Philcjr]

Tools,

I have been playing with this and have found no solution... that does not mean that there is no solution, but I can't find one... there are MANY more knowledgable people on here than me.

Also, why oh why would you allow a user the option of "deleting" anything??

If I protect a worksheet, it is to protect what is on the sheet.

Maybe, if you could tell us what your desired end-result would be? and why? maybe there is another approach.

Also, you should look into With...End With statements to clean-up your code.

[tools]

Hi all,

Thanks for your reply philcjr.


I have an excel sheet which has few header columns . The user should not be able to delete the header columns which is fine .

Now the excel calls an external system which responds back with some messages.

There are two columns in my sheet which displays the response messages . I dont want the user to change the value in these two columns whereas he can change it the other columns.

I am limiting the scrollarea since I am using that area for data manipulation.

As a work around I am hiding the columns and protecting.
Is there a better solution to this?

I have attached an excel sheet for reference.

Thanks and Regards

[lucas]

First of all, you are just locking cells that are already locked.

You need to select all columns you might be using to the right of the two you want locked and right click on them and select properties. On the protection tab you need to uncheck the box that says locked.........

the way you are doing it you can't use any of the cells on the sheet.

[tools]

Thnx Lucas I did do that .

But my problem still persists. Even I couldnt find a solution to this issue.