Solved: copij formula in protected sheet

[Ger]

Hi,

i'm looking for a way to copy a formula to a protected range.
i've a worksheet protected but it is possible to insert rows. Only problem is that the user cann't copy the formulas.
In the example you can insert a row (password = Martien) but the formulas in the grey cells cann't be copied by the user without unprotecting the sheet. is it possible to copij the formules with a macro that works as soon as a row is inserted?

[Teeroy]

I suggest you don't allow the user to insert rows and instead attach a button for this. The button can then activate a macro which will unprotect the sheet, insert the row, copy the formulas, and reapply the protection to the sheet.
Try this:

[vba]Sub test()
Dim oldrow As Long
' Unprotect sheet

ActiveSheet.Unprotect Password:="Martien"

' insert row below
oldrow = ActiveCell.Row
Rows(oldrow + 1).Insert
' copy formula cells
With Sheets("Blad1")
.Range("J" & oldrow).Copy .Range("J" & oldrow + 1)
.Range("O" & oldrow).Copy .Range("O" & oldrow + 1)
End With
' Protect sheet
ActiveSheet.Protect Password:="Martien", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFiltering:=True

End Sub[/vba]

[Ger]

Excuses for the late reaction, I was on holidays.
i was looking for a way without a button (if possible). Somewhere in this forum i found a macro that performed an action on doubleclick ( this macro didn't work in excell 2010), so i hoped that it was possible to perform an macro on insert row.

Ger

[Paul_Hossler]

Not sure if this is what you meant, but possible starting point. BeforeDoubleCLick is a WS event in 2010

In a standard module

[vba]
Option Explicit
Option Private Module
Public Const PW As String = "Martien"
Sub ProtectOn()
With Sheets("Blad1")
.Protect Password:=PW, AllowInsertingRows:=True, AllowDeletingRows:=True
.EnableSelection = xlUnlockedCells
End With
End Sub
Sub ProtectOff()
With Sheets("Blad1")
.Unprotect Password:=PW
End With
End Sub
Sub InsertCopyOfRow(r As Range)
Dim r1 As Range
Set r1 = r.Cells(1, 1).EntireRow
Call r1.Copy
r1.Insert Shift:=xlDown
End Sub
[/vba]

and in the WOrksheet module for Bild1

[vba]
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo InCase
Call ProtectOff
Call InsertCopyOfRow(Target.Cells(1, 1))

InCase:
Call ProtectOn
On Error GoTo 0
End Sub
[/vba]

Paul

[Ger]

Thx,

this is exactly where i'm looking for.

Ger