bug bloquant message macro VBA

  • Initiateur de la discussion NADEJKA
  • Date de début
N

NADEJKA

Guest
Bonjour,

Un bug sur ma macro me :angry: depuis ce matin.
Je ne trouve pas de solution.

Voici mon Pb:

J'ai créé un configurateur avec diverses contraintes; si une des contraintes est atteinte alors un message apparaît (TextBox) avec sortie par bouton YES.
Or j'ai un message qui reste bloqué... pour sortir il faut fermer Excel !!!

Avez-vous une idée?

Merci de votre aide.

Pour info voici ma macro

'Consommation***************************************** If ((Target.Row = 12) Or (Target.Row = 13) Or (Target.Row = 14) Or (Target.Row = 15) Or (Target.Row = 18) Or (Target.Row = 19) Or (Target.Row = 20) Or (Target.Row = 21) Or (Target.Row = 22) Or (Target.Row = 22)) And (Target.Column = 4) Or ((Target.Row = 12) Or (Target.Row = 16) Or (Target.Row = 32) Or (Target.Row = 33) Or (Target.Row = 34) And (Target.Column = 9)) Then
If ComboBox2.Text = '1U 220V AC' Or ComboBox2.Text = '1U 48V DC' Then
If ((3.5 + Worksheets('Input').Cells(12, 4).Value * 6.5) + (Worksheets('Input').Cells(13, 4).Value * 6.5) + (Worksheets('Input').Cells(14, 4).Value * 7) + (Worksheets('Input').Cells(15, 4).Value * 7) + (Worksheets('Input').Cells(18, 4).Value * 2) + (Worksheets('Input').Cells(19, 4).Value * 5.75) + (Worksheets('Input').Cells(20, 4).Value * 0) + (Worksheets('Input').Cells(21, 4).Value * 2.8) + (Worksheets('Input').Cells(22, 4).Value * 5.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 6.5) + (Worksheets('Input').Cells(32, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 2.5)) > 40 Or _
((3.5 + Worksheets('Input').Cells(12, 4).Value * 8.5) + (Worksheets('Input').Cells(13, 4).Value * 8.5) + (Worksheets('Input').Cells(14, 4).Value * 10) + (Worksheets('Input').Cells(15, 4).Value * 10) + (Worksheets('Input').Cells(18, 4).Value * 7) + (Worksheets('Input').Cells(19, 4).Value * 4.25) + (Worksheets('Input').Cells(20, 4).Value * 8) + (Worksheets('Input').Cells(21, 4).Value * 3.2) + (Worksheets('Input').Cells(22, 4).Value * 13.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 4.5) + (Worksheets('Input').Cells(32, 9).Value * 4.5) + (Worksheets('Input').Cells(33, 9).Value * 6.5) + (Worksheets('Input').Cells(33, 9).Value * 6)) > 55 Or _
(Worksheets('Input').Cells(6, 14).Value <> '') Then
choix = MsgBox('STOP' & Chr(10) & 'EXCEED AUTHORISED CONSUMPTION!', vbOKOnly + vbCritical)
Worksheets('Input').Cells(12, 4).Value = ''
Worksheets('Input').Cells(13, 4).Value = ''
Worksheets('Input').Cells(14, 4).Value = ''
Worksheets('Input').Cells(18, 4).Value = ''
Worksheets('Input').Cells(19, 4).Value = ''
Worksheets('Input').Cells(20, 4).Value = ''
Worksheets('Input').Cells(21, 4).Value = ''
Worksheets('Input').Cells(12, 9).Value = ''
Worksheets('Input').Cells(16, 9).Value = ''
Worksheets('Input').Cells(32, 9).Value = ''
Worksheets('Input').Cells(33, 9).Value = ''
Worksheets('Input').Cells(34, 9).Value = ''
End If
Else
If ((3.5 + Worksheets('Input').Cells(12, 4).Value * 6.5) + (Worksheets('Input').Cells(13, 4).Value * 6.5) + (Worksheets('Input').Cells(14, 4).Value * 7) + (Worksheets('Input').Cells(15, 4).Value * 7) + (Worksheets('Input').Cells(18, 4).Value * 2) + (Worksheets('Input').Cells(19, 4).Value * 5.75) + (Worksheets('Input').Cells(20, 4).Value * 0) + (Worksheets('Input').Cells(21, 4).Value * 2.8) + (Worksheets('Input').Cells(22, 4).Value * 5.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 6.5) + (Worksheets('Input').Cells(32, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 2.5)) > 90 Or _
((3.5 + Worksheets('Input').Cells(12, 4).Value * 8.5) + (Worksheets('Input').Cells(13, 4).Value * 8.5) + (Worksheets('Input').Cells(14, 4).Value * 10) + (Worksheets('Input').Cells(15, 4).Value * 10) + (Worksheets('Input').Cells(18, 4).Value * 7) + (Worksheets('Input').Cells(19, 4).Value * 4.25) + (Worksheets('Input').Cells(20, 4).Value * 8) + (Worksheets('Input').Cells(21, 4).Value * 3.2) + (Worksheets('Input').Cells(22, 4).Value * 13.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 4.5) + (Worksheets('Input').Cells(32, 9).Value * 4.5) + (Worksheets('Input').Cells(33, 9).Value * 6.5) + (Worksheets('Input').Cells(33, 9).Value * 6)) > 115 Or _
(Worksheets('Input').Cells(6, 14).Value <> '') Then
choix = MsgBox('STOP' & Chr(10) & 'EXCEED AUTHORISED CONSUMPTION!', vbOKOnly + vbCritical)
Worksheets('Input').Cells(12, 4).Value = ''
Worksheets('Input').Cells(13, 4).Value = ''
Worksheets('Input').Cells(14, 4).Value = ''
Worksheets('Input').Cells(18, 4).Value = ''
Worksheets('Input').Cells(19, 4).Value = ''
Worksheets('Input').Cells(20, 4).Value = ''
Worksheets('Input').Cells(21, 4).Value = ''
Worksheets('Input').Cells(12, 9).Value = ''
Worksheets('Input').Cells(16, 9).Value = ''
Worksheets('Input').Cells(32, 9).Value = ''
Worksheets('Input').Cells(33, 9).Value = ''
Worksheets('Input').Cells(34, 9).Value = ''
End If
End If
End If

'Slot frame*************************************************
If (Target.Row = 6) And (Target.Column = 7) Then
If ComboBox1.Text = '2.5' Then
If ComboBox2.Text <> '' Then
If Worksheets('Input').Cells(6, 7).Value <> '' Then
choix = MsgBox('STOP' & Chr(10) & 'Too many slot for the Frame!', vbOKOnly + vbCritical)
Worksheets('Input').Cells(12, 4).Value = ''
Worksheets('Input').Cells(13, 4).Value = ''
Worksheets('Input').Cells(14, 4).Value = ''
Worksheets('Input').Cells(18, 4).Value = ''
Worksheets('Input').Cells(19, 4).Value = ''
Worksheets('Input').Cells(20, 4).Value = ''
Worksheets('Input').Cells(21, 4).Value = ''
Worksheets('Input').Cells(12, 9).Value = ''
Worksheets('Input').Cells(16, 9).Value = ''
Worksheets('Input').Cells(32, 9).Value = ''
Worksheets('Input').Cells(33, 9).Value = ''
Worksheets('Input').Cells(34, 9).Value = ''
End If
End If
Else '2.6
If ComboBox2.Text <> '' Then
If Worksheets('Input').Cells(6, 7).Value <> '' Then
choix = MsgBox('STOP' & Chr(10) & 'Too many slot for the Frame!', vbOKOnly + vbCritical)
Worksheets('Input').Cells(12, 4).Value = ''
Worksheets('Input').Cells(13, 4).Value = ''
Worksheets('Input').Cells(14, 4).Value = ''
Worksheets('Input').Cells(15, 4).Value = ''
Worksheets('Input').Cells(18, 4).Value = ''
Worksheets('Input').Cells(19, 4).Value = ''
Worksheets('Input').Cells(20, 4).Value = ''
Worksheets('Input').Cells(21, 4).Value = ''
Worksheets('Input').Cells(22, 4).Value = ''
Worksheets('Input').Cells(12, 9).Value = ''
Worksheets('Input').Cells(16, 9).Value = ''
Worksheets('Input').Cells(32, 9).Value = ''
Worksheets('Input').Cells(33, 9).Value = ''
Worksheets('Input').Cells(34, 9).Value = ''
End If
End If
End If
End If
 
Trier par date Trier par votes
B

Bebere

XLDnaute Barbatruc
bonjour

target.row ds quelle colonne d ou i

faire un test comme suit

For Each Cel In Range('D4:D34')
If Cel.Row = 4 Or Cel.Row = 12 Then

End If

next CEl

plus simple serait peut-être de mettre les additions sur la feuille et de tester sur
les additions


au revoir
 
Vote positif 0 Vote négatif
C

Creepy

XLDnaute Accro
Hello,

Houlalalala tres tres lourd ce code !!

On pourrait pas avoir un exemlpe en PJ Stp par ce que là c'est difficile de suivre le fil de la macro !!

deja une remarque d'ordre général, remplace ca :

Worksheets('Input').Cells(12, 4).Value = ''
Worksheets('Input').Cells(13, 4).Value = ''
Worksheets('Input').Cells(14, 4).Value = ''
Worksheets('Input').Cells(15, 4).Value = ''
Worksheets('Input').Cells(18, 4).Value = ''
etc ....

Par :

Code: 
with Worksheets('Input')
.Cells(12, 4).Value = '' 
.Cells(13, 4).Value = '' 
etc..
end with
Ce sera deja plus propre.

Ensuite pour ton message d'erreur il faudrait tracer la macro pas à pas parce que là !! C'est la folie

:eek:

++

Creepy

Message édité par: Creepy, à: 28/06/2005 13:35
 
Vote positif 0 Vote négatif
C

Creepy

XLDnaute Accro
RE all,

Tiens j'ai même fait ca :

Code: 
Private Sub CommandButton1_Click()
If ((Target.Row = 12) Or (Target.Row = 13) Or (Target.Row = 14) Or (Target.Row = 15) Or (Target.Row = 18) Or (Target.Row = 19) Or (Target.Row = 20) Or (Target.Row = 21) Or (Target.Row = 22) Or (Target.Row = 22)) And (Target.Column = 4) Or ((Target.Row = 12) Or (Target.Row = 16) Or (Target.Row = 32) Or (Target.Row = 33) Or (Target.Row = 34) And (Target.Column = 9)) Then

If ComboBox2.Text = '1U 220V AC' Or ComboBox2.Text = '1U 48V DC' Then

If ((3.5 + Worksheets('Input').Cells(12, 4).Value * 6.5) + (Worksheets('Input').Cells(13, 4).Value * 6.5) + (Worksheets('Input').Cells(14, 4).Value * 7) + (Worksheets('Input').Cells(15, 4).Value * 7) + (Worksheets('Input').Cells(18, 4).Value * 2) + (Worksheets('Input').Cells(19, 4).Value * 5.75) + (Worksheets('Input').Cells(20, 4).Value * 0) + (Worksheets('Input').Cells(21, 4).Value * 2.8) + (Worksheets('Input').Cells(22, 4).Value * 5.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 6.5) + (Worksheets('Input').Cells(32, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 2.5)) > 40 Or _
((3.5 + Worksheets('Input').Cells(12, 4).Value * 8.5) + (Worksheets('Input').Cells(13, 4).Value * 8.5) + (Worksheets('Input').Cells(14, 4).Value * 10) + (Worksheets('Input').Cells(15, 4).Value * 10) + (Worksheets('Input').Cells(18, 4).Value * 7) + (Worksheets('Input').Cells(19, 4).Value * 4.25) + (Worksheets('Input').Cells(20, 4).Value * 8) + (Worksheets('Input').Cells(21, 4).Value * 3.2) + (Worksheets('Input').Cells(22, 4).Value * 13.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 4.5) + (Worksheets('Input').Cells(32, 9).Value * 4.5) + (Worksheets('Input').Cells(33, 9).Value * 6.5) + (Worksheets('Input').Cells(33, 9).Value * 6)) > 55 Or _
(Worksheets('Input').Cells(6, 14).Value <> '') Then


choix = MsgBox('STOP' & Chr(10) & 'EXCEED AUTHORISED CONSUMPTION!', vbOKOnly + vbCritical)
   CleanCell
End If
Else
If ((3.5 + Worksheets('Input').Cells(12, 4).Value * 6.5) + (Worksheets('Input').Cells(13, 4).Value * 6.5) + (Worksheets('Input').Cells(14, 4).Value * 7) + (Worksheets('Input').Cells(15, 4).Value * 7) + (Worksheets('Input').Cells(18, 4).Value * 2) + (Worksheets('Input').Cells(19, 4).Value * 5.75) + (Worksheets('Input').Cells(20, 4).Value * 0) + (Worksheets('Input').Cells(21, 4).Value * 2.8) + (Worksheets('Input').Cells(22, 4).Value * 5.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 6.5) + (Worksheets('Input').Cells(32, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 4.3) + (Worksheets('Input').Cells(33, 9).Value * 2.5)) > 90 Or _
((3.5 + Worksheets('Input').Cells(12, 4).Value * 8.5) + (Worksheets('Input').Cells(13, 4).Value * 8.5) + (Worksheets('Input').Cells(14, 4).Value * 10) + (Worksheets('Input').Cells(15, 4).Value * 10) + (Worksheets('Input').Cells(18, 4).Value * 7) + (Worksheets('Input').Cells(19, 4).Value * 4.25) + (Worksheets('Input').Cells(20, 4).Value * 8) + (Worksheets('Input').Cells(21, 4).Value * 3.2) + (Worksheets('Input').Cells(22, 4).Value * 13.5) + (Worksheets('Input').Cells(12, 9).Value * 5) + (Worksheets('Input').Cells(16, 9).Value * 4.5) + (Worksheets('Input').Cells(32, 9).Value * 4.5) + (Worksheets('Input').Cells(33, 9).Value * 6.5) + (Worksheets('Input').Cells(33, 9).Value * 6)) > 115 Or _
(Worksheets('Input').Cells(6, 14).Value <> '') Then
choix = MsgBox('STOP' & Chr(10) & 'EXCEED AUTHORISED CONSUMPTION!', vbOKOnly + vbCritical)
CleanCell
End If
End If
End If

'Slot frame*************************************************
If (Target.Row = 6) And (Target.Column = 7) Then
If ComboBox1.Text = '2.5' Then
If ComboBox2.Text <> '' Then
If Worksheets('Input').Cells(6, 7).Value <> '' Then
choix = MsgBox('STOP' & Chr(10) & 'Too many slot for the Frame!', vbOKOnly + vbCritical)
CleanCell
End If
End If
Else '2.6
If ComboBox2.Text <> '' Then
If Worksheets('Input').Cells(6, 7).Value <> '' Then
choix = MsgBox('STOP' & Chr(10) & 'Too many slot for the Frame!', vbOKOnly + vbCritical)
CleanCell
Worksheets('Input').Cells(15, 4).Value = ''
Worksheets('Input').Cells(22, 4).Value = ''
End If
End If
End If
End If
 
End Sub
Sub CleanCell()
With Worksheets('Input')
   .Cells(12, 4).Value = ''
   .Cells(13, 4).Value = ''
   .Cells(14, 4).Value = ''
   .Cells(18, 4).Value = ''
   .Cells(19, 4).Value = ''
   .Cells(20, 4).Value = ''
   .Cells(21, 4).Value = ''
   .Cells(12, 9).Value = ''
   .Cells(16, 9).Value = ''
   .Cells(32, 9).Value = ''
   .Cells(33, 9).Value = ''
   .Cells(34, 9).Value = ''
End With
End Sub

Beaucoup plus propre.

Ensuite remplace tes & Chr(10) & par des & Vbcrlf & car les CHR(10) peuvent planter sur certain micro et crois moi je sais de quoi je parle j'y fais la gaffe plus d'une fois avant de bien comprendre lol

++ dans l'attente de ton fichier

Creepy
 
Vote positif 0 Vote négatif

Discussions similaires

L
  • Question
VBA : erreur sur boucle
Réponses
2
Affichages
82
Latias
L
T
  • Question
XL 2016 Optimisation d'une boucle
2 3
Réponses
30
Affichages
932
thivame
T
yojema
  • Question
XL 2021 faire évoluer un code VBA
Réponses
3
Affichages
162
Dranreb
Dranreb
G
  • Question
Microsoft 365 Réduire la taille de la macro ?
Réponses
9
Affichages
604
Staple1600
Staple1600
S
  • Question
Microsoft 365 créer un macro vba
Réponses
0
Affichages
153
souhaib ad
S
V
  • Résolu(e)
XL 2016 Facturier : Retour à la ligne sur la facture
2
Réponses
19
Affichages
567
Vermithor
V
V
  • Résolu(e)
XL 2016 Exportation au format PDF à l'aide d'une macro : erreur 1004 'Document non enregistré'
Réponses
14
Affichages
528
Phil69970
Phil69970
Dadi147
  • Résolu(e)
XL 2016 cellules A, b, C et D sont automatiquement vides
Réponses
3
Affichages
375
job75
job75
M
  • Résolu(e)
XL 2016 Filtre auto VBA
Réponses
3
Affichages
587
MickaeL_D
M
T
  • Question
Microsoft 365 Problème affichage multipage dans un tabstrip
Réponses
1
Affichages
344
troki74
T

Membres actuellement en ligne

19 membres et 254 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 234
Messages
2 086 470
Membres
103 226
dernier inscrit
smail12