run macros on a protected sheet?
 

Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

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


"Joe Z" wrote in message
...
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

Chip -- That's what I suspected. Thanks very much for
confirming. Joe

-----Original Message-----
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


"Joe Z" wrote in
message
...
Is it possible to run a macro on a protected sheet?
I've
tried and tried but I can't figure it out. Thanks.


.

run macros on a protected sheet?
 

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.

"Chip Pearson" wrote in message
...
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


"Joe Z" wrote in message
...
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

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).

--
Regards,
Tom Ogilvy

"Joe Z" wrote in message
...
Chip -- That's what I suspected. Thanks very much for
confirming. Joe

-----Original Message-----
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


"Joe Z" wrote in
message
...
Is it possible to run a macro on a protected sheet?
I've
tried and tried but I can't figure it out. Thanks.


.

run macros on a protected sheet?
 

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.

--
Regards,
Tom Ogilvy

"Martyn" wrote in message
...
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.

"Chip Pearson" wrote in message
...
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


"Joe Z" wrote in message
...
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

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.

--
Regards,
Tom Ogilvy

"Martyn" wrote in message
...
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.

"Chip Pearson" wrote in message
...
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


"Joe Z" wrote in message
...
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

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


"Martyn" wrote in message
...
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.

"Chip Pearson" wrote in message
...
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


"Joe Z" wrote in
message
...
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

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.


"Chip Pearson" wrote in message
...
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


"Martyn" wrote in message
...
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.

"Chip Pearson" wrote in message
...
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


"Joe Z" wrote in
message
...
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

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é.


"Chip Pearson" a écrit dans le message de
...
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


"Martyn" wrote in message
...
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.

"Chip Pearson" wrote in message
...
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


"Joe Z" wrote in
message
...
Is it possible to run a macro on a protected sheet? I've
tried and tried but I can't figure it out. Thanks.

run macros on a protected sheet?
 

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


"rene" wrote in message
...
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é.


"Chip Pearson" a écrit dans le message de
...
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


"Martyn" wrote in message
...
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.

"Chip Pearson" wrote in message
...
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


"Joe Z" wrote in
message
...
Is it possible to run a macro on a protected sheet?
I've
tried and tried but I can't figure it out. Thanks.