Insert New Row From Drop Down

barim

Board Regular
Joined
Apr 19, 2006
Messages
176
Hello,

I would like to know if it's possible to insert a new row if you select from drop down "Insert New Row". All data should shift down and cell where drop downs are should have "Insert New Row" fixed as a selection. Is this possible?

Thank you.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Is it possible to convert this piece of code to Worksheet_Change event triggered by drop down selection "Insert New Row"?

Code:
Sub insertRow()

    Dim rng As Range
    Dim rw As Long

    With ActiveCell
        rw = .Row
        .Offset(1).EntireRow.Insert
    End With

    Set rng = Rows(rw + 1)
    rng.Columns("B:C").Interior.Color = RGB(191, 191, 191)

End Sub
 
Upvote 0
Is it possible to convert this piece of code to Worksheet_Change event triggered by drop down selection "Insert New Row"?

Code:
Sub insertRow()

    Dim rng As Range
    Dim rw As Long

    With ActiveCell
        rw = .Row
        .Offset(1).EntireRow.Insert
    End With

    Set rng = Rows(rw + 1)
    rng.Columns("B:C").Interior.Color = RGB(191, 191, 191)

End Sub

Put the following code in the events of your sheet.
Change B2 to the cell of your drop down.
Change xlFormatFromRightOrBelow to xlFormatFromLeftOrAbove if you want to copy the format from the previous line.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Range("[COLOR=#ff0000]B2[/COLOR]")
    If Not Intersect(Target, r) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "Insert New Row" Then
            r.Offset(1).EntireRow.Insert CopyOrigin:=[COLOR=#ff0000]xlFormatFromRightOrBelow[/COLOR]
        End If
    End If
End Sub
 
Upvote 0
Sorry for the late response I just saw your reply. This works really good and now I see where I made mistakes. Is it possible to have printed "New Item" in the first cell of the inserted row. My r Range is actually "A2" which I corrected. Thanks so much for your help on this.
 
Upvote 0
Sorry for the late response I just saw your reply. This works really good and now I see where I made mistakes. Is it possible to have printed "New Item" in the first cell of the inserted row. My r Range is actually "A2" which I corrected. Thanks so much for your help on this.

Sorry, I do not understand, you could describe step by step what you want to happen.
 
Upvote 0
I have cell A2 which I will use to add rows with drop downs. This newly inserted row, which starts from cell A3 should automatically have selected value "New Item" from drop down. This is not a big deal, I can make this selection manually.

My other issue is that rows below A3 have by default selected value "Item1" from drop down. I would like to have entire rows locked for editing as long as some other selection has been made. So, if cells A4 through the end of data have selection "Item1" entire rows should be protected from editing. If any other selection has been made from drop down entire row should be open for editing. Is this possible to do? Thanks again.
 
Upvote 0
I have cell A2 which I will use to add rows with drop downs. This newly inserted row, which starts from cell A3 should automatically have selected value "New Item" from drop down. This is not a big deal, I can make this selection manually.

My other issue is that rows below A3 have by default selected value "Item1" from drop down. I would like to have entire rows locked for editing as long as some other selection has been made. So, if cells A4 through the end of data have selection "Item1" entire rows should be protected from editing. If any other selection has been made from drop down entire row should be open for editing. Is this possible to do? Thanks again.


Sorry, but I still do not understand.
This has to do with the original requirement or is it a new request?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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