Advanced Calculations and If statements for Textbox

[paxile2k]

I am creating a userform that will input information on to spreadsheet, all boxes/comboboxes/labels, etc have been completed/asignd and set.


I have a combobox that has values of:
Hourly
Weekly
Bi-Weekly
Monthly
Yearly

I have txtMGI which will be set to calculate based on each option
for Hourly it will take textbox a * b * 52 / 12
the formula i am using is txbMGI.Value = ((txtboxA.value * txtboxb)*52)/12
This is tied to a If statement also, but i am not sure if the Else value is needed to complete the if statement.
Here is a code sample

Please Login or Register  to view this content.

I am stumped on the else part and if it is needed, because i am going to have to add other if conditions to have it calculate, and so far just running the form, the txtMGI does not even update to the correct value.

Any Help would be appreciated.

[shg]

Try this:

Please Login or Register  to view this content.

[paxile2k]

Try this:

Please Login or Register  to view this content.

I tried the code, but txtMGI did not update. Is there something that i am missing in the code to have the txtMGI auto update?

[shg]

This code in in the form module? Have you tried setting a breakpoint and stepping through the code?

[paxile2k]

This code in in the form module? Have you tried setting a breakpoint and stepping through the code?

Yes the code is in the form module named frmInput, I am not getting any errors, the box is not updating once txtHRRT and txtHRWK are updated, txtMGI should go the calculation based on the if condition..but nothing happens. Could it be an incorrect Sub i am using to have the textbox update?

[shg]

Set a breakpoint in the code, then step thtrough and see what's happening.

See http://www.cpearson.com/excel/Debug.htm.

[romperstomper]

You have your code in the change event of that textbox, so it won't be triggered unless you change the textbox's value. Really you need it in a separate sub that you can call from the exit event of the combobox and the two input textboxes.

[paxile2k]

You have your code in the change event of that textbox, so it won't be triggered unless you change the textbox's value. Really you need it in a separate sub that you can call from the exit event of the combobox and the two input textboxes.

Would it be something like this?

Please Login or Register  to view this content.

If so, i have to click around then it updates, is this correct? If it can be trigged by a Tab or Enter it would be preferred

[romperstomper]

More like:

Please Login or Register  to view this content.

[paxile2k]

More like:

Please Login or Register  to view this content.

Thankyou for the code, it is updating, but stll a couple of extra clicks and it displays a 0 (which that will be a whole other issue on formating the boxes )value when exiting the other txt boxes, which is fine, just as long it does update , would the same Sub UpdateMGI apply if say cboPAYF were for "Weekly" which would be val(txtWK.value)*52, or that could simply be solved by just adding a few more if statments in the sub?

[romperstomper]

You can amend the update sub to perform other calculations as required.

[paxile2k]

You can amend the update sub to perform other calculations as required.

Thankyou again, i have added another if condition but it seems that the txtMGI is still not updating after exiting the appropriate box and requires and couple of clicks, but it does update so still progress has been made which i cannot thankyou enough, a question i had is the cboPAYF & other exit subs needed for proper updating?

[romperstomper]

The MGI value depends on three other controls, therefore it makes sense to monitor the exit event of all three controls. The textbox should get updated whenever you leave any of the three controls, assuming the combobox has the relevant value.

[paxile2k]

The MGI value depends on three other controls, therefore it makes sense to monitor the exit event of all three controls. The textbox should get updated whenever you leave any of the three controls, assuming the combobox has the relevant value.

Thankyou again, you are a life saver. I am still having some issues with it updating, i have added the other conditions but now it does not seem to work at all.

Here is the updated code:

Please Login or Register  to view this content.

I hope i am not missing something, There is nothing on the properties of the combo/text boxes that needs to be changed such as TabKeyBehaviour/EnterKeyBehaviour for this to work fluidly?

[romperstomper]

Your conditions are overruling each other:

Please Login or Register  to view this content.

Note: I corrected what appeared to be a misspelling of "Monthly"

[paxile2k]

Your conditions are overruling each other:

Please Login or Register  to view this content.

Note: I corrected what appeared to be a misspelling of "Monthly"

Now That i did not know, i have another sub that was setup the previous way and working with no issues, thankyou for catching my spelling error, not the first time i have done that lol. After updating the code, i still have the exit problem with updating, but when going back to cboPAYF and exiting it does update, I am thinking its based on the exit order in the code. Should i change the order for the exit subs? or Is there another way around that to have it calculate after exiting the appropriate box or does it have to be cycled to get the exit to work?

[romperstomper]

No, it should update whenever you exit any of the controls, assuming you have the control names correct.

[paxile2k]

No, it should update whenever you exit any of the controls, assuming you have the control names correct.

Which i do, after looking over all of them from your advisement on the incorrect spelling. It seems to only trigger when exiting the cboPAYF box. As apposed to using multiple triggers i have changed to using a command button to do the calculation and update the box. I can't tell you how appreciative of your assistance i am. Thankyou again for your help. now for the fun task of formatting the textboxes to $ and %