Option Explicit
Private Sub Worksheet_Activate()
Dim PlageB As Range, PlageC As Range, PlageD As Range, PlageE As Range, PlageF As Range, PlageG As Range, PlageH As Range, PlageO As Range
With Sheets("F1")
Set PlageB = .Range("B4:B5,B8,B36:B40,B42:B43,B45,B48:B52,B54")
Set PlageC = .Range("C30:C32,C37:C39,C44,C53,C55:C57,C65:C66,C69")
Set PlageD = .Range("D4,D8,D11,D17,D28,D52")
Set PlageE = .Range("E11:E16,E37,E39,E53:E54,E65:E67")
Set PlageF = .Range("F4,F11:F16,F31,F33:F37,F40:F43,F45,F48:F57")
Set PlageG = .Range("G11:G16,G31,G33:G37,G40:G43,G45,G48:G57")
Set PlageH = .Range("H11:H17,H30:H31,H33:H37,H40:H43,H45,H48:H57,H65:H67,H69")
Set PlageO = .Range("O28")
If Range("K11").Value <> 143 Then
Union(PlageB, PlageC, PlageD, PlageE, PlageF, PlageG, PlageH, PlageO).Select
MsgBox "Merci de bien vouloir remplir les cellules sélectionnées !", vbCritical, "ATTENTION ..."
End If
End With
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Sheets("Feuil1")
If .Range("K11").Value <> 144 Then
Application.EnableEvents = False
.Activate
Application.EnableEvents = True
[COLOR="Red"]Union[/COLOR](Range("F48:F57,G11:G16,G31,G33:G37,G40:G43,G45,G48:G57,H11:H17,H30:H31,H33:H37,H40:H43,H45,H48:H52,H53:H57,H65:H67,H69,O28,B4:B5,B8,B33:B36,B40,B42:B43,B45,B48:B52,B54,C30:C32,C37:C39,C44,C53,C55:C57,C65:C66,C69,D4,D8,D11:D17,D28,D52,E11:E16,E37,E39,E53:E54"), _
Range("E65:E67,F4,F11:F16,F31,F33:F37,F40:F43,F45")).Select
MsgBox "Merci de bien vouloir remplir les cellules sélectionnées !", vbCritical, "ATTENTION ..."
Exit Sub 'utile s'il y a du code après End If...
End If
End With
End Sub
Exact aussi pour le NBVAL(), j'en compte 138 et non 143
Par contre je souhaiterai que la cellule "K11" soit sur une autre feuille (...)
quand on laisse 1 cellule vide de la feuille 1 on peut quand même passer à la feuille2 ce qui ne devrait pas se produire
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "F1" Then Exit Sub
Dim PlageB As Range, PlageC As Range, PlageD As Range, PlageE As Range, PlageF As Range, PlageG As Range, PlageH As Range, PlageO As Range, Plage As Range
With Sheets("F1")
Set PlageB = .Range("B4:B5,B8,B33:B36,B40,B42:B43,B45,B48:B52,B54")
Set PlageC = .Range("C30:C32,C37:C39,C44,C53,C55:C57,C65:C66,C69")
Set PlageD = .Range("D4,D8,D11:D17,D28,D52")
Set PlageE = .Range("E11:E16,E37,E39,E53:E54,E65:E67")
Set PlageF = .Range("F4,F11:F16,F31,F33:F37,F40:F43,F45,F48:F57")
Set PlageG = .Range("G11:G16,G31,G33:G37,G40:G43,G45,G48:G57")
Set PlageH = .Range("H11:H17,H30:H31,H33:H37,H40:H43,H45,H48:H57,H65:H67,H69")
Set PlageO = .Range("O28")
Set Plage = Union(PlageB, PlageC, PlageD, PlageE, PlageF, PlageG, PlageH, PlageO)
If Application.CountA(Plage) < Plage.Count Then
.Activate
Plage.Select
MsgBox "Merci de bien vouloir remplir les cellules sélectionnées !", vbCritical, "ATTENTION ..."
End If
End With
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "F1" Then Exit Sub
Dim PlageB As Range, PlageC As Range, PlageD As Range, PlageE As Range, PlageF As Range, PlageG As Range, PlageH As Range, PlageO As Range, Plage As Range
With Sheets("F1")
Set PlageB = .Range("B4:B5,B8,B33:B36,B40,B42:B43,B45,B48:B52,B54")
Set PlageC = .Range("C30:C32,C37:C39,C44,C53,C55:C57,C65:C66,C69")
Set PlageD = .Range("D4,D8,D11:D17,D28,D52")
Set PlageE = .Range("E11:E16,E37,E39,E53:E54,E65:E67")
Set PlageF = .Range("F4,F11:F16,F31,F33:F37,F40:F43,F45,F48:F57")
Set PlageG = .Range("G11:G16,G31,G33:G37,G40:G43,G45,G48:G57")
Set PlageH = .Range("H11:H17,H30:H31,H33:H37,H40:H43,H45,H48:H57,H65:H67,H69")
Set PlageO = .Range("O28")
Set Plage = Union(PlageB, PlageC, PlageD, PlageE, PlageF, PlageG, PlageH, PlageO)
If Application.CountA(Plage) < Plage.Count Then
.Activate
Plage.[COLOR="Red"]SpecialCells(xlCellTypeBlanks).[/COLOR]Select
MsgBox "Merci de bien vouloir remplir les cellules sélectionnées !", vbCritical, "ATTENTION ..."
End If
End With
End Sub
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "F1" Then Exit Sub
Dim PlageB As Range, PlageC As Range, PlageD As Range, PlageE As Range, PlageF As Range, PlageG As Range, PlageH As Range, PlageO As Range, Plage As Range
With Sheets("F1")
Set PlageB = .Range("B4:B5,B8,B33:B36,B40,B42:B43,B45,B48:B52,B54")
Set PlageC = .Range("C30:C32,C37:C39,C44,C53,C55:C57,C65:C66,C69")
Set PlageD = .Range("D4,D8,D11:D17,D28,D52")
Set PlageE = .Range("E11:E16,E37,E39,E53:E54,E65:E67")
Set PlageF = .Range("F4,F11:F16,F31,F33:F37,F40:F43,F45,F48:F57")
Set PlageG = .Range("G11:G16,G31,G33:G37,G40:G43,G45,G48:G57")
Set PlageH = .Range("H11:H17,H30:H31,H33:H37,H40:H43,H45,H48:H57,H65:H67,H69")
Set PlageO = .Range("O28")
Set Plage = Union(PlageB, PlageC, PlageD, PlageE, PlageF, PlageG, PlageH, PlageO)
[B][COLOR=Red]If Application.CountA(Plage) = Plage.Count Then Exit Sub[/COLOR][/B]
If Application.CountA(Plage) < Plage.Count Then .Activate
Plage.SpecialCells(xlCellTypeBlanks).Select
MsgBox "Merci de bien vouloir remplir les cellules sélectionnées !", vbCritical, "ATTENTION ..."
End With
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim PlageB As Range, PlageC As Range, PlageD As Range, PlageE As Range, PlageF As Range, PlageG As Range, PlageH As Range, PlageO As Range, Plage As Range
With Sheets("F1")
Set PlageB = .Range("B4:B5,B8,B33:B36,B40,B42:B43,B45,B48:B52,B54")
Set PlageC = .Range("C30:C32,C37:C39,C44,C53,C55:C57,C65:C66,C69")
Set PlageD = .Range("D4,D8,D11:D17,D28,D52")
Set PlageE = .Range("E11:E16,E37,E39,E53:E54,E65:E67")
Set PlageF = .Range("F4,F11:F16,F31,F33:F37,F40:F43,F45,F48:F57")
Set PlageG = .Range("G11:G16,G31,G33:G37,G40:G43,G45,G48:G57")
Set PlageH = .Range("H11:H17,H30:H31,H33:H37,H40:H43,H45,H48:H57,H65:H67,H69")
Set PlageO = .Range("O28")
Set Plage = Union(PlageB, PlageC, PlageD, PlageE, PlageF, PlageG, PlageH, PlageO)
[COLOR="Red"]If Sh.Name = .Name Or Application.CountA(Plage) = Plage.Count Then Exit Sub[/COLOR]
.Activate
Plage.SpecialCells(xlCellTypeBlanks).Select
MsgBox "Merci de bien vouloir remplir les cellules sélectionnées !", vbCritical, "ATTENTION ..."
End With
End Sub