Do Until - If-then problem

[jeff]

Here’s what I’m trying to do. The user fills out UserForm6 that
already has a number in TextBox22 when it comes up. This number is
from a previous form. There are 16 TextBoxes in UserForm6, and the
user will enter numbers in some or all of these boxes. The idea is to
have the sum of these 16 boxes equal the number that was already in
TextBox22.

I have code that will sum up the 16 boxes, and put that total in
TextBox23. I want the numbers from boxes 22 and 23 to equal. If they
don’t when the user clicks OK, then the user will be told they have to
match. Right now, I have a MsgBox come up telling the user they don’t
match.

At that point I want the user to be able to change the numbers in
those 16 boxes, and try it again. Then the 2 TextBoxes should be
compared again to see if they match totals.

I’ve tried various If then, and Do Until codes, but the best I can
come up with is what turns into an endless loop, or it will not test
the condition again. I can’t get it to re-calculate the total from the
boxes, and test against TextBox22 again.

Here’s what I have so far:


***this attempt calculates the sum of the 16 TextBoxes, and shows that
number in TextBox23. And, it will recalculate the TextBox23 correctly
after each change by the user. So far, so good. However, when the
number finally does match, it still comes up with the MsgBox and
doesn’t seem to re-test the IF-Then condition again. No matter what,
the MsgBox will come up. Also, if TextBoxes 22 & 23 DO match on the
1st try, it still comes up with the MsgBox.

Private Sub CommandButton1_Click()
'OK
ChkTotal 'sub in same UF that sums the total of the 16 TextBoxes
If TextBox23.Value = TextBox22.Value Then
AllocateDeposit 'Sub in same UF This is the final routine - done
only after TextBoxes 22 & 23 match.
Else
MsgBox "Total allocations must equal the Deposit value"
End If
End Sub


‘***This was an alternative solution I was working on. Upon clicking
OK, the MsgBox comes up no matter what the user enters in the 16
boxes. It’s an endless loop, and I have to Ctrl Break to stop the
macro.

Private Sub CommandButton1_Click()
‘OK

Do Until TextBox23.Value = TextBox22.Value
ChkTotal 'module below
If TextBox23.Value = TextBox22.Value Then
AllocateDeposit 'module below
Else
MsgBox "Total allocations must equal the Deposit value"
End If
Loop

End Sub

Am I close with either attempt? If anyone can help, I’d appreciate
it.
Thanks,
jeff

 

[Jim Cone]

jeff,
Message boxes have several alternative return values depending on the buttons you choose to display in the MsgBox.
(MsgBox is a function)
This approach may be close to what you need...
'---
lngAnswer = MsgBox("Total allocations must equal the Deposit value. ", vbExclamation + vbRetryCancel, "Jeff Did It")
If lngAnswer = vbRetry Then
'start again?
Else
'exit sub?
End If
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(30+ different ways to sort in excel)

..
..
..

"jeff" <[email protected]>
wrote in message
Here’s what I’m trying to do. The user fills out UserForm6 that
already has a number in TextBox22 when it comes up. This number is
from a previous form. There are 16 TextBoxes in UserForm6, and the
user will enter numbers in some or all of these boxes. The idea is to
have the sum of these 16 boxes equal the number that was already in
TextBox22.

I have code that will sum up the 16 boxes, and put that total in
TextBox23. I want the numbers from boxes 22 and 23 to equal. If they
don’t when the user clicks OK, then the user will be told they have to
match. Right now, I have a MsgBox come up telling the user they don’t
match.

At that point I want the user to be able to change the numbers in
those 16 boxes, and try it again. Then the 2 TextBoxes should be
compared again to see if they match totals.

I’ve tried various If then, and Do Until codes, but the best I can
come up with is what turns into an endless loop, or it will not test
the condition again. I can’t get it to re-calculate the total from the
boxes, and test against TextBox22 again.

Here’s what I have so far:


***this attempt calculates the sum of the 16 TextBoxes, and shows that
number in TextBox23. And, it will recalculate the TextBox23 correctly
after each change by the user. So far, so good. However, when the
number finally does match, it still comes up with the MsgBox and
doesn’t seem to re-test the IF-Then condition again. No matter what,
the MsgBox will come up. Also, if TextBoxes 22 & 23 DO match on the
1st try, it still comes up with the MsgBox.

Private Sub CommandButton1_Click()
'OK
ChkTotal 'sub in same UF that sums the total of the 16 TextBoxes
If TextBox23.Value = TextBox22.Value Then
AllocateDeposit 'Sub in same UF This is the final routine - done
only after TextBoxes 22 & 23 match.
Else
MsgBox "Total allocations must equal the Deposit value"
End If
End Sub


‘***This was an alternative solution I was working on. Upon clicking
OK, the MsgBox comes up no matter what the user enters in the 16
boxes. It’s an endless loop, and I have to Ctrl Break to stop the
macro.

Private Sub CommandButton1_Click()
‘OK

Do Until TextBox23.Value = TextBox22.Value
ChkTotal 'module below
If TextBox23.Value = TextBox22.Value Then
AllocateDeposit 'module below
Else
MsgBox "Total allocations must equal the Deposit value"
End If
Loop

End Sub

Am I close with either attempt? If anyone can help, I’d appreciate
it.
Thanks,
jeff

 

[Clif McIrvin]

Here’s what I’m trying to do. The user fills out UserForm6 that
already has a number in TextBox22 when it comes up. This number is
from a previous form. There are 16 TextBoxes in UserForm6, and the
user will enter numbers in some or all of these boxes. The idea is to
have the sum of these 16 boxes equal the number that was already in
TextBox22.

<code ... snipped>

Am I close with either attempt? If anyone can help, I’d appreciate
it.
Thanks,
jeff


***

Until someone with more experience chimes in here, my guess is that
something about TextBox23.Value = TextBox22.Value is never evaluating
True.

Have you tried setting a breakpoint at the If statement and using the
Locals window to compare the two values yourself?

Sonething that might work for you is:

Dim curBox23 as Currency
Dim curBox22 as Currency

(your code)

curBox22 = TextBox22.Value
curBox23 = TextBox23.Value

If (curBox23 - curBox22) < 0.001 Then

Just a thought. From what I saw, both of your attempts should do what
you want.

 

[Don Guillett Excel MVP]

Here s what I m trying to do. The user fills out UserForm6 that
already has a number in TextBox22 when it comes up. This number is
from a previous form. There are 16 TextBoxes in UserForm6, and the
user will enter numbers in some or all of these boxes. The idea is to
have the sum of these 16 boxes equal the number that was already in
TextBox22.

<code ... snipped>

Am I close with either attempt? If anyone can help, I d appreciate
it.
Thanks,
jeff

***

Until someone with more experience chimes in here, my guess is that
something about TextBox23.Value = TextBox22.Value is never evaluating
True.

Have you tried setting a breakpoint at the If statement and using the
Locals window to compare the two values yourself?

Sonething that might work for you is:

Dim curBox23 as Currency
Dim curBox22 as Currency

(your code)

curBox22 = TextBox22.Value
curBox23 = TextBox23.Value

If (curBox23 - curBox22) < 0.001 Then

Just a thought.  From what I saw, both of your attempts should do what
you want.

I didn't look at this closely but a modification of this idea should
do it.


Sub stoploop()
mr = 2
Do Until mv = Range("c1")
mv = mv + Cells(mr, "c")
MsgBox mv
mr = mr + 1
Loop
MsgBox mv
End Sub

 

[jeff]

I didn't look at this closely but a modification of this idea should
do it.

Sub stoploop()
mr = 2
Do Until mv = Range("c1")
mv = mv + Cells(mr, "c")
MsgBox mv
mr = mr + 1
Loop
MsgBox mv
End Sub- Hide quoted text -

- Show quoted text -

I'll try this again. I just replied, but it didn't take
I apprecitate the replies I got. But, I just don't understand them
enough to know how to use the replies with what I have already. I'm
sure non of the suggestions were meant to replace my coding. But, as I
read them, I don't see how they could connect. Obviously I don't know
enough about the subject to see it.
Just to be clear on what I'm trying to do.
I want the user to fill in some or all of the 16 TextBoxes. After
that, the total of those will be added, and placed in TextBox 23. What
I want is for TextBox 23 to be compared to TextBox 22. If the values
are exactly the same, then it runs AllocateDeposit module. If they DO
NOT equal, then I want that message box to come up again, then the
user tries it again (by changing some of the entries in those 16
TextBoxes. Then he clicks OK again, and TextBoxes 22 & 23 are compared
gain.... this goes on over and over until TextBoxes 22 & 23 are
equal.
If someone could please enlighten me, I would appreciate it. Thanks
for the replies so far.
jeff

 

[Clif McIrvin]

I'll try this again. I just replied, but it didn't take
I apprecitate the replies I got. But, I just don't understand them
enough to know how to use the replies with what I have already. I'm
sure non of the suggestions were meant to replace my coding. But, as I
read them, I don't see how they could connect. Obviously I don't know
enough about the subject to see it.
Just to be clear on what I'm trying to do.
I want the user to fill in some or all of the 16 TextBoxes. After
that, the total of those will be added, and placed in TextBox 23. What
I want is for TextBox 23 to be compared to TextBox 22. If the values
are exactly the same, then it runs AllocateDeposit module. If they DO
NOT equal, then I want that message box to come up again, then the
user tries it again (by changing some of the entries in those 16
TextBoxes. Then he clicks OK again, and TextBoxes 22 & 23 are compared
gain.... this goes on over and over until TextBoxes 22 & 23 are
equal.
If someone could please enlighten me, I would appreciate it. Thanks
for the replies so far.
jeff


*******

Jeff, I think I understand what you want to happen. From your OP:

***this attempt calculates the sum of the 16 TextBoxes, and shows that
number in TextBox23. And, it will recalculate the TextBox23 correctly
after each change by the user. So far, so good.

--> Sounds like your Click event doesn't need to care what the user has
done -- it simply tests your final condition and either displays the
message box and does nothing, or it executes the AllocateDeposit
procedure. (Question: how are you "proceeding to the next step" after
the test condition is met? Is that accomplished from within your
AllocateDeposit procedure? If so, you could be running into a problem
because you have not yet exited from the click event when you call
AllocateDeposit. But, that isn't the question I'm reading.)

However, when the
number finally does match, it still comes up with the MsgBox and
doesn't seem to re-test the IF-Then condition again. No matter what,
the MsgBox will come up.

Also, if TextBoxes 22 & 23 DO match on the
1st try, it still comes up with the MsgBox.

--> I've never attempted quite what you're describing, so my suggestion
is a guess at what the trouble might be. First off, set a breakpoint
[ Debug | Toggle Breakpoint ] at your IF statement and use the
immediate and locals windows to examine the contents of TextBox22.Value
and TextBox23.Value. [The Immediate and Locals windows are in the View
menu in the VBE window.]

Try changing your click event as follows and see if this helps:

Private Sub CommandButton1_Click() 'OK
Dim curBox23 As Currency
Dim curBox22 As Currency

ChkTotal 'sub in same UF
' that sums the total of the 16 TextBoxes

curBox22 = TextBox22.Value
curBox23 = TextBox23.Value

If (curBox23 - curBox22) < 0.0001 Then
'If TextBox23.Value = TextBox22.Value Then
AllocateDeposit 'Sub in same UF This final routine
'called only after TextBoxes 22 & 23 match.
Else
MsgBox "Total allocations must equal the Deposit value"
End If
End Sub


By converting the values to currency data types and then checking to see
if the difference between them is "small", I'm trying to avoid any
possible rounding errors or formating differences that could be causing
your test for equality to fail.

Post back if this doesn't clarify things for you.

HTH!