code won't loop

[Joanne]

I am using the following code on a simple user form to calculate some
customer charges. I am taking the value from the txt boxes to get the
calcs I need. It works well one time thru, but when I click the "Next"
button, it clears the txt boxes as expected, sets focust on txtCustMins,
but when I input into the txtCustMins, I don't get any calculations as I
tab thru.
Clearly I have something wrong here but I am pressed to know what it is.
I would appreciate any advice you have to offer to get this going.

Thanks a bunch
Joanne

Private Sub cmdDone_Click()
txtCustCharges.Value = ""
txtCustMins.Value = ""
txtNumStops.Value = ""
txtTruckerTime.Value = ""
txtTotalMinutes.Value = ""
Application.Quit
End Sub

Private Sub cmdNext_Click()
txtCustCharges.Value = ""
txtCustMins.Value = ""
txtCustMins.SetFocus
End Sub

Private Sub txtNumStops_AfterUpdate()
txtCustCharges.Value = (txtTotalMinutes.Value / txtNumStops.Value +
txtCustMins.Value) * 1.17
End Sub

Private Sub txtTruckerTime_Change()
txtTotalMinutes.Value = txtTruckerTime * 60
End Sub

 

[Dave Peterson]

You didn't post any _change or _beforeupdate or _afterupdate event for changes
to txtCustMins.

Does that mean you don't have that code--or just forgot to paste it into the
message?

ps. Application.quit seems pretty scary to me. Are you sure that the users
want to quit excel when they close your form?

 

[Joanne]

Dave
Thanks for your reply
I didn't post any _change or _beforeupdate or _afterupdate event for
changes to txtCustMins because, honestly, I didn't know that that was
what I needed to do to get the results I need. I am not as sure of Excel
VBA as I am of Access and Word versions of VBA. Actually, the 'change'
event in Excel is new to me and confuses me quite a bit.

So I am thinking that I should put code into _afterupdate event of
txtCustMins that would refire the calculation in
txtNumStops_AfterUpdate. I tried calling txtNumStops_AfterUpdate from
the txtCustMins_change event but that called the debugger instantly. I
am a bit confused here and would appreciate a little help please.


ps. Application.quit seems pretty scary to me. Are you sure that the
users want to quit excel when they close your form?

--- would it be better to just unload the user form and close the
workbook? I understand what you are saying -- the quit action could
interfere with other excel work that they may have open and active,
right?

Thanks for your interest
Joanne

 

[Joanne]

Dave
I've been messing around with this and still can't get a go.

When I click on the 'Next' Button it deletes the values in txtCustMins
and txtCustCharges and sets focus on txtCustMins. Problem is my
calculation for CustCharges is in the change event of txtCustMins so
after I get my calc and want to do another one, I click 'next' it
deletes the value in txtCustMins, causing the txtCusMins_change event to
fire and it stops the program I think because there are no values in the
inputs to calculate now. So I commented out the line
txtCustCharges.value = "", but I am still getting a stop in the program.
I really don't know what else to try.
Thanks for your consideration of my little problem
Joanne

 

[Dave Peterson]

Instead of processing the values based on when the user leaves a textbox, maybe
it would be easier to add an "Ok/Process" button to your userform. As a user,
I'd rather see that so that I could avoid typos.

Then you could validate all the input before you try to do arithmetic with it.
I'd add a label to the form with big bold red letters with any error message.

And I have no idea why you want to even close the workbook after dismissing the
userform. That seems scary to me, too. If the user screwed up really badly,
are you going to save the workbook automatically?

How would you know the user's intent?

 

[Joanne]

You're right, the process button is so much cleaner a method to do this
job - none of those pesky little problems. Thank you so much for the
help.
Now I am tring to round the result of the calculation to the nearest 5.
This is what I have come up with based on what I found on the internet.

round(txtCustCharges.Value * 2, 0) / 2
but it keeps giving me a compile error Expected =. I have no clue how to
adapt this to fix the error. Could you help me out once again please?
Thanks
Joanne

 

[Dave Peterson]

Do you really mean to round to the nearest 5?

Dim myVal as Variant 'could be a number or string
if isnumeric(me.txtcustcharges.value) then
myval = round(me.txtcustcharges.value / 5, 0) * 5
else
'what should happen if the value isn't numeric?
myVal = "Not a number!"
end if

But I'm not sure what you really want to have equal to that rounded number.

ps. Round was added to VBA in xl2k. If you (or any of the users) are using
xl97, you'll want to use application.round--and that behaves slightly
differently than VBA's Round.

 

[Joanne]

Thank you again Dave. and you are right again, as I look at the code I
can see from what I learned on the net that I want to round to 2, not 5,
which I think I understand means 1/2, so rounding to the nearest 5 will
be the result
Thanks for helping me finish out this little app.
You guys really are the greatest
Joanne