Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Passowrd Protecting
I currently have 8 sheets within a workbook which are protected, on each
sheet I have some locked and unlocked cells. Each worksheet is for each day of the week plus a totals sheet, therefore the layout for each sheet is identical. At present If I need to make an amendment to any of the locked cells I have to unprotect every sheet make the change and then proect every sheet. Is there anyway of proecting ALL the sheets at once and unprotecting them all at once as this would reduce the time it takes. Regards The Rook |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Passowrd Protecting
try a loop something like
for each ws in worksheets If ws.Name < "Sheet1" Then do your thing Next ws -- Don Guillett SalesAid Software "TheRook" wrote in message ... I currently have 8 sheets within a workbook which are protected, on each sheet I have some locked and unlocked cells. Each worksheet is for each day of the week plus a totals sheet, therefore the layout for each sheet is identical. At present If I need to make an amendment to any of the locked cells I have to unprotect every sheet make the change and then proect every sheet. Is there anyway of proecting ALL the sheets at once and unprotecting them all at once as this would reduce the time it takes. Regards The Rook |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Passowrd Protecting
You would need to use a VBA procedu
Sub ProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Protect Next SH End Sub Sub UnProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Unprotect Next SH End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "TheRook" wrote in message ... I currently have 8 sheets within a workbook which are protected, on each sheet I have some locked and unlocked cells. Each worksheet is for each day of the week plus a totals sheet, therefore the layout for each sheet is identical. At present If I need to make an amendment to any of the locked cells I have to unprotect every sheet make the change and then proect every sheet. Is there anyway of proecting ALL the sheets at once and unprotecting them all at once as this would reduce the time it takes. Regards The Rook |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Passowrd Protecting
Chip,
That worked a treat, but I forgot ti include that I want to use a password. Can this be done? "Chip Pearson" wrote: You would need to use a VBA procedu Sub ProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Protect Next SH End Sub Sub UnProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Unprotect Next SH End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "TheRook" wrote in message ... I currently have 8 sheets within a workbook which are protected, on each sheet I have some locked and unlocked cells. Each worksheet is for each day of the week plus a totals sheet, therefore the layout for each sheet is identical. At present If I need to make an amendment to any of the locked cells I have to unprotect every sheet make the change and then proect every sheet. Is there anyway of proecting ALL the sheets at once and unprotecting them all at once as this would reduce the time it takes. Regards The Rook |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Passowrd Protecting
Use code like
Sub ProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Protect Password:="the_password" Next SH End Sub Sub UnProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Unprotect Password:="the_password" Next SH End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "TheRook" wrote in message ... Chip, That worked a treat, but I forgot ti include that I want to use a password. Can this be done? "Chip Pearson" wrote: You would need to use a VBA procedu Sub ProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Protect Next SH End Sub Sub UnProtectAll() Dim SH As Object For Each SH In ThisWorkbook.Sheets SH.Unprotect Next SH End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "TheRook" wrote in message ... I currently have 8 sheets within a workbook which are protected, on each sheet I have some locked and unlocked cells. Each worksheet is for each day of the week plus a totals sheet, therefore the layout for each sheet is identical. At present If I need to make an amendment to any of the locked cells I have to unprotect every sheet make the change and then proect every sheet. Is there anyway of proecting ALL the sheets at once and unprotecting them all at once as this would reduce the time it takes. Regards The Rook |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting more than one worksheet in one workbook | Excel Discussion (Misc queries) | |||
Locking Cells ... Without Protecting Sheet | Excel Discussion (Misc queries) | |||
Locking Cells ... Without Protecting Sheet | Excel Discussion (Misc queries) | |||
Protecting Sheets | Excel Discussion (Misc queries) | |||
Leave list box unprotected while protecting worksheet | Excel Discussion (Misc queries) |