Excel lock sheet

[Per9922]

Hello, I have some problem with the lock sheet function! if I tick the first three when I lock the sheet:

Mark lock cells
Mark none locked cells
Format cells

I then have two buttons on my sheet, one unlock the sheet and the other lock the sheet, when I lock the sheet, the format cells box is unticked ?

ThisWorkbook.ActiveSheet.Protect <- This will untick the format cells box, why ?

ThisWorkbook.ActiveSheet.Unprotect

Plese help

 

[SkipVought]

hi,

There is no "lock sheet function".

You can LOCK cells and then PROTECT the sheet: Select Locked Cells, Select Unlocked Cells, Format Cells.

Please post the code that you run to Protect and Unprotect your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

This is the code:

Private Sub CommandButton1_Click()
ThisWorkbook.ActiveSheet.Protect
End Sub

Private Sub CommandButton2_Click()
ThisWorkbook.ActiveSheet.Unprotect
End Sub

Is it possible to prevent the user to add new rows from say row 10 to row 20 ?

I have Template there I would like the user to add rows but not everywhere ...

 

[SkipVought]

There are PARAMETERS for the Protect method that you have not assigned. What do you expect?

Turn on your macro recorder, if necessary, and record assigning (checking the boxes) the required parameters. Then you will have the required parameters assigned.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

sorry ... I will do that!

Is it possible to have an area that is unprotected in a sheet that is protected ?

 

[SkipVought]

Objects on the sheet that are UNLOCKED, can be manipulated in certain ways, when the sheet is PROTECTED.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

Sorry I explain so bad!

there are there different areas I would like to have ...

1) One area there the cells are locked and the user can not access (I lock the cells)

2) One area there the user can only input text/number etc, but not input rows/format text

3) One area there the user can add rows/format text/ etc

is it possible ?

 

[SkipVought]

What version Excel?

Have you looked at Excel HELP on Protect?

What have you tried that did not work?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

I have version 2007

1) Is okay
2) Is okay
3) Problem with this, I though I could use "Allow users to edit the field" to allow the user to format/add rows to this area, but is it possible ? When the sheet is protected ?

 

[SkipVought]

Where did you see the protection control to "Allow users to edit the field"?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

Rewiew->Allow Users To Edit Range

 

[SkipVought]

Of course this refers to those specific range that you have defined and for those specific users you have defined in the window.

This has nothing to do with inserting rows. That property is assigned in the Review > Protect Sheet window.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

Okay, so you cant't have a sheet that is protected there you can only insert rows only i.e at rows 10-20 ?

 

[SkipVought]

There is no way to say "only insert rows 10-20" is there?

Why do you need to insert rows? Can you enter data below your table and SORT the data into a desired order?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

I have a template there some areas are locked, there som areas I only want the user to de able to write text (no format etc), and I would like to have one area there the user can input a picture, format text etc

 

[SkipVought]

So why are you setting the protection via VBA code?

Do you have other code as well?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

I have button code that add/removes lines in tables etc

 

[SkipVought]

You may need a button to remove protection, add rows and add protection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Per9922]

Yes, done that, that is not the problem. The problem is this:

The sheet is protected: In some areas I would like the user only be able to input text, not format text, add rows etc, the cells are not locked. In one area I would like the user to be able to format text, add pictures, add rows etc, the cells are not locked. How to be able to to the last thing ....

 

[SkipVought]

Well it does not appear that the sheet protection feature is that granular.

You may have to do this via VBA, using the Worksheet_SelectionChange event.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}