Variation on the protection themes

[Risky Dave]

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

 

[Per Jessen]

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 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