Completely Stumped
 

Some kind souls help is requested to make my worksheet more user
friendly:

1) When data is filled in across cell range B14 – R14 and I then press
“enter”.... a new row is automatically inserted either above or
below.

2) The new row would retain the formulas in the cells just copied, but
none of the calculations or text. I am using using the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim lastrow As Long
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault
Rows(lastrow + 1).SpecialCells(xlConstants).ClearContents

End Sub

All is well until I "Protect" the worksheet. I do this to limit tab
order and because other cells above cell range B14 - R14 must not be
erased or changed by mistake. If anyone is interested in looking at the
relevant portion of this worksheet please let me know.

"Thank you" ... in advance for any suggestions you might have.


--
ZimBoy
------------------------------------------------------------------------
ZimBoy's Profile: http://www.excelforum.com/member.php...o&userid=24273
View this thread: http://www.excelforum.com/showthread...hreadid=382737

Hi Zimboy

In your macro, just before you do the row insert, set worksheet protection
to false, and then, after doing your thing, set it to true again.

ActiveSheet.Unprotect
and then do your thing
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


"ZimBoy" wrote:


Some kind souls help is requested to make my worksheet more user
friendly:

1) When data is filled in across cell range B14 €“ R14 and I then press
€œenter€�.... a new row is automatically inserted either above or
below.

2) The new row would retain the formulas in the cells just copied, but
none of the calculations or text. I am using using the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim lastrow As Long
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault
Rows(lastrow + 1).SpecialCells(xlConstants).ClearContents

End Sub

All is well until I "Protect" the worksheet. I do this to limit tab
order and because other cells above cell range B14 - R14 must not be
erased or changed by mistake. If anyone is interested in looking at the
relevant portion of this worksheet please let me know.

"Thank you" ... in advance for any suggestions you might have.


--
ZimBoy
------------------------------------------------------------------------
ZimBoy's Profile: http://www.excelforum.com/member.php...o&userid=24273
View this thread: http://www.excelforum.com/showthread...hreadid=382737