Insert New Row From Drop Down

barim

Board Regular
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

Board Regular
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
 

DanteAmor

Well-known Member
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
 

barim

Board Regular
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

Well-known Member
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

Board Regular
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

Well-known Member
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?
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top