Results 1 to 7 of 7

Thread: Insert New Row From Drop Down
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2006
    Posts
    166
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert New Row From Drop Down

    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.

  2. #2
    Board Regular
    Join Date
    Apr 2006
    Posts
    166
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Row From Drop Down

    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

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,987
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Insert New Row From Drop Down

    Quote Originally Posted by barim View Post
    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("B2")
        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:=xlFormatFromRightOrBelow
            End If
        End If
    End Sub
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Apr 2006
    Posts
    166
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Row From Drop Down

    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.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,987
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Insert New Row From Drop Down

    Quote Originally Posted by barim View Post
    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.
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    Apr 2006
    Posts
    166
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert New Row From Drop Down

    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.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,987
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Insert New Row From Drop Down

    Quote Originally Posted by barim View Post
    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?
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •