Insert New Row From Drop Down

[barim]

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.

 

[barim]

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

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

 

[DanteAmor]

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

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.

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

 

[barim]

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.

 

[DanteAmor]

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.

 

[barim]

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.

 

[DanteAmor]

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?