Hi birdy;

If I understood you well, you might try this one.

I regularly face this "problem" with my projects.
People must be able to insert new records (at the bottom of a
database/table/list), but may not change the contents of that database.

I solve that this way.

The fully protected database starts at row 9 with the headings/fieldnames so
that the first record is in row 10 (easy for counting etc).

Now in row 6 I put the same fieldnames as on row 9 and row 7 becomes the
"Input-record"
New stuff is inserted into this row which is always unprotected.

Then you can then do several things.

I first check the contents of the input record and when that is correct,
unprotect the database/worksheet, add the record at the bottom ot the
database,
format the database so that the new/last/added record looks like all the
others, clean the input-record and protect the database/worksheet again.

All this is triggered by a command-button.

Might that be a solution ?

--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands (Those who live some 18 feet below sea level)
--
E:
W: www.spreadsheetsolutions.nl
--

wrote in message
...
2003/2007

OK, I must not be asking the correct/logical question.

My w/s is protected; but the User can insert Rows.

I would like to have Excel automatically remove the protection
for the cells in the new row so that the cells (in all columns) can be
populated with data.

That said, I do not wish the User to ba able to change the data (except in
certain columns which are
not protected) in the cells that were their prior to the inserted row
data.

Truly, I am lost as to the best way to do this. Is there a way to do this?

I have played with worksheet events but I am having a problem obtaining
the original W/S row count
and comparing that to the row count after the row insertion. My idea was:
IF Sheets("Trial Balance Current").Rows.Count OrigRows Then
"Reformat only the added cells via the row insertion"

I am almost there but no home run yet.

Any help greatly appreciated!! Eagleone


The unsuccessful code that I have is:

Function OrigRows() As Long
OrigRows = Sheets("Trial Balance Current").UsedRange.Rows.Count
End Function

Private Sub Worksheet_Activate()

' How do I get OrigRows in the function above to the code next?

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Sheets("Trial Balance Current").Rows.Count OrigRows Then
Dim myRange As Range
ActiveSheet.Unprotect Password:="calldennis"
Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect Password:="calldennis", DrawingObjects:=False,
Contents:=True, _
Scenarios:=False, AllowFormattingCells:=True,
AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End If

End Sub