Variation on the protection themes
 

Hi,

I've been rooting through various sheet protection questions that have been
asked and am not sure that my particular provblem has been answered.

I have a series of sheets within a workbook that I want to protect so that
users are only able to change the values in certain cells. There are two
twists to my particular version of this old question:

1) The sheets in question each have an active worksheet function that
operates on them:
Private Sub Worksheet_Activate()
Rows("2:1002").EntireRow.AutoFit
End Sub

Which autosizes the data entry lines as they change (looks pretty and
impresses my boss!). I want to keep this functionality, but obviously some of
the cells that need to be resized are those that I also need to lock down,
which is causing an error when the code tried to run;

2) The worksheets are being used as a database (yes this would all be easier
in Access - but let's not go there!) and as a data set is added, new lines
are inserted at the bottom of each sheet to take the various data elements. I
need the protection code to be intelligent enough to automatically apply
itself each time a new line is added by the users. I think I can probably do
this if the problem above can be solved - I assume it can be managed in the
same way that I would apply, say, formatting rules to cells/ranges? Would
appreciate any advice/guidance on this, though.

I'm still new to this VBA stuff, so please don't make any assumptions about
my level of knowledge - I have little/none!

This is on XL 2007 under Vista, if that makes any difference.

TIA

Dave

Variation on the protection themes
 

Hi Dave

1) As the sheet is protected, it has to be unprotected before you can
manipulate the rows:

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="JustMe"
Rows("2:1002").EntireRow.AutoFit
ActiveSheet.Protect Password:="JustMe"
End Sub

2) Maybe this will help you:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Range("A" & Target.Row, "D" & Target.Row).Select
EmptyCell = False
For Each cell In Selection
If cell.Value = "" Then
EmptyCell = True
End If
Next
If EmptyCell = False Then ' Column A:D contain data
ActiveSheet.Unprotect Password:="JustMe"
Rows(Target.Row).Locked = True
nRow = Target.Row + 1
Rows(nRow).Locked = False
ActiveSheet.Protect Password:="JustMe"
End If
Target.Offset(0, 1).Select
Application.ScreenUpdating = True
End Sub

Regards,
Per

"Risky Dave" skrev i meddelelsen
...
Hi,

I've been rooting through various sheet protection questions that have
been
asked and am not sure that my particular provblem has been answered.

I have a series of sheets within a workbook that I want to protect so that
users are only able to change the values in certain cells. There are two
twists to my particular version of this old question:

1) The sheets in question each have an active worksheet function that
operates on them:
Private Sub Worksheet_Activate()
Rows("2:1002").EntireRow.AutoFit
End Sub

Which autosizes the data entry lines as they change (looks pretty and
impresses my boss!). I want to keep this functionality, but obviously some
of
the cells that need to be resized are those that I also need to lock down,
which is causing an error when the code tried to run;

2) The worksheets are being used as a database (yes this would all be
easier
in Access - but let's not go there!) and as a data set is added, new lines
are inserted at the bottom of each sheet to take the various data
elements. I
need the protection code to be intelligent enough to automatically apply
itself each time a new line is added by the users. I think I can probably
do
this if the problem above can be solved - I assume it can be managed in
the
same way that I would apply, say, formatting rules to cells/ranges? Would
appreciate any advice/guidance on this, though.

I'm still new to this VBA stuff, so please don't make any assumptions
about
my level of knowledge - I have little/none!

This is on XL 2007 under Vista, if that makes any difference.

TIA

Dave