VBA - UserForm with multiples TextBoxes in infinite calculation loop

Question

I have an UserForm with 5 TextBoxes, and each time one is updated I recalculate the others.

But how do I avoid a "infinite loop" as the first will recalculate second which will recalculate the first and so on...

Here is a part of my UserForm's code, only the TextBox_Change(s) :

Private Sub TxtNewVal_Change()
If InStr(1, Me.TxtNewVal.Value, ",") Then Me.TxtNewVal.Value = Val(Replace(Me.TxtNewVal.Value, ",", "."))

'recalculate values
If Right(Me.TxtNewVal.Value, 1) <> "." Then
    Me.TxtValEUR.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxEUR.Value)
    Me.TxtValUSD.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxUSD.Value)
End If
End Sub


Private Sub TxtTxEUR_Change()
If InStr(1, Me.TxtTxEUR.Value, ",") Then Me.TxtTxEUR.Value = Val(Replace(Me.TxtTxEUR.Value, ",", "."))
If Val(Me.TxtTxEUR.Value) = 0 Then Me.TxtTxEUR.Value = TxEur
'recalculate values =
If Right(Me.TxtTxEUR.Value, 1) <> "." Then Me.TxtValEUR.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxEUR.Value)
'Me.TxtValUSD.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxUSD.Value)
End Sub


Private Sub TxtTxUSD_Change()
If InStr(1, Me.TxtTxUSD.Value, ",") Then Me.TxtTxUSD.Value = Val(Replace(Me.TxtTxUSD.Value, ",", "."))
If Val(Me.TxtTxUSD.Value) = 0 Then Me.TxtTxUSD.Value = TxUsd
'recalculate values =
'Me.TxtValEUR.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxEUR.Value)
If Right(Me.TxtTxUSD.Value, 1) <> "." Then Me.TxtValUSD.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxUSD.Value)
End Sub


Private Sub TxtValEUR_Change()
    If InStr(1, Me.TxtValEUR.Value, ",") Then Me.TxtValEUR.Value = Val(Replace(Me.TxtValEUR.Value, ",", "."))
'recalculate values =
'Me.TxtValEUR.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxEUR.Value)

If Val(Me.TxtTxEUR.Value) <> 0 And Right(Me.TxtValEUR.Value, 1) <> "." Then _
    Me.TxtNewVal.Value = Val(Me.TxtValEUR.Value) / Val(Me.TxtTxEUR.Value)
'Me.TxtValUSD.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxUSD.Value)
End Sub



Private Sub TxtValUSD_Change()
    If InStr(1, Me.TxtValUSD.Value, ",") Then Me.TxtValUSD.Value = Val(Replace(Me.TxtValUSD.Value, ",", "."))
'recalculate values =
If Val(Me.TxtTxUSD.Value) <> 0 And Right(Me.TxtValUSD.Value, 1) <> "." Then _
    Me.TxtNewVal.Value = Val(Me.TxtValUSD.Value) / Val(Me.TxtTxUSD.Value)
'Me.TxtValEUR.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxEUR.Value)
'Me.TxtValUSD.Value = Val(Me.TxtNewVal.Value) * Val(Me.TxtTxUSD.Value)
End Sub

I thought about it but I didn't know how do it (even less properly...) and I was in a rush to catch train so I didn't extend that thought in OP... — R3uK
– R3uK, Commented Jun 1, 2015 at 16:28

to StackOverflow · Accepted Answer · 2015-06-01 16:16:08Z

1

Something like this:

Private bIsUpdating As Boolean


Private Sub TxtNewVal_Change()
    If bIsUpdating Then Exit Sub
    bIsUpdating = True
    ... update other text boxes
    bIsUpdating = False
End Sub

Private Sub TxtValEUR_Change()
    If bIsUpdating Then Exit Sub
    bIsUpdating = True
    ... update other text boxes
    bIsUpdating = False
End Sub

... etc ...

answered Jun 1, 2015 at 16:16

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R3uK Over a year ago

Ah nice! I didn't think to use the boolean like this! I tilted with your answer that the subs were running parallely! That should work just fine! I'll let you know and validate ASAP! Thx very much

R3uK Over a year ago

Exactly what I needed, I just couldn't figure out how to use the boolean before I read your answer! Thx so much!

snb · Accepted Answer · 2015-06-01 16:39:45Z

1

I'd use the Tag property of a control

Private Sub TxtNewVal_Change()
   TxtNewVal.Value = Val(Replace(Me.TxtNewVal.Value, ",", "."))

   'recalculate values
    txtnewval.tag=" "
    TxtValEUR.Value = Val(TxtNewVal) * Val(TxtTxEUR)
    TxtValUSD.Value = Val(TxtNewVal) * Val(TxtTxUSD)
    txtnewval.tag=""
  End If
end sub

Private Sub TxtTxEUR_Change()
  if txtnewval.tag=" " then exit sub
  '    - - - - - 
end sub

Private Sub TxtTxUSD_Change()
  if txtnewval.tag=" " then exit sub
  '    - - - - - 
end sub

End Sub

answered Jun 1, 2015 at 16:39

snb

3431 silver badge4 bronze badges

1 Comment

R3uK Over a year ago

I didn't know the tag property, but that can be handy indeed! I prefered the other solution because it need only one variable to check in every sub, but thanks for your contribution!

Collectives™ on Stack Overflow

VBA - UserForm with multiples TextBoxes in infinite calculation loop

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related