Macro - settings to protect sheets

[Philipp Eckardt]

Hi all,

I am currently looking for a way to define restrictions/settings when
applying an ActiveSheet.Protect macro. The idea is that users have to be able
to fill in more data as defined in the standard sheet protection setting
(select locked cells/select unlocked cells)... in my case:

Select locked cells = Yes
Select unlocked cells = No
Format cells = Yes
Format columns = No
Format rows = N0
Insert columns = No
Inser rows = Yes
Insert Hyperlinks = No
Delete columns = No
Delete rows = Yes
Rest = no

The whole thing is used to do a spellcheck on protected sheets (so the makro
unprotects the sheet, runs the spellcheck and protects the sheet again)...
and any help would be much appreciated .

Cheers,
Philipp

 

[Philipp Eckardt]

Great... once I had posted the question, I found the answer:

from: http://www.excelforum.com/showthread.php?threadid=382012

According to VBA, the choices for ActiveSheet.PROTECT are:

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

To prevent users from selecting protected cells,
"ActiveSheet.EnableSelection = xlUnlockedCells" has to be set additionally...
(however, some more problems occur here as this function is - unlike the
manual setting - not persistent when performed via code.

So, sorry for bothering the community ;-).
Cheers,
Philipp