Copy the formula from the protected cells

[Guest]

Hello all,

I have an Excel file that the users input the information. The sheet in the
file contains 10 columns (A-J), and the columns B,C,D and G are protected and
formula-driven. The way I have setup is the users can insert a new row
between two rows. The problem I am having is since some of the columns are
protected, the formula in those cells are not copied to the protected new
cells on the new row.

Is there a code that does the copying of the formula from the above
protected cell? Other suggestions are welcomed.

Thanks.

 

[Norman Jones]

Hi AccessHelp,

See David McRitchie's InsertRowsAndFillFormulas sub at:

http://www.mvps.org/dmcritchie/excel/insrtrow.htm

To deal wirh the protection issue, you could assign the macro to a button
and either:

(1) Turn protection off at the start of the macro and restore protection at
the end, or

(2) Setting the Protect method's UserInterfaceOnly parameter to true enables
VBA manipulation of the protected sheet. However, this setting is not
persistent and needs to be reset each time the workbook is opened.

Perhaps, therefore, you could set protection in the Workbook_Open or
Auto_Open procedures, e.g.:

'============>>
Sub Auto_Open()
With Worksheets("sheet1")
.Protect Password:="drowssap", UserInterfaceOnly:=True
End With
End Sub
'<<============