| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run macros on protected sheet | Excel Discussion (Misc queries) | |||
| protecting macros for a protected sheet | Excel Discussion (Misc queries) | |||
| Run macros on protected worksheet | Excel Worksheet Functions | |||
| Enabling macros while the sheet being protected ! | Excel Discussion (Misc queries) | |||
| is it possible to use macros when the sheet is protected | Excel Discussion (Misc queries) | |||
![Joe Z[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
