Hi,

In Excel I would like to be able to restrict the places where a use
can insert rows. I have an idea how the macro should work, but I don'
have enough experience to write the code.
The macro should work as follows:

If the activecell is in one of the rows 13:23, 26:30, 32:36, 38:41
43:47, 49, 51:68 then call the macro "InsertCopyRow2" else a messagebo
should appear with the text "You can't insert a row here".
The macro "InsertCopyRow2" is as follows:

Sub InsertCopyRow2()

Application.ScreenUpdating = False
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow

Selection.Offset(1).EntireRow.SpecialCells(xlConst ants).ClearContents

End Sub

Can someone help me with this problem? Thanks in advance

--
leonida
-----------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537
View this thread: http://www.excelforum.com/showthread.php?threadid=55446

Hi Leonidas,

Question:
If a row is inserted at row 15, do all the allowed ranges shift down or
do the row numbers stay the same?
So does the next "unprotected" range becomes 27:31 or stay 26:30?

Greetz,

Lex

leonidas wrote:
Hi,

In Excel I would like to be able to restrict the places where a user
can insert rows. I have an idea how the macro should work, but I don't
have enough experience to write the code.
The macro should work as follows:

If the activecell is in one of the rows 13:23, 26:30, 32:36, 38:41,
43:47, 49, 51:68 then call the macro "InsertCopyRow2" else a messagebox
should appear with the text "You can't insert a row here".
The macro "InsertCopyRow2" is as follows:

Sub InsertCopyRow2()

Application.ScreenUpdating = False
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow

Selection.Offset(1).EntireRow.SpecialCells(xlConst ants).ClearContents

End Sub

Can someone help me with this problem? Thanks in advance!


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=554464