Thread: dynamically adding a row

I have been using excel for quite a couple years but all limited to basic functionality. Now I am trying to do some advance tasks so I can create a simple application so people without any of excel knowledge can use it.

I am using excel 2010.

One of the task I need to implemented is that:
0. create a button in one cell. when user click the button, do following:
1. find a cell that the background is black color.
2. insert a row above the row contains the above found cell.
3. do some formatting, restriction(lock cell), put some formula in some cell on that row. etc.

could you some point me some direction? a few samples or links that can get me started?

Thanks!

sorry for the vague question, your answer is exactly what I am looking for.

let me take a step back and explain what I trying to do. I want to create a simple worksheet for someone else who has not much experience on excel to use. There will be formatting/validation/formula etc on the sheet, I wanted that users can only change those cells they need to and won't mess up all the formatting/validation/formula. I've attached a excel file for demonstration purpose.

In that "TestSheet1", the top left area that surrounded by the black background cells are user working area. The first 4 columns are allow user to input, and the rest 2 columns are calucated with formula. Note those columns are already have predefined type(date/currency/percetage etc) and also have some validate inplace(such as those prices are between -99999 to 99999). I also put the first 4 columns in unlocked mode and the whole excel is protected(with no password now) so users won't be able to change column E and F. 10 rows are predefined, but in case they need more, click the button(cell I3) to add a new row right before the black row, and automatically put those formatting/validation/formula there. (for users that has experience on excel, the easiest way is to just insert a row and copy from existing row, but for users without much excel experience, it might just mess it up).

Another functionality I hope to build is to click a button and create a new template sheet. for example, user click cell ("Create a new sheet" in cell I4) and content in "TestSheet2" is created and he can start working on.

I created the test excel file in excel 2010 and saved as .xls so hope it works on all versions of excel.

Thanks very much for helping!

For your add a row button:

Sub AddaNewRow()
Dim lastrow As Long

Application.ScreenUpdating = False
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("A" & lastrow).Offset(1, 0).EntireRow.Insert Shift:=xlDown
ActiveSheet.Range("A" & lastrow, "F" & lastrow).Copy
ActiveSheet.Range("A" & lastrow).Offset(1, 0).PasteSpecial xlPasteAll
ActiveSheet.Range("A" & lastrow, "D" & lastrow).Offset(1, 0).ClearContents
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

For your create a new sheet button:

Sub CreateNewSheet()
Dim lastrow As Long
Dim NewName As Variant
Dim newsht As Worksheet

lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
ActiveSheet.Cells.Copy
Set newsht = Sheets.Add(After:=Sheets(Sheets.Count))
newsht.Paste
newsht.Range("A2:D" & lastrow).ClearContents
NewName = Application.InputBox("What would you like to name the new sheet?", "New Sheet Name")
If NewName = "" Then
ElseIf NewName = False Then
Else
newsht.Name = NewName
End If
newsht.Range("A2").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Let me know.

got it working, thank you so much stnkynts!