Automatically move down row/insert new row - to prevent scrolling down

First of all, you should have used code tabs so the code displays properly with indents.

You say that you want to add data on row 15 and have it transported to the bottom of the data once the until figure is added. I notice that the sum calculations on row 13 include the new information added on row 15. Is this intentional? Don't you want the totals to start on row 16? I assume that there is no data on row 15 after the Until column even though the example shows some.

I will proceed with these assumptions. The solution I will suggest will use Excel Tables, so I suggest you read about them.

P.S. Do you want an automatic trigger, or do you want to enter in all the information and then click a button to make it happen. I am wondering on how to handle the break times.

Thanks for the clarifications.

I also see a couple of other fields, Multiply, Income and Costs that appear to be manual input as well.

One of the things we can do is protect the sheet and lock the cells that we don't want to update. This way you can enter data in a cell, and when you press tab or enter, the cursor will jump to the next updatable cell.

I think I will have to give you a button to do the transaction. There is no way to know how many breaks you will take, so I don't have something to trigger the code automatically.

I will do my best to explain why I do things as I go along. In addition to tables, I will also introduce data validation and named dynamic ranges.

I would also appreciate and explanation of what you are doing with the quarter calculations and the information in columns R:X

The first thing I am going to recommend is that you open the VBA Browser and then select Tools > Options.

Click OFF the Auto Syntax check. You'll see bad syntax because the text is in red. The Auto Syntax Check will stop you from proceeding until you correct the condition. Sometimes you want to temporarily accept bad syntax while you copy / paste coe from somewhere else.

Click ON Require Variable Declaration. This will put the key words, "Option Explicit" at the top of every module you open. This means you must declare your variables before you use them. It's a good way to check spelling errors in variables. This will save you many headaches in your coding.

I thought of another question. Is there any information, such as Client Name, that you would like to subject to data validation?

I can think of some data validations such as you can only enter a valid date equal to today or earlier. End times must be greater than begin times, Category must be a whole number in the range ? to ?, etc.

Thanks dflak, i turned off the Auto Syntax Check, and turned on the Require Variable Declaration. The multiply, income and costs fields are manual as well indeed, so locking the cells in between would seem like a good thing. Right now i sort of tried to do that visually with the background colours of the cells, but locking them altogether would be a lot better for sure (and nice and quick when proceeding to the next fillable cell with tab/enter). A button would indeed some better, seeing how this thing is kinda complicated already haha.

The quarter, week and yearly overviews are there just to see the hours i worked - mostly for self-motivating purposes and so i don't find out at the end of the year i've been slacking xD. Also the tax department requires a certain amount of hours to be made each year, to be able to get some tax benefits for self-employed people like me, so there's a more serious relevance as well.

The R to X columns were experiments to create some sort of search tool to be able to create a simple one-look overview of my actual income per hour, with the specific functionality to be used with different criteria. Here, i could for example compare my hourly income for my photography work versus my legal work, or my income for client X versus my income versus client Y. Of course i know what my actual hourly rate is, but in practice there is so much time involved which i don't charge (travel time, editing at home, phone calls, reviewing my work, e-mails etc.). So i really want to have a better way of understanding what my actual income is per hour after deducting all those non-billable hours (which can add up big time, but were never kept track of before i started witht his excel sheet a week ago). Besides the main purpose of time tracking, this function is something i really want.

About the data validation; i thought about this myself as well, but with the flexibility of the SUMIF function, i thought it best to not have hard requirements for any input data. The way i fill in each of the three descriptive columns is done in a very conscious way, with similar descriptive words repeating for similar jobs and so on, with the search tool in the back of my mind all the time. Column C in the real sheet (which is in Dutch) is also not named 'client' but 'concerns', to keep it more flexible, because i know i will be filling in different things here as well from time to time, for example 'administration' (although this could also be a 'category' of its own). Either way, i kind of like to keep things open 'on paper', i've got the tight structure in my head haha. But thanks a lot for thinking along, it's really appreciated and enjoy the process of thinking through such stepts. I'm just very excited to get this thing to work, and i really need it as well cause i'm an administrative disaster without it haha

I think I have most of it.

I did not work with columns R - X since I'm still not sure what you want there. I think I have the rest of he formulas on the top part correct. I encourage you to look at them.

I worked some magic on cells A8:A11 to show the last four weeks. I will explain this later.

Cell A15 has data validation on it. It will allow only a date entry and it must be today or older. You can't add dates in the future.

B15 is limited to a whole number between 1 and 5. I have no idea what you use this for.

C15, D15 and E15 are free-form entry.

F15 is limited to a time between 00:00:00 and 23:59:59

G15 must also be a time, but it has to be later than F15

I also made similar data validations for the breaks.

The rest of the formulas are as you had them originally.

I modified the Week formula: =YEAR([@Datum])&"W"&TEXT(WEEKNUM([@Datum]),"00") - this forces a leading zero for months less than October.

My month formula should work for you and not for me. My regional settings are English. Fortunately, for the testing, Dec and Jan are the same in both languages.

The quarter formula calculates the formula. I do not make a distinction for the year. This is something we may have to tweak for the formulas above row 15.

You can see from the formulas and the code one of the advantages of using tables: natural language syntax and also tables "know" how big they are.

I did a bit of magic on getting the last 4 weeks. I like to use "regular" Excel to do as much of the work as possible and sew the pieces together with VBA

On the Lookup Sheet, I have a pivot table whose source is the main table on the Time Manager sheet. It has as its row the week, so it gives a unique list of weeks.

Columns C and D are some calculations:
D1 is the number of dates that there are in the pivot table
D2 is the maximum number of dates we need to see
D3 is the number of rows we have to go down to get the start date for the range
D4 is the total number of rows to return to the range.

So I have a named range with the formula: =OFFSET(Lookups!$A$2,Lookups!$D$3,0,Lookups!$D$4,1)

The offset command has 5 variables:
The starting point - in this case cell A2
The number of rows to go down - the value in cell D3
The number of columns to go to the right - 0
The number of rows to return - the value in cell D4
The number of columns to return - 1

A named dynamic range can be used in VBA coding. If you are working with a dynamic range, it is usually easier to do it on the sheet than in the code.

Cell F1 has the formula: =Last_4Week - I highlighted cells F1:F4 entered this formula and then pressed CTRL-Enter to make it an array formula. Since there are only two elements in the dynamic range, the last two rows come out as #N/A. I have a formula in the cells in column G to determine if we want to use the row. Column H is the results. If there is a date for the row, use the date, otherwise show blank. This is a static range with the name Last_4Show. I use this in an array formula on the Time Manger sheet.

You can get a better feel for how the dynamic range works by adding dates under the data in the pivot table. Any string value will do. Just clear them out before going live with the application.

The code itself is exceptionally simple:

Please Login or Register  to view this content.

You should be able to unprotect the sheet, clear out the test data and go to work.

Hi dflak, what a magic you worked indeed, a true Christmas miracle haha! First and foremost, thanks so much for your help!! I've fooling around with the sheet for the last two days, trying to perfect it to my taste and also extensively test out if everything works correctly. There's a couple of minor (i think) things that i'd love to get your help on still, but first i'll let you know what i've been doing on the sheet and how i like it.

My original intent was for the inserted data to be moved down just a single row, but this is so much better. With the functionality of the table I can now carelessly add in older (forgotten) work times, and still be able to get them in the right chronology by using the functions on the pulldown-menu from the ‘date’ header column, exciting stuff! Alright so what did i do? I removed the data validation on the ‘category’ column, because I like to be able to differentiate between different tasks even more, even within the same job. So I got 4 columns where I can specific my work now: B, C, D and E. So for example when I photograph a house I will write: Client (C), Adress of the house (D), ordered products: photography and video (E). But all of these things stay the same when I want to write my time for editing the photo’s and video at home. So the way to do that in as little space as possible is that I assign category number 2 to all my editing work, and number 1 for the actual on-site work. Of course I could write out “on-site” and “editing at home” in column E, but it takes up a lot more space and since this differentiation is super repetitive anyway, might as well assign a number to it – keeping column E open for other details and specifics. Also, I’ve got a big project which I’m doing with friends, and we want to go use this same format for keeping track of our hours on that project, and assigning a category number to each person is a quick and space-saving way to be able to differentiate between our respective data entries. (This joint project is also why I have the multiplier in there, because we agreed that certain tasks should weigh more than others – depending on how hard/boring they are.)

I also removed the data validation on the ‘from’ and ‘until’ columns, because it prevented the ability to start before midnight and end after midnight (something that happens more often then I’d like). I also changed the format of the cells to custom uu:mm (hh:mm in English), prior to this change it refused to give the correct times when starting before and ending after midnight. So that entire issue is now solved. I also added in the formulas to properly display sum#2 and sum#3 on row 15 now, which is nice to see while entering in new data. I also removed the empty row beneath row 15, just to keep everything as minimalistic and tight as possible. Finally I changed the formulas for sum#2 and sum#3, because even though it was displaying the right amount of hours when a work shift went past midnight, in reality the number in the cell was 24 hours too high. So I spent a lot of time figuring out how to bracket IF and Multiply into those formulas to get it right, and also correct the ones for the breaks (my old formulas were skewed for the breaks and sum#2 when going after midnight). It was quite a hassle, but it's all working perfectly right now :D And i revamped the system with the search tools in the upper right corner. I think i've got it to work quite nicely - it's definitely not the most minimalistic but it's quite good for comparisons to have things all side by side (or rather underneath). For now that will suffice, it's not the most high tech but it will do the job for now.

If you could spare some more time, I would love to receive some more help on the following things:

1. An issue that persists is that now and again while working with the sheet the ‘protection’ turns on automatically, and I get the error message: “The cell or chart you’re trying to change is on a protected sheet.” The answer I get when I google this issue is that it’s likely something in the VBA. So I tried changing this line “shtT.Protect Userinterfaceonly:=True” in the VBA from True to False but that didn’t resolve the issue. The protection seems to always turn on after adding in a new line of data and pressing the button. When you try to select the new row of data that you just added, it gives the error. Do you have a solution for this? I would much prefer to have no protection at all, since I feel confident enough to not completely screw up the sheet by now :p

2. As soon as the list with data fills the entire screen, adding a new line and pressing the button will ‘follow’ the line that gets moved to the bottom, and so the screen jumps to the bottom of the data. This isn’t a major problem, but if it’s a simple thing to change, I would very much like it not to move down (as instead of having to scroll down to fill in data, now you have to scroll up after adding data hehe).

3. Somehow the monthly totals don’t add up correctly for June and july. The formula seems to make sense and is similar to all the other months, and the cells are formatted to [u]:mm just like the others. I’m really at a loss here. This should be rather straight-forward you’d think, but I can’t figure it out … On another note, if it would make things clearer or easier for coding or whatever, feel free to change the last remnants of Dutch into English. I will be using this form as is anyway, not gonna change it back to Dutch and risk screwing up code somewhere – I don’t mind the English anyway.

4. At one point after trying to insert a new data entry (with normal parameters) clicking the button I got the following error:

Run-time error ‘1004’:
Method ‘Range’ of object ‘_Global’ failed

After clicking “debug” in the pop-up window that had appeared, it opened up the VBA and showed me the following line of code marked in yellow:

If Len(Intersect(Range("Table_Times[Datum]"), Range(Range("Table_Times").Row & ":" & Range("Table_Times").Row))) = 0 Then

It only occurred once, so I’m not too worried about this, but perhaps there’s some useful information for you in this.

5. Lastly, I’m sorry to say but I really have no clue how the pivot table in the Lookups sheet works and what it’s functionality is, i tried messing with it but i just don't see how it works. This really is above my understanding of excel at the moment unfortunately. Perhaps future-me will understand this and get some use out of it, but for now I’ll just leave it as is. Related to this though (i think): i dont understand whether i have to do something to make all last 4 weeks appear in the general weekly overview in A9 to A12. This probably needs some action on my part with the pivot table, but yeah ... noob To be honest, i would also prefer to have all the required info for the sheet on the sheet itself, as this sheet will be joined by many other administrative sheets and i would prefer to have only sheet tabs in which i actually work, not just for code or supportive functionalities. So if it's just to help with the weekly overview, then i say best to remove it and forget about that overview, it's not that important anyway. I really hope you haven't put much time in this part, as i would feel super guilty and ungrateful

Thanks again so much for your help!

example forum3 - datumnotatie gewijzigd - sum2 en sum3 in rij 15 formule ingevoerd.xlsb

I'll have to take a longer look to address all the issues. If you don't want to lock the sheet every time, comment out the shtT.Protect Userinterfaceonly:=True line. Then unprotect the sheet manually once.

Working across regional settings creates some problems. Maybe there will be some future release that will make the translations.

Removing the User Interface only in the code worked for me. I had to go to Review and unprotect the sheet there, but it stays unprotected. I changed the data validations for the times to be a decimal between 0 and 1. Since, to Excel, 1 means 24 hours when you are talking about time and dates. This should allow you to enter a valid time. Make sure you format the cell as a time. I removed the restriction to have the end time be greater than the start time.

I did some work on time sheets and the way I handled an over midnight shift was to use Date and Time. I can see you are making the assumption that if the end time is less than the start time, the end time is the next day.

Please Login or Register  to view this content.

These lines of code is meant to compensate for the one shortcoming that Tables do have. Tables "think" they always have at least one row of data even if there is no information in the row. I need to find the last actual row in the table so I can go down one row and add new data there. I am being over zealous in my implementation. The intersect checks to find the location of the first row in the table even if we move the table around.

Since the table is in a fixed location, I could change the if statement to read:

Please Login or Register  to view this content.

That statement is easier to implement and less likely to break.

I think the attached will work. You may have to change the time formats to fit your regional settings. I still not have looked at the quarterly sections.

First of all Happy New Year!

To answer the second question first: yes the last 4 weeks are automatically calculated and displayed.

As for the totals, I have a suspicion that the issue has to do with regional settings. On my computer =TEXT(@[Datum],"MMM") is JUN and JUL. Use the three letter identifier for the month and see if that works. It appears that March, May and October are the three months where the abbreviations are different.

If these corrections don't work, then we might be able to work around the issue by using a lookup that does the conversion. I did a quick search on the web, and found some code that can detect what the regional setting is. I may take this on as my next project which will be to come up with a "universal translator."

I just noticed that you had an attachment (I've had most of my first cup of coffee). I did change the months to the three-letter abbreviations and it seemed to work.

If I understand you correctly, you want to add the new data at the top instead of the bottom.

Yes, this can be done. I'll have to do some experiments to see how efficiently I can do it.

Here is a version that does it. I left in some of the old code and commented it out to show you what I replaced.

You will notice that there is a new module in the workbook. modTopOfTable. If there is a functionality that I think I might want to use repeatedly, I try to make it so that I can pass parameters to it. Then I select the module and use File > Export File to export the module. This action produces a BAS file with the name of the module. I put all of my BAS files in the same folder: I call it MyExcel. Then if I need that functionality again in another spreadsheet, I go to the editor and use File > Import File and select the module.

I needed something that will copy a range to the first row of the table. I also needed something to tell the program if it had to open up the first row. That's the AppendFlag. If it is true, I shift the table down by a row. If it is false (or left off since it is optional and has the default of false), the program adds the information to the first row in the table. This module assumes that the information you want to copy is in the same columns as in the table.

The module I created for this application (and can use in future applications) has the following code:

Please Login or Register  to view this content.

You will see in the ModMoveRecord Module how I use VB code to "feed" the parameters to this module.

Since I had to "break up" your ranges to copy them, I needed to make multiple calls. The first call inserts the new row and the subsequent calls merely add data to it.

The concept of saving modules can be used to build a library of functions. I will pass on two that I use all the time: one of them clears a table and the other is used to send emails using outlook. I will do these in a separate post.

Hi Dflak, thanks a lot again! I went straight to work with your new code yesterday evening and today, and i just got it to work perfectly and on MULTIPLE sheets :D. Quite proud of myself, this was my first real work with vba's.

So i had one issue with the new code, and that was that it didn't copy and paste the formulas of the cells down with the rest of the data. This resulted in a little triangle error on all the cells that got moved down (and had a formula in it), and also meant i couldn't change the data in the table anymore - at least not in a way where the formulas took note of the changes. I fixed this by replacing "xlPasteValues" by "xlPasteFormulas" in the TopOfTable module. This was complete beginners' luck haha, but it worked perfectly! Then i tried to apply the same two modules on a new sheet in the workbook (concerning income/bills), and i finally just got it to work after quite a bit of messing around. I first inserted a seperate TopOfTable module and MoveRecord for the new sheet, but i finally found out that the TopOfTable module can be a general module that can be referred to by MULTIPLE MoveRecords, without having to change anything in the TopOfTable module. This is wonderful!

I saved the .bas files in a folder on my pc and will certainly use this more often, and i'm feeling quite confident i can get it to work on my own. I definitely don't understand every line of code and every parameter, but i understand at least where to change the letters for the columns to apply to the right columns in a (new) sheet, and that seems sufficient for now . Thanks so much for all your help, i learned so much from you and this process. I will continue working on my ultimate administration workbook and i'll probably run into some excel mysteries later on haha, but for now i can go ahead. Thanks so much again, and i hope you will find some use in these awesome modules yourself as well!

EDIT: okay here i am again already with a question hehe, so it seems the formulas don't get copied and pasted to the new top row in my second sheet called "Inkomsten" (Income). I've checked the modules to see if i could spot the difference between the MoveRecord for the Time Manager sheet and this new sheet, but i couldn't find any, so i think the problem must lay somewhere else, but i got no clue where. Of course I tried filling in the formulas in the data entry row in the new Income sheet, but they just get erased after pressing the "Add to table" button. Am i missing something here? (Check the attached file below)

example forum4 (1).xlsb

You can also "stack" PasteSpecials

Please Login or Register  to view this content.

Excel didn't do this "right." If they did it right, the above lines of code should read Range("D2").PasteSpecial xlPasteValues + xlPasteFormats.

My initial idea was to tell you do clear contents piecemeal like I did. But I found a better solution online.

Please Login or Register  to view this content.

This only clears cells that have data and leaves cells with formulas alone. I don't use specialcells often and it was worthwhile learning this one. This is one reason I like to take on these projects: they force me to learn things.

The only issue with this is if you have NO cells with data, you get an error message. So, welcome to the On Error statement . Here it goes to an error handler which just tells you that you didn't enter data. It skips over the code since there is nothing to do.

In some cases you may want to ignore the error (I don't like code that does this and I try to avoid using errors as part of the logic). If you want to turn off error detection use

Please Login or Register  to view this content.

To resume error checking use

Please Login or Register  to view this content.

You seem to have a very good grip on VB coding. You obvious took to Option Explicit and using Excel Tables. If there are any "takeaways" these are the most important.

Trial and error is my favorite learning method.

Hey dflak, just noticed the notification that led to your private message hehe. Thanks for those tools, i'll keep them close whenever the moment comes when i start creating my own code . I'm not getting any confirmation that i managed to sent my message back to you, so i'll keep it here. What i wanted to say is, first of your trips sounds amazing, but let me know if you need any tips or have any questions in general about your stay here, happy to be of help!

To not run the risk of this getting moderated for being completely off-topic: I've been trying to get the autofill function to work on the new data row, but couldn't, so after a bit of googling i moved the 'new-data-row' right under the headers of the Table (so it becomes the first row of the Table) and now the autofill function works. But it triggers faster in somce cells than it does in others. For example in the sheet "Inkomsten" in the column "product" the word "photo" never seems to trigger the autofill, while the word "john" appears straight away as a suggestion when typing in just the "j", and in the column "client" the word photo doesn't trigger the autofill either (also after filling in "photo" in a couple of prior examples). I know there's the option to turn on or off autotrigger in the 'advanced' settings under 'file', and this is turned on of course, but is there maybe another characteristic that determines the sensitivity of the autofill function, or another possible reason why some words and cells behave differently? (don't make a life's work out of this question, would be nice to get it a bit more sensitive but not a big deal obviously :P) NEVER MIND THIS (got bigger issues now)