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

 

[paulgu]

There is an alternative to unprotecting and re-protecting the sheet - use the parameter UserInterfaceOnly:= True when protecting the sheet. This protects it from changes made by the user but not by those done by your code.
However you need to do this programmatically as it's not an option available when you do it from the Tools menu, and it needs to be done in the workbook Open event as it only lasts until the workbook is closed. After re-opening, if it's not done in the workbook Open event, the sheet is protected but against changes made by the code as well as the user.