Help with VBA

[operabr]

Could someone help me, please? I've been looking at this code for hours and I can't seem to find my error.

Problem:

I have a form that is requesting some user information, namely:

txQtdeTransfOrigem txBonusOrigem txDesagio txNovoSaldo txNovoCPM

The textboxes: txSaldoOrigem, txCPMOrigem, txSaldoDestino, and txCPMDestino will be provided by the database.

I need to calculate the data for txNovoSaldo and txNovoCPM, according to the information entered by the user in the userForm.

What I want it to do: I wanted it so that every time the user enters a new value in txQtdeTransfOrigem, the value of txNovoSaldo would be updated (for this I used a change event). However, if the value of txQtdeTransfOrigem is empty, the values of the other fields should also be empty.

The same should happen for txBonusOrigem and txDesagio, and whenever there is a change in these fields, the calculation of the value of txNovoSaldo and txNovoCPM should be updated according to the calculations below.

However, the error that is happening is that whenever I enter a value in txQtdeTransfOrigem and then try to clear the field by leaving it empty, it goes into a loop, and I don't know how to solve this problem. I thought about setting the variable txQtdeTransfOrigem to 0 and then clearing the field, which worked to some extent, but after I filled in the other fields and tried to clear it again, the same error occurred.

Can someone help me? I don't know what I'm doing wrong...

For this, I created 5 subroutines, namely:

Private Sub txQtdeTransfOrigem_Change()
If Me.txQtdeTransfOrigem.Value = "" Then
      Me.txBonusOrigem.Value = ""
      Me.txDesagio.Value = "" 
      Me.txNovoSaldo.Value = ""
      Me.txNovoCPM.Value = ""
Else
      Me.txQtdeTransfOrigem.Value = Format(Me.txQtdeTransfOrigem.Value, "#,##0")
      saldoTransferencia = valorSaldoDestino + Me.txQtdeTransfOrigem.Value
      Me.txNovoSaldo.Value = Format(saldoTransferencia, "#,##0")
End If
End Sub

Private Sub txQtdeTransfOrigem_AfterUpdate()
If Me.txQtdeTransfOrigem.Value <> "" Then
     If Me.txQtdeTransfOrigem.Value > valorSaldoMilhas Then
           MsgBox "Insufficient balance to make the transfer!"
           Me.txQtdeTransfOrigem.Value = ""
           Me.txBonusOrigem.Value = ""
           Me.txDesagio.Value = ""
           Me.txNovoSaldo.Value = ""
           Me.txNovoCPM.Value = ""
           txQtdeTransfOrigem.SetFocus
     End If
End If
End Sub

Private Sub txBonusOrigem_Change()
If Me.txBonusOrigem.Value = "" Then
      bonusOrigem = 1
Else
      bonusOrigem = (Val(Replace(Me.txBonusOrigem.Value, "%", "")) / 100) + 1
End If
If Me.txQtdeTransfOrigem.Value = "" Then       
     Me.txQtdeTransfOrigem.Value = 0       
     totalComBonus = Me.txQtdeTransfOrigem.Value * bonusOrigem   
Else       
     totalComBonus = Me.txQtdeTransfOrigem.Value * bonusOrigem   
End If   
saldoBonusTransferencia = valorSaldoDestino + totalComBonus   
Me.txNovoSaldo.Value = Format(saldoBonusTransferencia, "#,##0")
End Sub

Private Sub txDesagio_Change()
If IsNumeric(Me.txDesagio.Value) And Me.txDesagio.Value > 0 Then   
     desagio = CDbl(Me.txDesagio.Value)
Else   
     desagio = 1
End If
conta7 = Me.txCPMOrigem.Value * desagio
contaDesagio = totalComBonus / desagio
totalBonusDesagio = valorSaldoDestino + contaDesagio
Me.txNovoSaldo.Value = Format(totalBonusDesagio, "#,##0")

Private Sub txNovoSaldo_Change()   
Call txBonusOrigem_Change   
Call txDesagio_Change           
CPMDestino = Me.txCPMDestino.Value   
CPMOrigem = conta7 / bonusOrigem       
contaDesagio = totalComBonus / desagio   
totalBonusDesagio = valorSaldoDestino + contaDesagio   
conta2 = CPMDestino * valorSaldoDestino   
conta3 = CPMOrigem * contaDesagio   
conta4 = valorSaldoDestino + contaDesagio   
conta5 = conta2 + conta3   
If conta4 = 0 Then       
     Me.txNovoSaldo.Value = ""       
     Me.txNovoCPM.Value = ""   
Else       
     CPMFinal = conta5 / conta4       
     Me.txNovoCPM.Value = Format(CPMFinal, "Currency")   
End If
End Sub

 

[rlv01]

However, the error that is happening is that whenever I enter a value in txQtdeTransfOrigem and then try to clear the field by leaving it empty, it goes into a loop, and I don't know how to solve this problem. I thought about setting the variable txQtdeTransfOrigem to 0 and then clearing the field, which worked to some extent, but after I filled in the other fields and tried to clear it again, the same error occurred.

Recommend that you set a breakpoint in the txQtdeTransfOrigem_Change subroutine then use the VBE debugger to single-step your code to see why it goes into a loop when you clear it.

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.