| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting Multiple Worksheets
Hi
I need to protect multiple worksheets to lock cells. Only way I can see to do this is to go into individual sheets and do this. The 'protect sheet' option is greyed out when multiple sheets are open. Is it therefore possible to run a macro to protect / unprotect worksheets with a password? Thanking you in advance. Sam |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protecting Multiple Worksheets
Try this Sam
Sub prot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ron" Next sh Application.ScreenUpdating = True End Sub Sub unprot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Unprotect "ron" Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Hi I need to protect multiple worksheets to lock cells. Only way I can see to do this is to go into individual sheets and do this. The 'protect sheet' option is greyed out when multiple sheets are open. Is it therefore possible to run a macro to protect / unprotect worksheets with a password? Thanking you in advance. Sam |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protecting Multiple Worksheets
Ron!
Thank you - this worked perfectly - you little star! Quick question - does sh.protect "ron" set the password? Thanks Sam -----Original Message----- Try this Sam Sub prot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ron" Next sh Application.ScreenUpdating = True End Sub Sub unprot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Unprotect "ron" Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Hi I need to protect multiple worksheets to lock cells. Only way I can see to do this is to go into individual sheets and do this. The 'protect sheet' option is greyed out when multiple sheets are open. Is it therefore possible to run a macro to protect / unprotect worksheets with a password? Thanking you in advance. Sam . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protecting Multiple Worksheets
Ron
Me again. If the sh.protect "ron" is the password - is it possible to make it look at a cell in the spreadsheet - otherwise the password just runs and doesn't need to be entered. It would be good to have the person enter the password (once) manually to make it more protected. something like - sh.Unprotect = sh.name cell ## Does that make sense? Thanks again - Sam -----Original Message----- Try this Sam Sub prot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ron" Next sh Application.ScreenUpdating = True End Sub Sub unprot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Unprotect "ron" Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Hi I need to protect multiple worksheets to lock cells. Only way I can see to do this is to go into individual sheets and do this. The 'protect sheet' option is greyed out when multiple sheets are open. Is it therefore possible to run a macro to protect / unprotect worksheets with a password? Thanking you in advance. Sam . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protecting Multiple Worksheets
You can use this
sh.Protect ThisWorkbook.Sheets("Sheet1").Range("A1").Value -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Ron Me again. If the sh.protect "ron" is the password - is it possible to make it look at a cell in the spreadsheet - otherwise the password just runs and doesn't need to be entered. It would be good to have the person enter the password (once) manually to make it more protected. something like - sh.Unprotect = sh.name cell ## Does that make sense? Thanks again - Sam -----Original Message----- Try this Sam Sub prot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ron" Next sh Application.ScreenUpdating = True End Sub Sub unprot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Unprotect "ron" Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Hi I need to protect multiple worksheets to lock cells. Only way I can see to do this is to go into individual sheets and do this. The 'protect sheet' option is greyed out when multiple sheets are open. Is it therefore possible to run a macro to protect / unprotect worksheets with a password? Thanking you in advance. Sam . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protecting Multiple Worksheets
does sh.protect "ron" set the password
Yes -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl wrote in message ... Ron! Thank you - this worked perfectly - you little star! Quick question - does sh.protect "ron" set the password? Thanks Sam -----Original Message----- Try this Sam Sub prot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ron" Next sh Application.ScreenUpdating = True End Sub Sub unprot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Unprotect "ron" Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Hi I need to protect multiple worksheets to lock cells. Only way I can see to do this is to go into individual sheets and do this. The 'protect sheet' option is greyed out when multiple sheets are open. Is it therefore possible to run a macro to protect / unprotect worksheets with a password? Thanking you in advance. Sam . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Protecting Multiple Worksheets
Ron - this is perfect! Thank you so much - you are an
absolute star. I appreciate your help immensly. Sam -----Original Message----- You can use this sh.Protect ThisWorkbook.Sheets("Sheet1").Range("A1").Value -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Ron Me again. If the sh.protect "ron" is the password - is it possible to make it look at a cell in the spreadsheet - otherwise the password just runs and doesn't need to be entered. It would be good to have the person enter the password (once) manually to make it more protected. something like - sh.Unprotect = sh.name cell ## Does that make sense? Thanks again - Sam -----Original Message----- Try this Sam Sub prot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Protect "ron" Next sh Application.ScreenUpdating = True End Sub Sub unprot() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Unprotect "ron" Next sh Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Sam A" wrote in message ... Hi I need to protect multiple worksheets to lock cells. Only way I can see to do this is to go into individual sheets and do this. The 'protect sheet' option is greyed out when multiple sheets are open. Is it therefore possible to run a macro to protect / unprotect worksheets with a password? Thanking you in advance. Sam . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for protecting and unprotecting multiple worksheets | Excel Discussion (Misc queries) | |||
| Protecting Multiple Worksheets at the same time | Excel Worksheet Functions | |||
| Protecting multiple worksheets. | Excel Discussion (Misc queries) | |||
| Protecting Worksheets | Excel Worksheet Functions | |||
| protecting multiple worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
