PDA

View Full Version : Protect Dynamically Created Worksheets



ukdane
11-26-2008, 05:09 AM
Part of my workbook creates new worksheets, from a template worksheet.
I have the following code on the template worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
Rows("1:2").Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
End Sub
This protects the first two rows of the template worksheet.

How do I get this to be copied onto the new worksheet, when it is created?

If it's not possible, how can I protect the first two rows of the new worksheet when it's created?

Bob Phillips
11-26-2008, 05:37 AM
It would be better to use a Workbook event for selection change rather than the worksheet event if you want it to apply to all worksheets.

GTO
11-26-2008, 05:47 AM
Greetings Ukdane,

If the 'template worksheet' has your code in the sheet's module, this will be copied along with the sheet.

I would mention though, that .Locked = True is the default for any cell. Thus - unless you changed all the cells on the template sheet to .Locked = False prior to the code shown... what you have done is to protected the entire sheet's cells. Hopefully I explained that sensibly enough; but if not, maybe a bit more of an explanation as to what you want the newly created sheets to be able to (or limited to) do would be helpful.

Mark

PS - Well, slow fingers + slow laptop; saw your input after hitting advanced button...

Hi Bob and good midday to you sir. Off to bed for me (0541 hrs here). I understood this to be more at only based on a new sheet based on the 'template' sheet, so will still 'chunk' my thought in... may well be 'off the tracks' (my bad if such is the case).

A great day to you both :-)

ukdane
11-26-2008, 05:47 AM
That certainly works to lock all the worksheets. (I only need the first two rows to be locked)
and, I do have one worksheet that needs the first two rows to be left unlock. I assume I can do that locally, ie


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="password"
Rows("1:2").Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
End Sub


But how should that be adjusted so that the cells are unlocked?

Bob Phillips
11-26-2008, 06:54 AM
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name <> "some sheet" Then

Sh.Unprotect Password:="password"
Sh.Rows("1:2").Locked = True
Sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
End If
End Sub

ukdane
11-26-2008, 06:57 AM
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'fillFormula 'Only for sheets Jan-Dec.
ActiveSheet.Unprotect Password:="password"
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Rows("1:2").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.cell("A3").Select
End Sub


Edit: Cross-post. Sorry.

This code causes a runtime error 438.

Bob Phillips
11-26-2008, 07:07 AM
Did you add to THisWorkbook?

You don't use ACtivesheet, you use sh as I showed you.

Why are you unlocking all cells?

ukdane
11-26-2008, 07:15 AM
XLD: The above was a cross post (I'm now using the code from your last post, where you use "sh.")

I'm unlocking all cells, because I need all the cells in the workbook to be unlocked, with the exception of the first 2 rows.

Bob Phillips
11-26-2008, 07:29 AM
So do it once manually, don't do all cells every time in code, that just makes no sense.

Ischyros
11-26-2008, 01:27 PM
Sub createtemplate() 'Creates new worksheets and protects rows 1&2
'Below is a simple program that just adds a worksheet and then sets the security to rows 1 and 2 to unlocked. Once the sheet is protected only these two rows can be edited. You could add this following the code you have for creating the new worksheets. It will change depending on where the new worksheet is inserted.
---------------------------------------
Dim x As Integer

Worksheets.Add , Worksheets("Template"), 1, xlWorksheet 'adds one worksheet after the template worksheet

x = Worksheets("Template").Index + 1

Worksheets(x).Cells.Select
Selection.Locked = False
Worksheets(x).Rows("1:2").Select
Selection.Locked = True

Worksheets(x).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
----------------------------------------
Let me know if this is what you were looking for! Best of Luck!