modify without unprotecting the sheet, Unsolved question

[Guest]

yesterday in this forum:
1.Hi, Macro1 unprotect sheet1 modifies a lot of the sheet values (A lot of
ranges) and then protect the sheet again, my problem is when the modification
action takes place, because takes aprx 1 to 2 seconds, so if the user want to
pres cntrl+pause he could do that ( And I don’t want the user breaks my
program), there is somehow tell VB that although the sheet is protected, VB
can write everything he wants with out unprotecting the sheet??????
Also
2. I’m using an array and to call it I need to use the following format
ar(x,y)(z) I know that the “z†have something to do with the option base 1
(because when I put option base 1 I need to write my array as ar(x,y)(1) and
with option base 0 I need to write ar(x,y)(0))
How I can use my regular format of the arrays??? ( ar(x,y) )
TIA

Dave Peterson answered:
Take a look at Application.EnableCancelKey in VBA's help.
Read the warning closely.
(THANKS FOR THAT JAMES, IT SOLVES POINT NUMBER ONE)

Now, just for curiosity, there is a way for VB to write in a protected sheet
(and in the cells loked) without unprotecting the sheet???

And the point 2. still unsolved
Thanks

 

[Norman Jones]

Hi Filo666,

Now, just for curiosity, there is a way for VB to write in a protected
sheet (and in the cells loked) without unprotecting the sheet???

Setting the Protect method's UserInterfaceOnly parameter to true enables vba
manipulation of the protected sheet.

However, this setting is not persistent and needs to be reset each time the
workbook is opened.

Perhaps, therefore, you could set protection in the Workbook_Open or
Auto_Open procedures, e.g.:

Sub Auto_Open()
With Worksheets("sheet1")
.Protect Password:="drowssap", UserInterfaceOnly:=True
End With
End Sub