VBA: Input box qui s'affiche à nouveau si l'utilisateur saisit : cash_out > account_balance

**lulu4466** · 09/06/2020, 14h22

Bonjour,
Je souhaite réafficher l'input box "How many cash in?" si l'utilisateur saisit un montant de cash_out supérieur à account_balance.
Comment faire? Merci.

Compute tax cryptocurrency.xlsm

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
Option Explicit
 
Sub compute_tax_crypto()
 
  Worksheets("Sheet1").Activate
  Dim number_of_session As Single
  Dim cash_in As Single
  Dim profit_or_loss As Single
  Dim account_balance As Single
  Dim cash_out As Single
  Dim end_session_profit_or_loss_before_tax As Single
  Dim tax_percentage As Single
  tax_percentage = 30
  Dim end_session_profit_or_loss_after_tax As Single
  Dim i As Integer
  i = 1
  Dim irow As Single
 
  number_of_session = InputBox("How many sessions did you trade?")
  Range("A2").Value = number_of_session
 
 
  Do Until i >= number_of_session
    Cells(1, 8) = i
    i = i + 1
 
 
      cash_in = InputBox("How many cash-in?")
      Range("B2").Value = cash_in
      profit_or_loss = InputBox("How many profit or loss?")
      Range("C2").Value = profit_or_loss
      account_balance = cash_in + profit_or_loss
      Range("D2").Value = account_balance
      cash_out = InputBox("How many cash out?")
        If cash_out > account_balance Then
          Sub DefaultMsgBox()
          MsgBox ("Cash out is too high compared to account_balance! Put a smaller cash out.")
 
          Return
          Else
          End Sub
      Range("E2").Value = cash_out
      end_session_profit_or_loss_before_tax = cash_out - (cash_in * (cash_out / account_balance))
      Range("F2").Value = end_session_profit_or_loss_before_tax
          If Range("F2").Value <= 0 Then
             Range("G2").Value = 0
             MsgBox ("The end session profit or loss after tax is : " & 0)
             Else
             end_session_profit_or_loss_after_tax = (tax_percentage / 100) * end_session_profit_or_loss_before_tax
             Range("G2").Value = end_session_profit_or_loss_after_tax
             MsgBox ("The end session profit or loss after tax is : " & end_session_profit_or_loss_after_tax)
             End If
 
  Loop
 
 
 
 
End Sub

**mfoxy** · 09/06/2020, 14h29

Bonjour,

Bien que peu orthodoxe, une boucle tant que X inférieur à Y, MsgBox erreur, Inputbox pour récupérer Y.

Au vu de votre code actuel, je vous conseillerai aussi la lecture de ce petit tutoriel sur les bases du langage vba.

https://mhubiche.developpez.com/vba/...yntaxes/bases/

**lulu4466** · 09/06/2020, 14h48

Si j'ai bien compris, pour la forme, je dois enlever les parenthèses de MsgBox?
Nom : Capture.PNG
Affichages : 154
Taille : 3,2 Ko

Pour le fond, je vois bien qu'il faut procéder de manière suivante:
1: boucle tant que x inférieur à y [ou tant que account_balance < cash_out]
2: msgbox erreur
3: inputbox pour récupérer y

MAIS JE NE SAIS PAS LE CODER..

**lulu4466** · 09/06/2020, 15h26

J'ai essayé de modifier le fond.
En respectant le schéma:
1: boucle tant que x<y (account balance<cash in)
2: ouvrir msg box
3: rappeler input box x(cash in)

Mais cela ne marche pas. Cela se répète sans fin.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Option Explicit
 
Sub compute_tax_crypto()
 
  Worksheets("Sheet1").Activate
  Dim number_of_session As Single
  Dim cash_in As Single
  Dim profit_or_loss As Single
  Dim account_balance As Single
  Dim cash_out As Single
  Dim end_session_profit_or_loss_before_tax As Single
  Dim tax_percentage As Single
  tax_percentage = 30
  Dim end_session_profit_or_loss_after_tax As Single
  Dim i As Integer
  i = 1
  Dim irow As Single
 
  number_of_session = InputBox("How many sessions did you trade?")
  Range("A2").Value = number_of_session
 
 
  Do Until i >= number_of_session
    Cells(1, 8) = i
    i = i + 1
 
 
      cash_in = InputBox("How many cash-in?")
      Range("B2").Value = cash_in
      profit_or_loss = InputBox("How many profit or loss?")
      Range("C2").Value = profit_or_loss
      account_balance = cash_in + profit_or_loss
      Range("D2").Value = account_balance
      cash_out = InputBox("How many cash out?")
          While account_balance < cash_out
                MsgBox ("Error! Put a cash out inferior to account balance")
                InputBox ("How many cash out?")
                If cash_out <= account_balance Then break
 
      Range("E2").Value = cash_out
      end_session_profit_or_loss_before_tax = cash_out - (cash_in * (cash_out / account_balance))
      Range("F2").Value = end_session_profit_or_loss_before_tax
          If Range("F2").Value <= 0 Then
             Range("G2").Value = 0
             MsgBox "The end session profit or loss after tax is : " & 0
             Else
             end_session_profit_or_loss_after_tax = (tax_percentage / 100) * end_session_profit_or_loss_before_tax
             Range("G2").Value = end_session_profit_or_loss_after_tax
             MsgBox "The end session profit or loss after tax is : " & end_session_profit_or_loss_after_tax
             End If
 
  Loop

**mfoxy** · 09/06/2020, 15h47

Re,

Suis sur mobile et ne saurais donc pas poster un code testé, pour ton cas précis.

Un petit exemple, tout de même, pour te mettre sur la route.

On va tester un code postal :

Condition :
Tant que le code postal ne sera pas entièrement numeric et comportera bien 4 chiffres (Belgique)

Colle le code ci-dessous dans un New module pour test

À toi ensuite de modifier pour correspondre à ton besoin, puis tester, et ensuite ajouter à ta procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Sub InputBoxDemo()
    Dim postcode As String
    Do
       postcode = VBA.InputBox("please enter into the postcode(4 digits)", "my InputBoxDemo")
    Loop Until VBA.Len(postcode) = 4 And VBA.IsNumeric(postcode)
    MsgBox "Le CP est : " & postcode
End Sub

**lulu4466** · 09/06/2020, 16h14

Merci, j'ai réussi à presque faire ce que je voulais, il ne manque plus qu'à afficher un msg box d'erreur quand le cash_out >account_balance!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Option Explicit
 
Sub compute_tax_crypto()
 
  Worksheets("Sheet1").Activate
  Dim number_of_session As Single
  Dim cash_in As Single
  Dim profit_or_loss As Single
  Dim account_balance As Single
  Dim cash_out As Single
  Dim end_session_profit_or_loss_before_tax As Single
  Dim tax_percentage As Single
  tax_percentage = 30
  Dim end_session_profit_or_loss_after_tax As Single
  Dim i As Integer
  i = 1
  Dim irow As Single
 
  number_of_session = InputBox("How many sessions did you trade?")
  Range("A2").Value = number_of_session
 
 
  Do Until i >= number_of_session
    Cells(1, 8) = i
    i = i + 1
 
 
      cash_in = InputBox("How many cash-in?")
      Range("B2").Value = cash_in
      profit_or_loss = InputBox("How many profit or loss?")
      Range("C2").Value = profit_or_loss
      account_balance = cash_in + profit_or_loss
      Range("D2").Value = account_balance
      Do
        cash_out = VBA.InputBox("How many cash out?")
      Loop Until cash_out <= account_balance
      MsgBox "The amount of cash out is under amount of account balance, great!"
 
 
 
      Range("E2").Value = cash_out
      end_session_profit_or_loss_before_tax = cash_out - (cash_in * (cash_out / account_balance))
      Range("F2").Value = end_session_profit_or_loss_before_tax
          If Range("F2").Value <= 0 Then
             Range("G2").Value = 0
             MsgBox "The end session profit or loss after tax is : " & 0
             Else
             end_session_profit_or_loss_after_tax = (tax_percentage / 100) * end_session_profit_or_loss_before_tax
             Range("G2").Value = end_session_profit_or_loss_after_tax
             MsgBox "The end session profit or loss after tax is : " & end_session_profit_or_loss_after_tax
             End If
 
  Loop
 
 
 
 
End Sub

**mfoxy** · 09/06/2020, 16h21

Re,

Il te suffit d'ajouter ton MsgBox et condition dans ta boucle ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
  Do
        cash_out = VBA.InputBox("How many cash out?")
        If cash_out > account_balance then MsgBox "ton message" 
      Loop Until cash_out <= account_balance

VBA: Input box qui s'affiche à nouveau si l'utilisateur saisit : cash_out > account_balance [XL-2016]

Macros et VBA Excel

Discussions similaires

Partager

Partager