Insert New Row From Drop Down

barim

Board Regular
Joined
Apr 19, 2006
Messages
167
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
Joined
Apr 19, 2006
Messages
167
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
Joined
Dec 3, 2018
Messages
7,920
Office Version
2007
Platform
Windows
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
Joined
Apr 19, 2006
Messages
167
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
Joined
Dec 3, 2018
Messages
7,920
Office Version
2007
Platform
Windows
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
Joined
Apr 19, 2006
Messages
167
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
Joined
Dec 3, 2018
Messages
7,920
Office Version
2007
Platform
Windows
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?
 

Forum statistics

Threads
1,078,451
Messages
5,340,369
Members
399,371
Latest member
wilbot

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top