Protect sheets

[Guest]

Greetings and TIA for your time
I am using the code below to protect selected worksheets so that the user
can only select unlocked cells and can enter comments (in unlocked cells)

Running the code below has the desired effect. However, when I save the
file, close and re-open, the protection is still in place but the user can
now also select locked cells. How do I prevent the user selecting locked
cells after save, close and re-open?

Sub ProtectSelectedSheets()
Dim wks As Worksheet
Dim myarray()
ReDim myarray(1 To ActiveWindow.SelectedSheets.Count)
counter = 0
For Each wks In ActiveWindow.SelectedSheets
counter = counter + 1
myarray(counter) = wks.Name
Next
For i = 1 To counter
With Worksheets(myarray(i))
.EnableSelection = xlUnlockedCells
.Protect Password:="pwd", DrawingObjects:=False, _
Contents:=True, Scenarios:=True, userinterfaceonly:=True
End With
Next
End Sub

 

[cscorp]

Dear Sir

Try This:

Invert the order of the following two statements:

.EnableSelection = xlUnlockedCells
.Protect Password:="pwd", DrawingObjects:=False, _
Contents:=True, Scenarios:=True, userinterfaceonly:=True

I guess ".enableproctection =xlunlockedCells" should go last... Afte
the protect statement.

Hope this helps.

Regards

Juan Carlo

 

[countryfan_nt]

I have a code that fills out many cells in the rows. and I have a
picture named: *PIC1* placed in sheet 2. I want the picture to popup
once I run the macro. But I also want the picture to be scrolled down
in the middle as the rows are filled (i.e as the macro is running).

What is the code that I shiuld use?
Thank you so much.

All the best,
Nawaf
@ 7572

 

[Tom Ogilvy]

Unfortunately, changing the order won't help in this situation. The
enableSelection property is non-persistent and must be set each time the
workbook is opened. You can do this in the workbook_open event

See Chip Pearson's page on events if you are not familiar with them:

http://www.cpearson.com/excel/events.htm

 

[Guest]

Thanks Tom,
.... so. if i understand it right, when setting with code, enableSelection
needs to be reset every time the workbook is opened. Unlike when setting
'manually' on the sheet when it is set 'forever'? ie: code cannot directly
mimic the manual setting?
TIA
--
David

Unfortunately, changing the order won't help in this situation. The
enableSelection property is non-persistent and must be set each time the
workbook is opened. You can do this in the workbook_open event

See Chip Pearson's page on events if you are not familiar with them:

http://www.cpearson.com/excel/events.htm

 

[Tom Ogilvy]

In xl2000 and earlier, it is non-persistent for both manual or code
settings. In xl2002, they changed the dialog for setting protection - but
as I recall, this is not supported programmatically and the old property is
used programmatically. So I believe this is the case - that is is not
persistent programmatically, but in xl2002 and later, is persistent if set
manually.

You note that in the revised protect method for xl2002/3

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
AllowSorting, AllowFiltering, AllowUsingPivotTables)

there is no provision for setting enableselection. An apparent
oversight/bug.

 

[Guest]

Thanks again Tom,
It's a privilege to communicate with you.