Discussion :

[davimaje]

Bonjour,

Je suis nouveau sur ce forum et plutôt débutant en VBA. Avant de poster cette demande, j'ai cherché dans le forum, mais n'ai pas trouvé de réponse à ma problématique.

Dans le fichier ci-joint j'ai 4 feuilles, avec 4 macros dans la feuille 1 (que je vais nommer ci-dessous macro 1, 2 et 3) et 1 macro (que je vais nommer ci-dessous macro 4) dans chacune des autres feuilles.
Ces macros sont des modules utilisant des "contrôle de formulaire"
Dans la feuille 1 : la macro 1 sous "Case n°" me sert à sélectionner l'utilisation de la feuille 2, de la feuille 3 ou de la feuille 4
La macro 2 sous "Number of option" me sert à sélectionner le nombre d'option (de 1 à 3) et d'afficher dans les feuilles 1 et 2 ou 3 ou 4 le nombre d'options sélectionnées
La macro 3 sous "Qty per option" me sert à sélectionner le nombre de quantité à traiter de 1 à 6 et d'afficher dans dans les feuilles 1 et 2 ou 3 ou 4 les colonnes quantité 3 à 6
La macro 4 est présente à l'identique dans les 4 feuilles. Elle me permet de choisir d'afficher dans chaque feuille et/ou les options 1, 2, 3 et/ou toutes.

Il y a sûrement moyen d'améliorer le code de ces macro que j'ai faites avec mes petites connaissances en langage VBA, et avec ma logique ...

Mon premier problème est le plus important : ce fichier est un outil destiné à des utilisateurs qui doivent saisir certains champs (cellules blanches encadrées de bleu dans la feuille 1 et cellules bleues dans les autres feuilles). Les autres cellules de la feuille contiendront des informations et/ou des formules qui sont protégées par un mot de passe (le mot de passe est "test" dans le cas présent). Pour l'instant je n'arrive pas à insérer un code qui me permette de faire fonctionner ces macros lorsque la feuille est protégée. J'ai lu pas de mal de choses sur plusieurs forum, mais je ne trouve toujours pas la solution.
J'ai essayé de mettre dans le module "ThisWorkBook" le code suivant sans succès :

Code :

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

1
2
3
4
5
6
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="test", UserInterfaceOnly:=True
Next ws
End Sub

J'ai également essayé d'intégrer le code suivant dans les modules :

Code :

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

1
2
3
4
5
Sub Case_Option_Qty()
ActiveSheet.Unprotect "test"
'mes instructions
ActiveSheet.Protect "test", True, True, True
End Sub

et j'en passe ... mais impossible d'utiliser les macros, une fois la 1ère exécutée.

L'un d'entre vous aurait-il une idée ?

Je cherche également une amélioration à l'affichage des colonnes quantités 4, 5 et/ou 6 lorsqu'elles sont masquées par la macro. Je souhaiterais encadrer en gras noir les options, telles qu'elles sont présentées lorsque les 6 sont sélectionnées. Je pense que ce n'est pas trop compliqué, mais je ne sais comment faire et n'ai pas eu trop le temps de chercher.

Merci d'avance pour vos réponses

[Daniel.C]

Bonjour,

1. ce code fonctionne :

Code :

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

1
2
3
4
5
6
7
8
Private Sub Workbook_Open()
Dim ws As Worksheet
 
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="test", UserInterfaceOnly:=True
Next ws
 
End Sub

2. Je ne trouve pas d'instruction "UnProtect" dans la macro "Case_Option_Qty".

[davimaje]

Bonsoir Daniel C.

Merci de votre réponse

Cependant même en mettant votre code dans "ThisWorkbook" ça ne fonctionne toujours pas.

Effectivement il n'y a pas de Unprotect dans le module "Case_Option_Qty()" car après plusieurs essais, je l'ai enlevé suite à des bug "erreur d'exécution '1004'"

Je viens de réessayer en ajoutant Unprotect dans le module "Case_Option_Qty()" et votre code dans "ThisWorkbook", ça ne fonctionne pas non plus. J'ai à nouveau des erreurs "erreur d'exécution '1004'"

Si vous voyez autre chose ?

Merci

[Daniel.C]

Cependant même en mettant votre code dans "ThisWorkbook" ça ne fonctionne toujours pas.

Quel est le message d'erreur ?

Quant à la macro, je n'ai pas d'erreur sur le "UnProtect".

[davimaje]

OK ça fonctionne effectivement, sauf que la feuille est déverrouillée, ce que je ne souhaite pas bien sûr.

Et si j'écris le code :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
Sub Case_Option_Qty()
 
ActiveSheet.Unprotect "test"
 
'instruction
 
ActiveSheet.Protect "test"
 
End Sub

dans ce cas la feuille est à nouveau verrouillée et les macros ne sont plus utilisables

[Daniel.C]

dans ce cas la feuille est à nouveau verrouillée et les macros ne sont plus utilisables

Comme tu as précisé :

Code :

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

ws.Protect Password:="test", UserInterfaceOnly:=True

dans "Workbook_Open", tu ne dois pas avoir besoin de déprotéger la feuille. Par contre,si tu déprotèges la feuille il faut la re-protéger en spécifiant "UserInterfaceOnly:=True".

[davimaje]

Bonjour Daniel,

Comme tu me dis

dans "Workbook_Open", tu ne dois pas avoir besoin de déprotéger la feuille

j'ai donc enlevé le Unprotect du module "Case_Option_Qty()"

Néanmoins, lorsque la feuille est protégée (puisqu'elle doit l'être pour protéger un certain nombre de cellules) les macros ne veulent pas fonctionner. Lorsque je veux sélectionner un élément d'une des listes déroulantes (Case n°, Number of option, Qty per option ou Display option) j'ai le message :

La cellule ou le graphique est protégé et en lecture seule.
Pour modifier une cellule ou un graphique protégé, ôtez la protection avec la commande Ôter la protection de la feuille (onglet Révision, groupe Modification). Vous devrez peut-être taper votre mot de passe.

Si j'ai bien compris, normalement la commande "UserInterfaceOnly:=True" sert à utiliser les macros même si la feuille est verrouillée, c'est bien cela ?

Si c'est le cas, ça ne fonctionne pas chez moi et je ne comprends pas pourquoi, aurai-je oublié un paramètre dans la sécurité des macros ou autre ?

[Daniel.C]

Bonjour,

Néanmoins, lorsque la feuille est protégée (puisqu'elle doit l'être pour protéger un certain nombre de cellules) les macros ne veulent pas fonctionner. Lorsque je veux sélectionner un élément d'une des listes déroulantes (Case n°, Number of option, Qty per option ou Display option) j'ai le message :

La cellule ou le graphique est protégé et en lecture seule.
Pour modifier une cellule ou un graphique protégé, ôtez la protection avec la commande Ôter la protection de la feuille (onglet Révision, groupe Modification). Vous devrez peut-être taper votre mot de passe.

Ce message n'est pas provoqué par la macro. La cellule liée à la zone combinée en A6 est W2. Cette cellule étant verrouillée, elle est protégée et provoque l'erreur quand tu effectues un choix dans la zone combinée. Cette erreur se produit avant le déclenchement de la macro, donc ça ne sert à rien de mettre "UnProtect" dans celle-ci. Déverrouille la cellule W2 et, au besoin, masque la colonne W.

Si j'ai bien compris, normalement la commande "UserInterfaceOnly:=True" sert à utiliser les macros même si la feuille est verrouillée, c'est bien cela ?

Exactement.

[davimaje]

Mais oui, bien sûr ... c'est assez logique, comment n'y ai-je pas pensé

Merci énormément Daniel de ton aide précieuse

Daniel,

Je cherche également une amélioration à l'affichage des colonnes quantités 4, 5 et/ou 6 lorsqu'elles sont masquées par les macros.
Je souhaiterais encadrer en gras noir les options, telles qu'elles sont présentées lorsque les 6 sont sélectionnées.
Cette affichage avec ces bordures me permettrait de bien séparer les options.
Pourrais-tu me donner un code pour faire cela ?

Merci d'avance de ton aide

[Daniel.C]

Est-ce que tu peux me donner une idée de ce que tu souhaites ? Donne-moi un exemple (colonnes masquées, bordures voulues). Merci.

[davimaje]

Par exemple lorsque je sélectionne la valeur 1 dans "Case n°", puis 2 dans "Number of option", puis 4 dans "Qty per option", je souhaiterais un cadre en gras noir sur le bloque de cellules D10 à G51 et J10 à M51 comme suit :

[Daniel.C]

A tester voir si ça fonctionne dans tous les cas :

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
Sub test()
    Dim Opt As Integer, NbOption As Integer, Qté As Integer
    '*** initialisatiion des bordures
    'toutes les bordures gauches
    [D10:U51].Borders(xlEdgeLeft).Weight = -4138
    'bordure droite de la colonne U
    [U10:U51].Borders(xlEdgeLeft).Weight = 4
    'bordure gauche de la colonne D
    [D10:D51].Borders(xlEdgeLeft).Weight = 4
    Opt = Application.Index([X1:X4], [W2])
    NbOption = Application.Index([Z2:Z4], [Y2])
    Qté = Application.Index([AB2:AB5], [AA2])
    col = 4 + (Opt - 1) * 6
    Range(Cells(10, col), Cells(51, col)).Resize(, NbOption * 6 - (6 - Qté)).BorderAround _
        Weight:=xlThick, LineStyle:=xlContinuous, ColorIndex:=xlColorIndexAutomatic
End Sub

[davimaje]

Bonsoir Daniel,

Merci encore de ton aide, car je serais bien incapable aujourd'hui d'écrire un tel code.

Ça fonctionne plutôt pas mal, sauf que par exemple, si je choisis 2 dans "number of option" et 4 dans "Qty per option", puis 3 dans "number of option" et 5 dans "Qty per option", des bordures se sont crées entre la colonne M11:M51 et N11:N51 ...


Peut-être est-il possible de réinitialiser la bordure droite qui a été créée lors des 1ers choix (2 dans "number of option" et 4 dans "Qty per option") puis de la recréer ?

D'ailleurs à quel endroit dois-je mettre ce code ? Puis-je l'ajouter avant le "End Sub" du code "Case_Option_Qty" comme suit ?

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
Sub Case_Option_Qty()
'instructions
End Select
Dim Opt As Integer, NbOption As Integer, Qté As Integer
    '*** initialisatiion des bordures
    'toutes les bordures gauches
    [D10:U51].Borders(xlEdgeLeft).Weight = -4138
    'bordure droite de la colonne U
    [U10:U51].Borders(xlEdgeLeft).Weight = 4
    'bordure gauche de la colonne D
    [D10:D51].Borders(xlEdgeLeft).Weight = 4
    Opt = Application.Index([X1:X4], [W2])
    NbOption = Application.Index([Z2:Z4], [Y2])
    Qté = Application.Index([AB2:AB5], [AA2])
    col = 4 + (Opt - 1) * 6
    Range(Cells(10, col), Cells(51, col)).Resize(, NbOption * 6 - (6 - Qté)).BorderAround _
        Weight:=xlThick, LineStyle:=xlContinuous, ColorIndex:=xlColorIndexAutomatic
End Sub

Merci

[Daniel.C]

Bonjour,

Essaie comme ça :

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
Sub test()
    Dim Opt As Integer, NbOption As Integer, Qté As Integer, Col As Integer
    '*** initialisatiion des bordures
    'toutes les bordures gauches
    [D10:U51].Borders(xlEdgeLeft).Weight = xlMedium
    [D10:U51].Borders(xlEdgeRight).Weight = xlMedium
    [D10:U51].Borders(xlInsideVertical).Weight = xlMedium
 
    'bordure droite de la colonne U
    [U10:U51].Borders(xlEdgeRight).Weight = 4
    'bordure gauche de la colonne D
    [D10:D51].Borders(xlEdgeLeft).Weight = 4
    Opt = Application.Index([X1:X4], [W2])
    NbOption = Application.Index([Z2:Z4], [Y2])
    Qté = Application.Index([AB2:AB5], [AA2])
'    col = 8 + (NbOption - 1) * 6
    Select Case NbOption
        Case 1: Col = 4
        Case 2: Col = 10
        Case 3: Col = 16
    End Select
    Col = Col + Qté - 1
    For i = 1 To NbOption * 6 Step 6
        If (4 + i - 1) > Col Then Exit For
        Range(Cells(10, 4 + i - 1), Cells(51, 4 + i - 1)).Borders(xlEdgeLeft).Weight = 4
    Next i
    Range(Cells(10, 4), Cells(51, Col)).BorderAround _
        Weight:=xlThick, LineStyle:=xlContinuous, ColorIndex:=xlColorIndexAutomatic
End Sub

D'ailleurs à quel endroit dois-je mettre ce code ? Puis-je l'ajouter avant le "End Sub" du code "Case_Option_Qty" comme suit ?

Tu dois le mettre à la fin de chaque macro attachées aux listes déroulantes.

[davimaje]

Bonjour Daniel,

Merci pour ce code qui fonctionne à merveille sur la feuille 1 et à condition de préciser que ça doit s'appliquer sur cette feuille comme suit:

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
Dim Opt As String, NbOption As Integer, Qté As Integer, Col As Integer
    '*** initialisatiion des bordures
    'toutes les bordures gauches
    Sheets("Feuille 1").[D10:U51].Borders(xlEdgeLeft).Weight = xlMedium
    Sheets("Feuille 1").[D10:U51].Borders(xlEdgeRight).Weight = xlMedium
    Sheets("Feuille 1").[D10:U51].Borders(xlInsideVertical).Weight = xlMedium
 
    'bordure droite de la colonne U
    Sheets("Feuille 1").[U10:U51].Borders(xlEdgeRight).Weight = 4
    'bordure gauche de la colonne D
    Sheets("Feuille 1").[D10:D51].Borders(xlEdgeLeft).Weight = 4
    Opt = Sheets("Feuille 1").Application.Index([X1:X4], [W2])
    NbOption = Sheets("Feuille 1").Application.Index([Z2:Z4], [Y2])
    Qté = Sheets("Feuille 1").Application.Index([AB2:AB5], [AA2])
'    col = 8 + (NbOption - 1) * 6
    Select Case NbOption
        Case 1: Col = 4
        Case 2: Col = 10
        Case 3: Col = 16
    End Select
    Col = Col + Qté - 1
    For i = 1 To NbOption * 6 Step 6
        If (4 + i - 1) > Col Then Exit For
        Sheets("Feuille 1").Range(Cells(10, 4 + i - 1), Cells(51, 4 + i - 1)).Borders(xlEdgeLeft).Weight = 4
    Next i
    Sheets("Feuille 1").Range(Cells(10, 4), Cells(51, Col)).BorderAround _
        Weight:=xlThick, LineStyle:=xlContinuous, ColorIndex:=xlColorIndexAutomatic

j'ai modifié la liste de choix pour les feuilles 2 à 4, en "choix 1", "Choix 2" et "Choix 3" et du coup j'ai modifié "Opt As Interger" en "Opt As String"

Les feuilles 2, 3 et 4 ne sont pas présentées de la même manière, la colonne D de la feuille 1 correspond à la colonne F dans les 3 autres feuilles ...
j'ai essayé de modifier le code pour qu'il fonctionne également sur les feuilles 2, 3 et 4, mais je n'y arrive pas

D'autre part, j'ai essayé aussi de coller ce code dans le module "Test.xlsm!Zonecombinée22_QuandChangement", car lorsque je choisis d'afficher l'une des options, le cadre de droite n'apparaît pas toujours, mais bon ça c'est moins gênant pour moi.

Merci encore de ton aide

[Daniel.C]

Bonjour,

Les feuilles 2, 3 et 4 ne sont pas présentées de la même manière, la colonne D de la feuille 1 correspond à la colonne F dans les 3 autres feuilles ...

Oui. De plus, la ligne d'entêtes "Option x" n'est pas la même non plus...

lorsque je choisis d'afficher l'une des options, le cadre de droite n'apparaît pas toujours, mais bon ça c'est moins gênant pour moi.

Est-ce que tu peux me donner les valeurs pour lesquelles cela see produit ?

[davimaje]

Oui. De plus, la ligne d'entêtes "Option x" n'est pas la même non plus...

Exact

lorsque je choisis d'afficher l'une des options, le cadre de droite n'apparaît pas toujours, mais bon ça c'est moins gênant pour moi.

Est-ce que tu peux me donner les valeurs pour lesquelles cela see produit ?

Par exemple, lorsque je fais les choix suivants :
"Number of option" = 3, "Qty per option" = 4
Si je choisis d'afficher l'option 1 ou 2, la bordure de droite ne s'affiche pas en gras. Si je choisis l'option 3, pas de problème la bordure s'affiche correctement.

[Daniel.C]

Utilise cette macro :

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
Sub test()
    Dim Opt As Integer, NbOption As Integer, Qté As Integer, Col As Integer
    Dim DecalH As Byte, DecalV As Byte
    Opt = ['Feuille 1'!w2] - 1
    Select Case Opt
        Case 1: DecalH = 0: DecalV = 0
        Case 2: DecalH = 2: DecalV = 2
        Case 3: DecalH = 2: DecalV = -4
        Case 4: DecalH = 2: DecalV = 2
    End Select
    '*** initialisatiion des bordures
    'toutes les bordures gauches
    With Sheets("Feuille " & Opt)
 
        .[D10:U51].Offset(DecalV, DecalH).Borders(xlEdgeLeft).Weight = xlMedium
        .[D10:U51].Offset(DecalV, DecalH).Borders(xlEdgeRight).Weight = xlMedium
        .[D10:U51].Offset(DecalV, DecalH).Borders(xlInsideVertical).Weight = xlMedium
 
        'bordure droite de la colonne U
        .[U10:U51].Offset(DecalV, DecalH).Borders(xlEdgeRight).Weight = 4
        'bordure gauche de la colonne D
        .[D10:D51].Offset(DecalV, DecalH).Borders(xlEdgeLeft).Weight = 4
        NbOption = Application.Index(['Feuille 1'!Z2:Z4], ['Feuille 1'!Y2])
        Qté = Application.Index(['Feuille 1'!AB2:AB5], ['Feuille 1'!AA2])
        Select Case NbOption
            Case 1: Col = 4
            Case 2: Col = 10
            Case 3: Col = 16
        End Select
        Col = Col + Qté - 1
        For i = 1 To NbOption * 6 Step 6
            If (4 + i - 1) > Col Then Exit For
            .Range(.Cells(10, 4 + i - 1), .Cells(51, 4 + i - 1)).Offset(DecalV, DecalH).Borders(xlEdgeLeft).Weight = 4
        Next i
        .Range(.Cells(10, 4), .Cells(51, Col)).Offset(DecalV, DecalH).BorderAround _
            Weight:=xlThick, LineStyle:=xlContinuous, ColorIndex:=xlColorIndexAutomatic
        End With
End Sub

[Daniel.C]

Si je choisis d'afficher l'option 1 ou 2...

C'est quoi, "l'option" par rapport au "Number of option" ?

[davimaje]

C'est quoi, "l'option" par rapport au "Number of option" ?

"Number of option" = nombre d'option possible de 1 à 3
"option" = la même chose, mais cette liste d'afficher soit toutes les options (1 à 3) ou l'option 1 ou l'option 2 ou l'option 3.
Ce tableau étant assez large (jusqu'à 6 colonnes par option), j'ai fait cette liste pour que les utilisateurs puissent travailler plus facilement en n'affichant qu'une option à la fois s'ils le souhaitent