After input, move all rows 1 down

In your worksheet, open the VBE (Alt + F11), In the left window, double click on the sheet you are working on. In the window that opens, paste the following VBA code. Everytime you enter data in cells B,C and D, after entering data in D, the macro will insert a new row.

Please Login or Register  to view this content.

I am not familiar with Open Office so I don't know if this will work in that environment. It will work with Excel.

Why not just add the data to the end of the list?

Originally Posted by Arjan35

FDibbins:
The point is to make the list as simple as possible. I work with computer illiterates. Just inputting data at the top of the list should be the simplest way. (at the end of the year the list will be atleast 365 rows down. It will be to much effort for them to scroll down, use Ctrl + F etc etc.)

If the save the file where they last finished, it will be at the bottom anyway, they wont need to scroll anywhere. You could even set the sheet up so that when they hit enter, it auto moves to the right (if that is where they will be entering), and on the last entry of that row, it auto jumps to the next row, 1st entry cell

Alansidman:
the code works beautifully. Thank you very much.
Right now I am trying to adjust it (and understand it ) and insert it into the Excel file I am using at work.

Many thanks again.


FDibbins:
thank you for the tip. These little tweaks will save a lot of time as I encountered this issue in another Excel file I made.

Two follow up questions / tweaks to the code:

1a)
If cell D has no inserted value. When pressing enter. Nothing happens.
Only when I insert a number (like 0 for indicating nothing), the macros works. Is it possible to activate the macros without inserting any value in cell D but by only pressing Enter?

1b)
after pressing Enter. Reset the cursor to cell B (the start).


2)
The color background also moves when the macros is activated. Is it possible for the color background not to move? (I am using the color to indicate to my colleagues THIS is the area to input the new values.)


Again, thank you very much for your time. This is very helpful to me.

This is a worksheet change event that occurs automatically if a cell within the target changes. If there is no change, then nothing happens. If you don't want it to happen automatically with a change event, then we can set it to occur when you activate a macro. This however, is a manual step that you must invoke.

Once you make that decision, we can look at the other issues as that will drive how we treat the balance.

@Keith Leonard59

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread