Insert Row macro is awesome - except for one little thing...

I found this snippet of code, and it works beautifully for my need to be able to insert a row, maintaining formulas and formats, but clearing constants.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  'David McRitchie,  2007-09-07    insrtrow.htm on double-click
  '-- will copy more often than  Extend Formulas and Format (tools option)
  Cancel = True
  Target.EntireRow.Copy
  Cells(Target.Row + 1, 1).EntireRow.Insert
  Cells(Target.Row + 1, 1).EntireRow.Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  On Error Resume Next
  '-- customize range for what cells constants can be removed --
  Intersect(Selection, Range("a:IV")).SpecialCells(xlConstants).ClearContents
  On Error GoTo 0
End Sub

I would like to make a couple of tweaks:

1.  I would like to limit the macro to inserting rows below Row 20.  That way, users would be unable to screw up any page formatting in Rows 1-19.

Even better, I'd like to limit the macro to operate on a table.  That way, the macro will never be able to run on any rows BELOW the last line of the table.

Related topic:  Is there a way to allow users to delete rows, but only within a range of rows?  I know I can protect a sheet from row deletion, but it's a global allow/disallow thing.

And finally, can I limit the scrolling to the middle portion only?  In this case it would be Ross-Master-Quote---Macro-enabled-ExpertRow 21-24 (plus any additional inserted rows above Row 24.)


I've attached a copy of the file, with personal info redacted.

Thanks!
eracerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Above this line:

Target.EntireRow.Copy


Insert this:

If Target.Row < 20 Then Exit Sub

I will keep thinking about the second tweak, it will just be an OR statement within the above line but will need to determine last row of table first.

Thanks
Rob H
0
eracerAuthor Commented:
The last row is going to be tricky, since adding rows will increment the last row's number.

The 'If Target Row...' statement worked perfectly.  Thanks!
I'm going to put a warning on the sheet for users not to run the macro on any rows below the new rows that pop up.
0
eracerAuthor Commented:
For some reason, I didn't notice that protecting the worksheet causes a [Run-time Error '1004': Insert method of Range class failed.] when the 'Insert Row' macro runs.

I need to be able to protect the worksheet, but I need my people to be able to use the event macro.

Enabling users ability to 'Insert Rows' in a protected workbook fixes the problem, but I'm hoping to avoid giving them that ability.

I saved the worksheet to C:\users\%user%\appdata\roaming\microsoft\templates (since this is a trusted location, and will run the macro without prompting.  Even so, the error occurs whether I open the template, or the sheet in my documents folder (which does prompt to enable the macro.)

It doesn't matter whether the individual cells are locked or unlocked.  If the sheet is protected, the macro fails.  If it's unprotected, the macro runs.

Here's the Debug:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  'David McRitchie,  2007-09-07    insrtrow.htm on double-click
  '-- will copy more often than  Extend Formulas and Format (tools option)
  Cancel = True
  If Target.Row < 24 Then Exit Sub
  Target.EntireRow.Copy
'.................The following line is highlighted yellow, with an arrow pointing at it.................
    Cells(Target.Row + 1, 1).EntireRow.Insert
'..................No other lines are highlighted..............
Cells(Target.Row + 1, 1).EntireRow.Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  On Error Resume Next
  '-- customize range for what cells constants can be removed --
  Intersect(Selection, Range("a:IV")).SpecialCells(xlConstants).ClearContents
  On Error GoTo 0
End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

J2FCommented:
might I suggest:
Add code at start of macro to unprotect the worksheet and add code at end of macro to reset the worksheet protection.
Also, I have found it useful to have the code remember the activecell when first called and return the cursor to that cell at the end.
0
eracerAuthor Commented:
Thanks J2F.

I know a little bit about Excel, but I know nothing about VBA.  Amazing how the internet can make me look educated, when I'm actually ignorant.  I'm learning a little bit from experts here, and my boss is saying, "Oooooo, how did you know how to do THAT?"  ;)


I'm going to look up the code for adding the 'Protection off/on' to the macro.

In my case, I want the cursor to stay on the last row inserted.  It would be nice to have it move two columns over, however.
0
Rob HensonFinance AnalystCommented:
The two lines that you will need are:

1) ActiveSheet.Unprotect Password:="password"

2) ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

Line 1 will need to go after the row number check that I suggested previously

If Target.Row < 24 Then Exit Sub

Line 2 will go after the line:

On Error GoTo 0

You can change the "password" part but it needs to be enclosed in double quotes.
Thanks,
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J2FCommented:
to move the cursor over two columns
1. below the line which reads
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    add the following two lines
Dim curAddress as String
curAddress=Activecell,address 'this will record the cursor location when this procedure is called

2. above the line which reads
        On Error GoTo 0
    add the line
Range(curAddress).Offset(1,2).Select 'this places the cursor in the newly inserted row two columns over

Note: if the cursor doesn't wind up where you want it, tweak the values within offset as needed.
the first value (1 in the example) controls the rows from the original - positive moves down, negative moves up
the second value (2 in the example) controls the columns from the original - positive moves right, negative moves left

You can search Excel's Help for more details on functions and commands.
0
eracerAuthor Commented:
Thanks J2F.

I need to spend some time learning more about Excel (and VBA) for sure.  Right now I'm tasked with getting a worksheet done that meets the bosses needs.  Having resources like you guys is great, and I'm learning some as I go.
0
J2FCommented:
Part of what I know about Excel, I've learned by studying other people's code. (I learn a lot from this forum)
A little of what I know about Excel has been gleaned from books.
The rest of what I know about Excel is from "trial and error".

Hang in there. The level of your expertise in Excel is indirectly proportional to how much sleep you get :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.