VBA Erreur d'exécution 13

stayshen à dit:

bonjour;
étant donné que je suis un novice avec vba, je vous prie de bien vouloir m'aider à régler ce problème,
alors j'ai créé un vba afin que je puisse afficher et masquer des colonne selon le contenu de cases précises, le programme marche bien, mais quand je veux supprimer la valeur d'une case avec "supp" le message d'erreur "erreur d'exécution 13" s'affiche et les la ligne "If Target.Address = "$A$10" And Target.Value = 0 Then" est surlignée,


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("C3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$10" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("C3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$40" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("D3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$40" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("D3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$70" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("E3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$70" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("E3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$100" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("F3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$100" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("F3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$130" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("G3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$130" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("G3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$160" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("H3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$160" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("H3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$190" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("I3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$190" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("I3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$220" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("J3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$220" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("J3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$250" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("K3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$250" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("K3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$280" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("L3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$280" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("L3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$310" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("M3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$310" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("M3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$340" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("N3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$340" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("N3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$370" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("O3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$370" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("GO").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$400" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("P3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$400" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("P3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$430" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("Q3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$430" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("Q3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$460" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("R3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$460" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("R3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$490" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("S3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$490" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("S3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$520" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("T3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$520" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("T3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$550" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("U3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$550" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("U3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$580" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("V3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$580" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("V3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$610" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("W3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$610" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("W3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$640" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("X3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$640" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("X3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$670" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("Y3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$670" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("Y3").EntireColumn.Hidden = False
End If
End If
If Target.Address = "$A$700" And Target.Value = 0 Then
Sheets("hiérarchisation AE").Range("Z3").EntireColumn.Hidden = True
Else
If Target.Address = "$A$700" And Target.Value <> 0 Then
Sheets("hiérarchisation AE").Range("Z3").EntireColumn.Hidden = False
End If
End If
End Sub

Cliquez pour agrandir...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
  For i = 10 To 790 Step 30
    Sheets("hiérarchisation AE").Columns(Int(i / 30 + 3)).Hidden = Cells(i, 1) = ""
  Next
End If
End Sub

Jacky67 à dit:

Bonjour,
Essaye ceci

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
  For i = 10 To 790 Step 30
    Sheets("hiérarchisation AE").Columns(Int(i / 30 + 3)).Hidden = Cells(i, 1) = ""
  Next
End If
End Sub

Cliquez pour agrandir...

Jacky67 à dit:

Re,
Essaye comme ceci

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheets("hiérarchisation AE")
        .Cells.EntireColumn.AutoFit
        .Cells.EntireRow.AutoFit
        If Target.Column = 1 Then
                For i = 10 To 790 Step 30
                .Columns(Int(i / 30 + 3)).Hidden = Cells(i, 1) = ""
            Next
        End If
    End With
End Sub

Cliquez pour agrandir...

Jacky67 à dit:

Re..
Mettre le classeur en ligne avec ce disfonctionnement.

Cliquez pour agrandir...

Jacky67 à dit:

RE..
Je n'éprouve aucune difficulté à masquer/afficher les colonnes de la feuille "hiérarchisation AE" selon que la colonne A de la feuille "Evaluation des AE" est renseignée.

Cliquez pour agrandir...