Calculating field on a VBA From

**Gtrhero** · 06-20-2007, 10:48 PM

Hello,
I have run into a problem I have not found a solution for surprisingly. I need to calculate several fields on the form prior to saving it and thus exporting the data to the worksheet. All I can find is how to calculate the field in a worksheet and then reference it with VB code on the form. Is what I want to do possible?

If I run into many more problems I may want to shop this project out as my schedule barely allows for the work I have let alone attempting to learn VBA on the fly.

If anyone is interested I can post the project so far along with the detail of what I want it do in the end.

**Steel Monkey** · 06-20-2007, 11:12 PM

If you could post a sample of what you are trying to do that would bemost helpful

**Gtrhero** · 06-21-2007, 12:53 AM

OK here is the file I have, so far, please reply with any specific questions, this is being done in excel 2007 in case that matters.

If you open the EOS form in the VB editor I want to calculate the total checks field, total drop, and the 3 field under the variance section. I want these to calculate autmatically just as they would in a worksheet then once the finish button is clicked export them to the worksheet "Shift counts", the export is completed and I need to write code to clear the form before this portion of the application is finished.

**Steel Monkey** · 06-21-2007, 01:09 AM

Sorry that counts me out as i dont have 2007. Hopefully someone else will be able to help!

**royUK** · 06-21-2007, 03:44 AM

the quickest way to clear the form is to simply unload then load it.

The writing & clearing could be done in a loop, but your method of naming the objects is a bit obscure. The loop would certainly speed this up.

The calculations can easily be done within the VBA, I do this with many applications that I write.

Are you looking to total all the ckn textboxes, etc?

**Gtrhero** · 06-21-2007, 01:56 PM

Yes I amoung others I need to total all the ckn fields, the drop1 - 6 fields, etc.

As I stated I am learning as I go with all of this obviously not the best practice, so I am sure that I should make many errors and have many questions, I like working with excel and making things work but as I stated my time is very tight so I may have to source this out.

**royUK** · 06-21-2007, 02:45 PM

I will have a look at it later for you. It would need some error checking, eg for empty textboxes.

**royUK** · 06-21-2007, 03:11 PM

I'm not sure what you need to do with the toatal, but I have added a temporary button and some code that totals the check amounts

**Gtrhero** · 06-21-2007, 07:15 PM

royUK,

Sorta, I need it to calculate to the totalck field and to do so on the fly as I add each new check amount. To clarify the calculation for this form:

Total Drop = (Drop1:Drop6)

Enter this amount for EOS = (Starting Cash + Total Coin + Total Drop + Total Check) these of the label names on the form

Total Check = (Check $1:Check $24)

Variance Currency = (Total Coin + Total Drop)-Phoenix EOS Currency))-Starting Cash

Variance Check = (Total Check - Phoenix EOS Check)

Total Variance = (Variance Currency + Variance Check)

I somehow need to make all of this either calculate on the fly or I suppose it could work with a command button that was clicked prior to the finish command. I hope that makes what I need much clearer, as it was pretty vague before. Any help or steering me toward the right direction is much appreciated.