| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Protecting Worksheets
Win XP HE, SP1
Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S |
|
#2
|
|||
|
|||
|
You will need a for/each looping macro
-- Don Guillett SalesAid Software "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S |
|
#3
|
|||
|
|||
|
Don Guillett wrote:
You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S |
|
#4
|
|||
|
|||
|
S
Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sat, 22 Jan 2005 14:50:47 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Don Guillett wrote: You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S |
|
#5
|
|||
|
|||
|
Gord,
Great, Tx a lot for the macro. How do I make the adjustment in it to uncheck "Allow all users of the worksheet to: Select Locked Cells" (In other words, do NOT let users select locked cells.) Tx a million. S Gord Dibben wrote: S Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sat, 22 Jan 2005 14:50:47 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Don Guillett wrote: You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S |
|
#6
|
|||
|
|||
|
Sub ProtectAllSheets()
Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="justme" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub Gord On Sat, 22 Jan 2005 15:50:50 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Gord, Great, Tx a lot for the macro. How do I make the adjustment in it to uncheck "Allow all users of the worksheet to: Select Locked Cells" (In other words, do NOT let users select locked cells.) Tx a million. S Gord Dibben wrote: S Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sat, 22 Jan 2005 14:50:47 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Don Guillett wrote: You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S |
|
#7
|
|||
|
|||
|
Gord, That's great.
Thanks a lot, very much appreciate it. S Gord Dibben wrote: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="justme" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub Gord On Sat, 22 Jan 2005 15:50:50 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Gord, Great, Tx a lot for the macro. How do I make the adjustment in it to uncheck "Allow all users of the worksheet to: Select Locked Cells" (In other words, do NOT let users select locked cells.) Tx a million. S Gord Dibben wrote: S Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sat, 22 Jan 2005 14:50:47 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Don Guillett wrote: You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S |
|
#8
|
|||
|
|||
|
Any hints or suggestions as to a simple way to get started with VBA....?
S Gord Dibben wrote: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="justme" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub Gord On Sat, 22 Jan 2005 15:50:50 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Gord, Great, Tx a lot for the macro. How do I make the adjustment in it to uncheck "Allow all users of the worksheet to: Select Locked Cells" (In other words, do NOT let users select locked cells.) Tx a million. S Gord Dibben wrote: S Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sat, 22 Jan 2005 14:50:47 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Don Guillett wrote: You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S |
|
#9
|
|||
|
|||
|
David McRitchie has a "getting started" page at
http://www.mvps.org/dmcritchie/excel/getstarted.htm Note links to other sites at bottom of David's page. Note: Visual Basic for Applications 101 is an apparently defunct site. Also at Tushar Mehta's site is "beyond the macro recorder" http://www.tushar-mehta.com/excel/vb...rder/index.htm The recorder leaves a lot to be desired since it will generally create much superfluous code for simple operations, but it is a start. Spend some time lurking over at the microsoft.public.excel.programming newsgroup. Study the code provided by the regulars over there. Gord On Sun, 23 Jan 2005 12:11:15 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Any hints or suggestions as to a simple way to get started with VBA....? S Gord Dibben wrote: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="justme" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub Gord On Sat, 22 Jan 2005 15:50:50 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Gord, Great, Tx a lot for the macro. How do I make the adjustment in it to uncheck "Allow all users of the worksheet to: Select Locked Cells" (In other words, do NOT let users select locked cells.) Tx a million. S Gord Dibben wrote: S Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sat, 22 Jan 2005 14:50:47 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Don Guillett wrote: You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S |
|
#10
|
|||
|
|||
|
Gord Dibben wrote:
David McRitchie has a "getting started" page at http://www.mvps.org/dmcritchie/excel/getstarted.htm Note links to other sites at bottom of David's page. Note: Visual Basic for Applications 101 is an apparently defunct site. Also at Tushar Mehta's site is "beyond the macro recorder" http://www.tushar-mehta.com/excel/vb...rder/index.htm The recorder leaves a lot to be desired since it will generally create much superfluous code for simple operations, but it is a start. Spend some time lurking over at the microsoft.public.excel.programming newsgroup. Study the code provided by the regulars over there. Gord On Sun, 23 Jan 2005 12:11:15 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Any hints or suggestions as to a simple way to get started with VBA....? S Gord Dibben wrote: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="justme" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub Gord On Sat, 22 Jan 2005 15:50:50 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Gord, Great, Tx a lot for the macro. How do I make the adjustment in it to uncheck "Allow all users of the worksheet to: Select Locked Cells" (In other words, do NOT let users select locked cells.) Tx a million. S Gord Dibben wrote: S Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="justme" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="justme" Next n Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Sat, 22 Jan 2005 14:50:47 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Don Guillett wrote: You will need a for/each looping macro Win XP HE, SP1 Excel 2002 SP3 Hi, How can I protect the 25 worsheets contained in single workbook AT ONCE? I dt want to have to do it manually for each sheet.... Tx, S Tx, Don. Any chance u have one I can copy/paste? S Ok. Tx again. S |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number of worksheets | Excel Discussion (Misc queries) | |||
| HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
| How do I combine two worksheets into one graph | Charts and Charting in Excel | |||
| data entry on multiple worksheets | Excel Discussion (Misc queries) | |||
| Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) | |||
Linear Mode
