Trouble adding rows with code

al97233

Board Regular
Joined
Nov 13, 2006
Messages
71
Awhile back Val was kind enough to write the following code for me which has worked very well up till now.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 3 And Target.Row = Me.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 2 And Target.Count = 1 And IsEmpty(Target.Value) = False Then
        Application.EnableEvents = False
        
        Me.Range("A" & Target.Row + 1).EntireRow.Insert
        
        With Me
            .Range("A9:D9").Copy
            .Range("A" & Target.Row + 1 & ":D" & Target.Row + 1).PasteSpecial Paste:=xlPasteFormats
            .Range("D" & Target.Row + 1).Formula = "=A" & Target.Row + 1 & " * C" & Target.Row + 1
            .Range("A" & Target.Row + 1).Select
        End With
        
        Application.CutCopyMode = False
        
    End If
    Application.EnableEvents = True

the problem I am having is that I now need to insert several rows into the work sheet and when I do the code no longer works properly. Unfortunately I do not have the knowledge to know how to revise this code to keep it working properly. Another problem I have is I would like to lock most of the cells and protect the work sheet so that I could tab through the appropriate cells rather than have to click through them with a mouse.

Any help with this would be greatly appreciated.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
First of all, let's get the code to insert rows correctly

Test this in a copy of your workbook
- user is asked to input the number of rows to be inserted

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 3 And Target.Row = Me.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 2 And Target.Count = 1 And IsEmpty(Target.Value) = False Then
        Application.Calculation = xlCalculationManual: Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Dim r As Long, rCount As Double
        rCount = Application.InputBox("How many rows to insert?", "Insert Rows", 1, , , , , 1)
        rCount = WorksheetFunction.Max(rCount, 1)                   'insert a minimum of 1 row
        
            For r = 1 To rCount
                Range("A" & Target.Row + 1).EntireRow.Insert
                Range("A9:D9").Copy
                Range("A" & Target.Row + 1 & ":D" & Target.Row + 1).PasteSpecial Paste:=xlPasteFormats
                Range("D" & Target.Row + 1).formula = "=A" & Target.Row + 1 & " * C" & Target.Row + 1
                Range("A" & Target.Row + 1).Select
                Application.CutCopyMode = False
            Next r
        
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
    End If
   
End Sub

I would like to lock most of the cells and protect the work sheet so that I could tab through the appropriate cells rather than have to click through them with a mouse.
Which cells should be locked?
 
Last edited:
Upvote 0
Yongle,

Thank you for the help but I think I have not made myself clear. What I am trying to do is insert rows above row #3 so that i can insert a company name and some other information. The code you sent me works great but after inserting it if I attempt to insert a row the code doesn't work properly. below is a sample of the workbook.


Book1
ABCD
1Rows are added automatically. In order for a row to be added you must enter a number into the "Sell" column. If you would like to add a blank row just enter "0" into that cell and tab out.
2
3Sales Person:Al Dennis
4Customer:Date:
5Job Name:Phone #:
6Attn:Lead Time:
7Note:Use tab key only below this line, do not use mouse (except first cell)
8QtyDescriptionSellExtended
9$0.00
10
11Total:$0.00
12
13
14
15
16
Sheet1
Cell Formulas
RangeFormula
D9=A9 * C9
D11=SUM(D9:D10)
 
Upvote 0
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   If Target.Column = 3 And Target.Row = Me.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 2 And Target.Count = 1 And IsEmpty(Target.Value) = False Then
      Application.EnableEvents = False
   
      Me.Range("A" & Target.Row + 1).EntireRow.Insert
   
      With Me
         Set Fnd = .Range("A:A").Find("Qty", , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Fnd.Offset(1).Resize(, 4).Copy
            .Range("A" & Target.Row + 1).Resize(, 4).PasteSpecial paste:=xlPasteFormats
            .Range("D" & Target.Row + 1).FormulaR1C1 = "=rc1*rc3"
            .Range("A" & Target.Row + 1).Select
         End If
      End With
      
      Application.CutCopyMode = False
      
   End If
   Application.EnableEvents = True
   End Sub
 
Upvote 0
This may be a better view.
Excel 2016 (Windows) 64 bit
A
B
C
D
1
Rows are added automatically. In order for a row to be added you must enter a number into the "Sell" column.
If you would like to add a blank row just enter "0" into that cell and tab out.
2
3
Sales Person:
Al Dennis
4
Customer:
Date:
5
Job Name:
Phone #:
6
Attn:
Lead Time:
7
Note:
Use tab key only below this line, do not use mouse (except first cell)
8
Qty​
Description
Sell​
Extended​
9
$0.00​
10
11
Total:​
$0.00​
12
13
14
15
16
Sheet: Sheet1
 
Upvote 0
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   If Target.Column = 3 And Target.Row = Me.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 2 And Target.Count = 1 And IsEmpty(Target.Value) = False Then
      Application.EnableEvents = False
   
      Me.Range("A" & Target.Row + 1).EntireRow.Insert
   
      With Me
         Set Fnd = .Range("A:A").Find("Qty", , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Fnd.Offset(1).Resize(, 4).Copy
            .Range("A" & Target.Row + 1).Resize(, 4).PasteSpecial paste:=xlPasteFormats
            .Range("D" & Target.Row + 1).FormulaR1C1 = "=rc1*rc3"
            .Range("A" & Target.Row + 1).Select
         End If
      End With
      
      Application.CutCopyMode = False
      
   End If
   Application.EnableEvents = True
   End Sub

Fluff,

this worked perfectly. thanks so much for your help!
Any idea how i can set the tab order to be B5,B6,B7,D5,D6,D7 then down to A10,B10,C10 and so on through the code you sent me? the problem I have had is i have been unable to set the tab order and protect the workbook without messing up the rest of the form.
 
Upvote 0
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   If Target.Column = 3 And Target.Row = Me.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - 2 And Target.Count = 1 And IsEmpty(Target.Value) = False Then
      Application.EnableEvents = False
      Me.Unprotect
      Me.Range("A" & Target.Row + 1).EntireRow.Insert
      
      With Me
         Set Fnd = .Range("A:A").Find("Qty", , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Fnd.Offset(1).Resize(, 4).Copy
            .Range("A" & Target.Row + 1).Resize(, 4).PasteSpecial paste:=xlPasteFormats
            .Range("D" & Target.Row + 1).FormulaR1C1 = "=rc1*rc3"
            .Range("A" & Target.Row + 1).Select
         End If
      End With
      
      Application.CutCopyMode = False
      
   End If
   Cells.Locked = True
   Range("B5:B7,D5:D7").Locked = False
   Target.Offset(1, -2).Resize(, 3).Locked = False
   Me.Protect
   Me.EnableSelection = xlUnlockedCells
   Application.EnableEvents = True
End Sub
 
Upvote 0
Fluff,

This one did not work. Would it be possible for me to send you the actual workbook? Below is a image with the revisions I had to make. I apologize for not knowing more about how to code. If I did i might be able to decipher what you did and just tweak it. I'm sure something i did to the layout probably messed up you code.


Book1
ABCD
3BAXTER & FLAMING IND., INC. 3717 NW ST HELENS ROAD PORTLAND, OR 97210 P: 503-225-0486 F: 503-227-8403This quote is subject to withdrawal or re-quote if not accepted within 30 days. Keying is excluded. Payment terms net 30 days with approved credit. Freight is F.O.B. Our shop unless noted otherwise
4Sales Person:Al Dennis
5Customer:Date:
6Job Name:Phone #:
7Attn:Lead Time:
8Note:Use tab key only below this line, do not use mouse (except first cell)
9QtyDescriptionSellExtended
10$0.00
11
12Total:$0.00
13
14
15
16
17
Sheet1
Cell Formulas
RangeFormula
D10=A10 * C10
D12=SUM(D10:D11)
 
Upvote 0
Please define "did not work"
 
Upvote 0
it would not contain the tab series to the specified cells and when i clicked into A10 i could not enter a quantity.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top