dynamically adding a row

[fei2010]

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!

[stnkynts]

Your post was extremely vague so the macro is extremely vague in response. I am assuming your cell background color is true black, in other words, will have a color index of 1. Just create a command button and assign this macro to it:

Please Login or Register  to view this content.

[fei2010]

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!

[stnkynts]

For your add a row button:

Please Login or Register  to view this content.

For your create a new sheet button:

Please Login or Register  to view this content.

Let me know.

[fei2010]

got it working, thank you so much stnkynts!