Recalculate cell values when change made in same row?

**testerxxx** · 2015-02-22, 02:14 AM

First off, moderators please forgive me. I posted this in formulas when it should have been in vba programming. So its here now...DRL TS FC1.22.xlsm

I've googled for an answer to this with no luck so here i am.

My workbook is my time sheet for work. Each row of data is a job that I did. The last 2 cells in each row take the data from the rest of the row to calculate the total cost of the work done and what I made on the job. All the data is input and calculated through a userform. That all works great.

What I am trying to do is if I made a typo in the userform and output the wrong totals how can I make it so any change in value in a row will recalculate the last 2 cells in the same row?

The data in columns F-J are used in the script to calculate the values in K and L. So for example if in row 10 I need to change the hours worked from 8 to 10 I need the values in K10 and L10 to recalculate.

As it is now, all the data and calculations are done with the user form when I click the calculate button.

I attached the latest version of my workbook. It does use DTpicker so if you need to run it to see anything it will error out. I am going to look into another way to capture the date and/or input the date so if you have any suggestions I open. This is just a side question lol.

DRL TS FC1.22.xlsm

Thanks

Mike

**p45cal** · 2015-02-22, 01:41 PM

Since the calculations seem to be quite involved and might be difficult to put on the sheet as a formula, then I would consider the pros and cons of having it so that, say, a double-click on a line you wanted to amend (a sheet double-click event) some checks would be made as to which cell was double-clicked on, and if it's on an existing data row then fire up the very same userform (the one which adds a line) but have the fields on that userform all filled out with values on the sheet taken from the same row. Some changes to the calculate_charge code would be needed, such as setting a boolean variable to act as a flag saying whether the userform is being used to add a row or to amend a row, and so change the way lrow is determined.
This approach would mean that the editing/amending process would be very familiar to you.

**testerxxx** · 2015-02-23, 02:45 AM

That sounds like a great way to do this. If you look at the workbook I attached and are able to use the userform you will see that is how basically it works already. Jobs that I do will usually go for hours or days and will produce multiple billing tickets that will be exactly the same except for the ticket number. The way it works now is when I click the add ticket button it finds the last filled row and copies all the data into the userform, except the ticket number gets the next sequential number. So I agree with your idea of bringing the row to amend into the user form, me changing the error, then returning the new data into the same row.

Now the tricky part...How? lol. I'm really just a novice at this vba stuff. To add a new row I click the add ticket button. If I double click on a line I call the same sub as clicking on the add ticket button? And in there the new changes might be a if/then checking to see if it were a double click or not. If yes then do this else do the normal. Or a double click could call up virtually the exact same sub except for pin-pointing the row and replacing that same row?

Also if its not too much trouble I would need some help with the actual vba coding to do this. Mainly the pin-pointing and replacing. The rest would just be reusing or rehashing the exact same code I have now?

Thanks for looking at this and helping me out. Thanks also for any continued help.

**p45cal** · 2015-02-24, 07:04 PM

The attached is only a start and needs lots of debugging.
I've added some code to the ThisWorkbook code module and altered code in other places (I've tried to remember to add a comment 'pd to lines of code I've altered so that you can do a search for them.
Double-click a row of data.
Things that will go wrong:

If you double-click on a row and amend the date to one outside the current range of dates, the cell K2 may not update properly because it assumes the rows are in date order so only looks at the first and last dates.
You have a $ symbol appearing in the HOURS textbox, this causes an error when calculations using it are carried out.
If you amend row 30 you have a check which does something.. you probably don't always want what happens to happen!
Double-clicking on a blank row.
Lots of other things.

Anyway, it should get you started.

**testerxxx** · 2015-02-25, 09:52 PM

Hey p45cal, thanks for the work up on my workbook. I haven't had time to really dig into the code and what not, but I did do a quick test run. The initial run everything was awesome.

The only problem I had was double clicking on an empty row. No value for the code. I'm thinking that a simple if value = "" (or 0 or whatever) then exit sub at the start might fix that.

Changing the dates in K2 is actually a non issue. Me having to change a date that is outside the first row date or last row date shouldn't ever happen because of the way I fill out the sheet. The most important column, besides the hours, bbls...that determines the pay, is the Add Ticket column. This is important only for making it easier for my boss to go through and check that all pay tickets are on my time sheet and that I've filled it out right (checking how the actual ticket is filled out to how the time sheet is filled out). K2 dates are mostly for the export to pdf naming anyway. Again, you are right though.

As far as the $ symbot appearing in the HOURS textbox, im not seeing this happening. That has never been an issue and on brief rechecking of the userform being used I didnt see it nor when I actually amended some of the rows that use hours to calculate pay did i have any calculation errors. Not saying that you didnt see anything, I just haven't had any issues with that yet. I will re-check that.

Row 30...ugh, yes, big problem. Lol. That is my auto new sheet when the last row is filled in code. Again I'm thinking maybe a if/then exit sub.

****UPDATE****
The if/then exit sub works great for row 30.

One thing I am curious about though, you totally commented out the code that updates the hyperlinks to my ticket pics when you click on a sheet and make it active. That was there because the pics is usually the last thing I do after and is normally after the sheet is full. It was the easiest way I found to create the links. Maybe you commented that out because you didn't have the pics and it was slowing things down?

Other than that things are great. You set me off in the right direction and left me work to do on my own. Thanks for that. I prefer to do my own stuff as much as possible and not be handed something fully functional, well unless of course I was paying for it to be done lol.

So I'm off to fix things.

Again thanks for you help and time. Much appreciated.

Mike

**p45cal** · 2015-02-25, 10:12 PM