J
Joe Z
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.
tried and tried but I can't figure it out. Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
Chip Pearson
Joe,
If the procedure changes cells, you must unprotect the sheet
prior to executing the macro.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
If the procedure changes cells, you must unprotect the sheet
prior to executing the macro.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
J
Joe Z
Chip -- That's what I suspected. Thanks very much for
confirming. Joe
confirming. Joe
M
Martyn
Chip,
My additional question is: Are you sure that one cannot unprotect sheet
whitin a macro, make changes to cells, protect the sheet again and exit?
I'll be much obliged if you or anyone else could answer.
My additional question is: Are you sure that one cannot unprotect sheet
whitin a macro, make changes to cells, protect the sheet again and exit?
I'll be much obliged if you or anyone else could answer.
T
Tom Ogilvy
Unprotecting is certainly the most robust approach, but you can do most
things with the sheet still protected using the
UserInterfaceOnly
only property of the the Protect method which must be set using VBA. In
xl2000 and xl97, you can set this without providing the password. In later
versions, I believe you have to provide the password.
Activesheet.Protect UserInterfaceOnly:=True
Must be done each time the workbook is opened as the setting is not retained
across the closing and opening of the workbook (so use the Workbook_Open
event).
things with the sheet still protected using the
UserInterfaceOnly
only property of the the Protect method which must be set using VBA. In
xl2000 and xl97, you can set this without providing the password. In later
versions, I believe you have to provide the password.
Activesheet.Protect UserInterfaceOnly:=True
Must be done each time the workbook is opened as the setting is not retained
across the closing and opening of the workbook (so use the Workbook_Open
event).
T
Tom Ogilvy
Chip didn't say you couldn't - he said that would be the way to do it.
Although I have suggested an additional method as well.
Although I have suggested an additional method as well.
T
Tom Ogilvy
I reread his response and see the confusion - he meant that the sheet must
be unprotected before changes to the cell were made - his answer could be
misconstrued to seem he said it had to be done in a separate macro - but
that is not the case.
be unprotected before changes to the cell were made - his answer could be
misconstrued to seem he said it had to be done in a separate macro - but
that is not the case.
C
Chip Pearson
Martyn,
You can certainly unprotect the sheet within the procedure, and
then protect it at the end. E.g.,
Worksheets("Sheet1").Unprotect 'password:="whatever"
' your code here
Worksheets("Sheet1").Protect 'password:="whatever"
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
You can certainly unprotect the sheet within the procedure, and
then protect it at the end. E.g.,
Worksheets("Sheet1").Unprotect 'password:="whatever"
' your code here
Worksheets("Sheet1").Protect 'password:="whatever"
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
M
Martyn
I' am glad that I underestood the same...
Thanks to Tom as well. AFAIK he is among the best around here...and I love
keep following his answers.
Thanks to Tom as well. AFAIK he is among the best around here...and I love
keep following his answers.
R
rene
Chip,
Excel documentation speaks of
Worksheets("Sheet1").Protect 'password:="whatever" UserInterfaceOnly:= true
which should protect the sheet but allow the running of macros.
But it does not seem to work well with excel 2000 ; several actions by
macros seems to be still forbidden with this "userinterfaceonly" protection.
Truly yours,
René.
Excel documentation speaks of
Worksheets("Sheet1").Protect 'password:="whatever" UserInterfaceOnly:= true
which should protect the sheet but allow the running of macros.
But it does not seem to work well with excel 2000 ; several actions by
macros seems to be still forbidden with this "userinterfaceonly" protection.
Truly yours,
René.
C
Chip Pearson
Rene,
Using the UserInterfaceOnly flag should permit any VBA code to
modify the worksheet. Can you provide a specific example is this
not being the case? I've never run across a situation in which it
doesn't work as described.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Using the UserInterfaceOnly flag should permit any VBA code to
modify the worksheet. Can you provide a specific example is this
not being the case? I've never run across a situation in which it
doesn't work as described.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"userinterfaceonly" protection.rene said:Chip,
Excel documentation speaks of
Worksheets("Sheet1").Protect 'password:="whatever" UserInterfaceOnly:= true
which should protect the sheet but allow the running of macros.
But it does not seem to work well with excel 2000 ; several actions by
macros seems to be still forbidden with this