Recalculate cell values when change made in same row?

First off, moderators please forgive me. I posted this in formulas when it should have been in vba programming. So its here now...View attachment 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.

View attachment DRL TS FC1.22.xlsm

Thanks

Mike

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.

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.

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.

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

testerxxx said:

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.

Click to expand...

Yes, the boolean variable AMEND might be useful in the same way as I've used it elsewhere.

testerxxx said:

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.

Click to expand...

So what version of Excel are you using?

testerxxx said:

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.

Click to expand...

Using AMEND?

testerxxx said:

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?

Click to expand...

Exactly; I should have put them back.

testerxxx said:

Other than that things are great.

Click to expand...

Excellent!

Im using the latest version of Excel. The one that come with office 365.

And yes, the row 30 thing works with the amend. Although some where in there I have code that takes me back to the current ts sheet when I change something on another sheet. Its a little annoying now lol. I really need to find it.

As far as clicking on the blank rows I'm thinking I have 3 options. One of which came from your suggestion of clicking to amend. Option one is to just do nothing if you click on a blank cell. Option 2, use the code that finds the last empty row on the sheet in the amend code to set the sh.range. That way clicking on empty cells would do nothing. I guess that would kill my Option 1. Option 3, which I'm gonna try first is to make the userform pop up in non-amend form. I really like that idea over having to click my one Add Ticket button in one specific place. Its a little annoying when you are at the bottom of the sheet and have to scroll up to click that button.

Another thing I am doing with the sh.range is making it only be clickable in the numbers columns. The date, ticket number, truck, trailer, company and product have nothing to do with the equations that output the billed amount and employee take. I can change those values right on the sheet.

One other thing im going to look into with the double-click is when the userform pops up to have whichever cell I double clicked be on focus and highlighted. The reason being I will double-click on the cell I need to amend and be able to just type the new value in without having to tab to it or click on it. Lazy? Hell ya. lol. If you have any knowledge or ideas on that I'd love to hear them.

Soooooo a nice little exciting update.

Thanks to p45cal's help I've made great advances and improvements in my time sheet workbook.

I can now fix any errors in rows that have already been done. I can fix row 30 with no problems. I found the code that put me back on the Current TS sheet and fixed it. I can double click on an empty cell (in the range I have set to be able to be amended) and my userform to add a new ticket comes up. How do I know its to add a new ticket? well because when I'm in amend mode I have made the userform Calculate button change to Amend.

I'm still having issues with my ticket column text formatting like there is a hyperlink to it when one hasnt been set up yet. I even have in the code to set it right when there is no link but it doesnt seem to work right after the page is messed up. I have to look into that more.

I still need to make it set focus and highlight the textbox in the userform that I double click on the sheet to amend. Just a lazy thing but I want it. lol

One other big thing that needs my attention is if for some strange reason I clear out rows in the middle of my sheet, how to get those filled back in. I can double click in a cell and bring up the userform but it puts the data in the last row (even if row 30 is full...ugh messes everything up) instead of in the row I double clicked on. im thinking on that maybe a if amend.row < lrow then...blah blah blah...i'll have to check that out.

Other than that just some small stuff and cleaning up a bit.

Thanks p45cal for your help...im still taking ideas on things ive mentioned above if i dont get to them first

mike