?
Solved

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

Posted on 2014-08-06
9
Medium Priority
?
423 Views
Last Modified: 2014-08-07
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!
0
Comment
Question by:eracer
  • 4
  • 3
  • 2
9 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 40244006
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
 

Author Comment

by:eracer
ID: 40244122
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
 

Author Comment

by:eracer
ID: 40244533
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Assisted Solution

by:J2F
J2F earned 1332 total points
ID: 40245898
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
 

Author Comment

by:eracer
ID: 40246241
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
 
LVL 34

Accepted Solution

by:
Rob Henson earned 668 total points
ID: 40246329
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
 
LVL 1

Assisted Solution

by:J2F
J2F earned 1332 total points
ID: 40246799
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
 

Author Comment

by:eracer
ID: 40246823
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
 
LVL 1

Expert Comment

by:J2F
ID: 40247154
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question