Appeler une fiche à nom variable

**TugdualB** · 17/11/2015, 10h31

Bonjour à toutes et à tous,

Alors voilà, dans le cadre de mon stage, je suis en train de travailler sur une feuille de calcul Excel afin que celle-ci soit optimisée.
Ne m'y connaissant absolument pas en VBA (j'ai quelques bases en programmation cependant), j'ai cherché des codes sur internet afin de gagner du temps, mais je me retrouve maintenant bloqué.
Je vais vous coller le code juste après, mais pour vous exposer mon problème au mieux je préfère vous en parler directement.
J'ai donc commencer à rentrer un code afin que la feuille prenne automatiquement le nom de la cellule "E11" (Mis finalement ensuite, a partir de la ligne 45).
Ensuite, mon but est d'afficher/masquer certaines colonnes selon la valeur située en "O31" qui correspond elle-même à un menu déroulant.
Cependant, tout se compile bien, aucune erreur n'est détectée, mais le changement de la cellule "O31" n'a pas d'influence.

Je pense déjà avoir trouvé le problème, qui (peut-être ?) provient du fait que le nom de la feuille change et que je n'arrive donc pas à l'appeler correctement, ce qui fait que cette fonction n'a aucun problème mais n'a pas non plus d'influence sur la page qui m'intéresse.

Voici le code en question :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
 Dim KeyCells As Range
 Set KeyCells = Sheets(Feuil1).Range("O31")
 If Not Application.Intersect(KeyCells, Range(Target.Address)) _
 Is Nothing Then
 
    If Range("o31") = "" Then
    Range("a1:a200").EntireRow.Hidden = False
    Range("a35:a50").EntireRow.Hidden = True
    End If
 
    If Range("o31") = "1" Then
    Range("a1:a200").EntireRow.Hidden = False
    Range("a35:a50").EntireRow.Hidden = True
    End If
 
    If Range("o31") = "2" Then
    Range("a1:a200").EntireRow.Hidden = False
    Range("a38:a50").EntireRow.Hidden = True
    End If
 
    If Range("o31") = "3" Then
    Range("a1:a200").EntireRow.Hidden = False
    Range("a41:a50").EntireRow.Hidden = True
    End If
 
    If Range("o31") = "4" Then
    Range("a1:a200").EntireRow.Hidden = False
    Range("a44:a50").EntireRow.Hidden = True
    End If
 
    If Range("o31") = "5" Then
    Range("a1:a200").EntireRow.Hidden = False
    Range("a47:a50").EntireRow.Hidden = True
    End If
 
    If Range("o31") = "6" Then
    Range("a1:a200").EntireRow.Hidden = False
    End If
 
End If
 
    'Specify the target cell whose entry shall be the sheet tab name.
    If Target.Address <> "$E$11" Then Exit Sub
        'If the target cell is empty (contents cleared) then do not change the shet name
    If IsEmpty(Target) Then Exit Sub
 
    'If the length of the target cell's entry is greater than 31 characters, disallow the entry.
    If Len(Target.Value) > 31 Then
        MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
        "You entered " & Target.Value & ", which has " & Len(Target.Value) & " characters.", , "Keep it under 31 characters"
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
 
    'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    'Verify that none of these characters are present in the cell's entry.
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = "/"
    IllegalCharacter(2) = "\"
    IllegalCharacter(3) = "["
    IllegalCharacter(4) = "]"
    IllegalCharacter(5) = "*"
    IllegalCharacter(6) = "?"
    IllegalCharacter(7) = ":"
    For i = 1 To 7
        If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
            MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
            "Please re-enter a sheet name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
            Application.EnableEvents = False
            Target.ClearConloltents
            Application.EnableEvents = True
            Exit Sub
        End If
    Next i
 
    'Verify that the proposed sheet name does not already exist in the workbook.
    Dim strSheetName As String, wks As Worksheet, bln As Boolean
    strSheetName = Trim(Target.Value)
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(strSheetName)
    On Error Resume Next
    If Not wks Is Nothing Then
        bln = True
    Else
        bln = False
        Err.Clear
    End If
 
    'If the worksheet name does not already exist, name the active sheet as the target cell value.
    'Otherwise, advise the user that duplicate sheet names are not allowed.
    If bln = False Then
        ActiveSheet.Name = strSheetName
    Else
        MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
        "Please enter a unique name for this sheet."
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
 
End Sub

Merci d'avance de votre aide, et désolé d'avance de ce bloc assez indigeste !

**Theze** · 18/11/2015, 07h26

Bonjour,

Teste déjà de cette façon :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim strSheetName As String
    Dim wks As Worksheet
    Dim IllegalCharacter(1 To 7) As String
    Dim I As Integer
 
    If Target.Address(0, 0) = "O31" Then
 
        Select Case Target.Value
 
            Case "", 1, 2, 3, 4, 5
                Range("a1:a200").EntireRow.Hidden = False
                Range("a35:a50").EntireRow.Hidden = True
 
            Case 6
                Range("a1:a200").EntireRow.Hidden = False
 
        End Select
 
    End If
 
    'Specify the target cell whose entry shall be the sheet tab name.
    If Target.Address(0, 0) <> "E11" Then Exit Sub
 
        'If the target cell is empty (contents cleared) then do not change the shet name
    If IsEmpty(Target) Then Exit Sub
 
    'If the length of the target cell's entry is greater than 31 characters, disallow the entry.
    If Len(Target.Value) > 31 Then
        MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
        "You entered " & Target.Value & ", which has " & Len(Target.Value) & " characters.", , "Keep it under 31 characters"
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If
 
    'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    'Verify that none of these characters are present in the cell's entry.
    IllegalCharacter(1) = "/"
    IllegalCharacter(2) = "\"
    IllegalCharacter(3) = "["
    IllegalCharacter(4) = "]"
    IllegalCharacter(5) = "*"
    IllegalCharacter(6) = "?"
    IllegalCharacter(7) = ":"
 
    For I = 1 To 7
        If InStr(Target.Value, (IllegalCharacter(I))) > 0 Then
            MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
            "Please re-enter a sheet name without the ''" & IllegalCharacter(I) & "'' character.", 48, "Not a possible sheet name !!"
            Application.EnableEvents = False
            Target.ClearConloltents
            Application.EnableEvents = True
            Exit Sub
        End If
    Next I
 
    'Verify that the proposed sheet name does not already exist in the workbook.
    strSheetName = Trim(Target.Value)
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(strSheetName)
 
    'If the worksheet name does not already exist, name the active sheet as the target cell value.
    'Otherwise, advise the user that duplicate sheet names are not allowed.
    If Not wks Is Nothing Then
        MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
        "Please enter a unique name for this sheet."
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    Else
        ActiveSheet.Name = strSheetName
    End If
 
End Sub

**TugdualB** · 18/11/2015, 09h29

Merci de ton aide, mais entre temps j'ai réussi à me débloquer, j'ai revu le code que j'avais tapé et j'ai utilisé des macros que j'ai ensuite appelé selon les cas et la valeur de "O31" !

Appeler une fiche à nom variable [Toutes versions]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager