probleme alerte vba

judoka0209

XLDnaute Occasionnel
bonjour, j'ai un soucis je l'alerte se fasse que pour les case 6 et 7 mais pas les autres
sauf que ca me met une alerte sur les case 9 10 11
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("c6:nc11")) Is Nothing Then
With alerte.Label1
Select Case Target.Row
Case 6
.Caption = "Alerte 1"
Case 7
.Caption = "Alerte 2"

End Select
End With
Select Case UCase(Target.Value)
Case Is = "21", "21ND", "10ND", "10", "0HP", "21ND", "FP", "33", "DE", "41", "RM", "SYND", "AKPS", "AKSC", "GT EX", "GT PRO", "SEM"
alerte.Show
End Select
End If
End Sub
 

Pièces jointes

  • testAlerte V1.xlsm
    42.8 KB · Affichages: 16

Jacky67

XLDnaute Barbatruc
bonjour, j'ai un soucis je l'alerte se fasse que pour les case 6 et 7 mais pas les autres
sauf que ca me met une alerte sur les case 9 10 11
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("c6:nc11")) Is Nothing Then
With alerte.Label1
Select Case Target.Row
Case 6
.Caption = "Alerte 1"
Case 7
.Caption = "Alerte 2"

End Select
End With
Select Case UCase(Target.Value)
Case Is = "21", "21ND", "10ND", "10", "0HP", "21ND", "FP", "33", "DE", "41", "RM", "SYND", "AKPS", "AKSC", "GT EX", "GT PRO", "SEM"
alerte.Show
End Select
End If
End Sub
Bonsoir,
Si cela est définitivement que les lignes 6 et 7, alors remplace
If Not Application.Intersect(Target, Range("c6:nc11")) Is Nothing Then
Par
If Not Application.Intersect(Target, Range("c6:nc7")) Is Nothing Then
 

Pièces jointes

  • testAlerte V1 (1).xlsm
    43.8 KB · Affichages: 12
Dernière édition:

Jacky67

XLDnaute Barbatruc
la classe super bien merci beaucoup
RE..
Oui…..Mais
Si ton tableau risque de s'élargir , par exemple une année bissextile,
utilise ce code
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Plage, Col&
If Target.Count > 1 Then Exit Sub
Col = Cells(2, Columns.Count).End(xlToLeft).Column
Set Plage = Union(Range(Cells(6, 3), Cells(7, Col)), Range(Cells(17, 3), Cells(18, Col)), Range(Cells(28, 3), Cells(29, Col)), Range(Cells(39, 3), Cells(40, Col)), Range(Cells(50, 3), Cells(51, Col)), Range(Cells(60, 3), Cells(61, Col)))
If Not Application.Intersect(Target, Plage) Is Nothing Then
  With alerte.Label1
    Select Case Target.Row
      Case 6, 17, 28, 39, 50, 61
        .Caption = "Alerte 1"
      Case 7, 18, 29, 40, 51, 62
        .Caption = "Alerte 2"
    End Select
  End With
  Select Case UCase(Target.Value)
    Case Is = "21", "21ND", "10ND", "10", "0HP", "21ND", "FP", "33", "DE", "41", "RM", "SYND", "AKPS", "AKSC", "GT EX", "GT PRO", "SEM"
      alerte.Show
  End Select
End If
End Sub
Ou alors modifier ce tableau en calendrier perpétuel(voir pj)
 

Pièces jointes

  • testAlerte V2.xlsm
    185.9 KB · Affichages: 15
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 744
Messages
2 082 107
Membres
101 893
dernier inscrit
trompette