Simplification d'un code VBA

Bonjour à tous,

Voici mon code :

Private Sub ChkB01_Click()
    'CONCERNÉ/NON-CONCERNÉ 01
    Sheets("Base").Select
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row

Set CL = Range("A2:A" & Dlig).Find(What:=Me.TBoxM1.Value, LookIn:=xlValues, LookAt:=xlWhole)
   If ChkB01.Value = True Then
   Sheets("Base").Range("DH" & CL.Row) = "NC"
   Else
   Sheets("Base").Range("DH" & CL.Row).ClearContents
   End If

Set CL = Range("A2:A" & Dlig).Find(What:=Me.TBoxM2.Value, LookIn:=xlValues, LookAt:=xlWhole)
   If ChkB01.Value = True Then
   Sheets("Base").Range("DH" & CL.Row) = "NC"
   Else
   Sheets("Base").Range("DH" & CL.Row).ClearContents
   End If

Set CL = Range("A2:A" & Dlig).Find(What:=Me.TBoxM3.Value, LookIn:=xlValues, LookAt:=xlWhole)
   If ChkB01.Value = True Then
   Sheets("Base").Range("DH" & CL.Row) = "NC"
   Else
   Sheets("Base").Range("DH" & CL.Row).ClearContents
   End If

End Sub

Etant donné qu'i y a la redondance de if/then/else, il y a un moyen de simplifier opur le même effet ?

Ce code permet, e n cliquant dans une checkbox, de mettre VRAI ou FAUX dans une liste de cases.

Merci par avance =)

Re,

J'ai essayé ce code, mais ça ne marche pas

Private Sub ChkB01_Click()
    'CONCERNÉ/NON-CONCERNÉ 01
    Sheets("Base").Select
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row

Set CL = Range("A2:A" & Dlig).Find(What:=Me.TBoxM1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TBoxM2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TBoxM3.Value, LookIn:=xlValues, LookAt:=xlWhole)

   If ChkB01.Value = True Then
   Sheets("Base").Range("DH" & CL.Row) = "NC"
   Else
   Sheets("Base").Range("DH" & CL.Row).ClearContents
   End If

End Sub

Bonjour,

Tu étais proche ...

Un essai ...

Private Sub ChkB01_Click()
    'CONCERNÉ/NON-CONCERNÉ 01
Dim CL1 As Range
Dim CL2 As Range
Dim CL3 As Range
Dim Dlig As Integer

Sheets("Base").Select
Dlig = Cells(Rows.Count, "A").End(xlUp).Row

Set CL1 = Range("A2:A" & Dlig).Find(What:=Me.TBoxM1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Set CL2 = Range("A2:A" & Dlig).Find(What:=Me.TBoxM2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Set CL3 = Range("A2:A" & Dlig).Find(What:=Me.TBoxM3.Value, LookIn:=xlValues, LookAt:=xlWhole)

   If ChkB01.Value = True Then
      Sheets("Base").Range("DH" & CL1.Row) = "NC"
      Sheets("Base").Range("DH" & CL2.Row) = "NC"
      Sheets("Base").Range("DH" & CL3.Row) = "NC"
   Else
      Sheets("Base").Range("DH" & CL1.Row).ClearContents
      Sheets("Base").Range("DH" & CL3.Row).ClearContents
      Sheets("Base").Range("DH" & CL3.Row).ClearContents
   End If
End Sub

ric

Merci 1000 fois, c'est parfait =)

Rechercher des sujets similaires à "simplification code vba"