Microsoft 365 Attribuer une valeur multiple à une variable objet

ShrekL · 24 Décembre 2020

Bonjour,

J'ai cherché ici si cette question avait déjà été posée mais je n'ai pas trouvée. Donc, voici ma question en espérant que je ne répète pas trop.

Afin de monter des commandes qui s'appliquent à plusieurs feuilles, j'essai de créer une variable objet qui porterait le nom de "ToutBord" et qui désigne plusieurs feuilles dans un même classeur qui portent les nom 2X1, 2X2, 2X3, etc. Par contre, lorsque j'essai d'appliquer une commande à ma variable, j'ai toujours un message d'erreur qui m'indique "L'indice n'appartient pas à la sélection".

Voici un exemple :

VB:

    Sub Unprotect()
Dim ToutBord As Worksheet
    Set ToutBord = Sheets("2X1, 2X2, 2X3, 2X4, 2X5, 2X6, 2X7, 2X8, 2X9, 2Y1, 2Y2, 2Y3, 2Y4, 2Y5, 2Y6")
ToutBord.unprotect

End Sub

Pourtant, si je désigne une seule feuille comme variable, la commande fonctionne bien sur la dite feuille. Est-ce que c'est que la variable ne peut être multiple? Si c'est le cas, y a t'il une façon de le faire?

Un merci sincère.

mapomme · 26 Décembre 2020

Re 🙂,

ShrekL à dit:
il semble que le problème provienne du fait que je ne peut désigner plusieurs feuilles de calcul dans la fonction sheets

Pour le fun :

une variable publique ToutBord contenant les feuilles qu'on désire.
cette variable ToutBord est de type collection. La clef (key) est le nom de la feuille, l'élément (item) la feuille portant le nom de la clef.
InitToutBord au début de chaque procédure s'assure que ToutBord a bien été initialisé.

nb : 2ème fil où je semble être devenu transparent 🙄

ShrekL · 26 Décembre 2020

ShrekL à dit:
Bonjour,

J'ai cherché ici si cette question avait déjà été posée mais je n'ai pas trouvée. Donc, voici ma question en espérant que je ne répète pas trop.

Afin de monter des commandes qui s'appliquent à plusieurs feuilles, j'essai de créer une variable objet qui porterait le nom de "ToutBord" et qui désigne plusieurs feuilles dans un même classeur qui portent les nom 2X1, 2X2, 2X3, etc. Par contre, lorsque j'essai d'appliquer une commande à ma variable, j'ai toujours un message d'erreur qui m'indique "L'indice n'appartient pas à la sélection".

Voici un exemple :

VB:

Sub Unprotect() Dim ToutBord As Worksheet Set ToutBord = Sheets("2X1, 2X2, 2X3, 2X4, 2X5, 2X6, 2X7, 2X8, 2X9, 2Y1, 2Y2, 2Y3, 2Y4, 2Y5, 2Y6") ToutBord.unprotect End Sub

Pourtant, si je désigne une seule feuille comme variable, la commande fonctionne bien sur la dite feuille. Est-ce que c'est que la variable ne peut être multiple? Si c'est le cas, y a t'il une façon de le faire?

Un merci sincère.

Bon... je comprend que dans mon exemple, la variable peut sembler inutile. L'idée est de sortir la variable et de la placer au-dessus de toutes mes procédure pour ainsi l'utiliser dans toutes mes procédure ensuite. Ce que je me rend compte en lisant vos commentaires, c'est qu'il semble que ce que je cherche soit plutôt une constante et non pas une variable. Par contre, j'ai aussi essayé avec la fonction constante et cela ne fonctionne pas plus et il semble que le problème provienne du fait que je ne peut désigner plusieurs feuilles de calcul dans la fonction sheets.

Par exemple :

VB:

Const ToutBord = "2X1,2X2,2X3,2X4,2X5,2X6,2X7,2X8,2X9,2Y1,2Y2,2Y3,2Y4,2Y5,2Y6"
 
Sub OterProtection()
    sheets(ToutBord).unprotect
End Sub

Merci beaucoup à vous.

dysorthographie · 26 Décembre 2020

bonjour,
quand je parle d'absurde, ce n'est pas au sujet de la demande qui me semble légitime.

créer une variable objet qui ne prendrait en compte que les onglets rechercher reviendrait à faire qu'un mini objet Sheets et de toutes les façon il faudrait énumérer tous les onglet concernés!

désolé si je t'ai blaisé!

dans le module ThisWorkbook

VB:

Public Property Let Protection(Value As Boolean)
Const MesFeuilles = "2X1,2X2,2X3,2X4,2X5,2X6,2X7,2X8,2X9,2Y1,2Y2,2Y3,2Y4,2Y5,2Y6"
Dim x
If Value Then
   For Each x In Split(MesFeuilles, ","): Sheets(x).Protect: Next
Else
     For Each x In Split(MesFeuilles, ","): Sheets(x).Unprotect: Next
End If
End Property

dans un module standard!

Code:

Sub test()
ThisWorkbook.Protection = True
End Sub

ThisWorkbook est bien une classe ou on peut ajouter des méthode et des propriétés! on est bien dans l'ob jet de la question!

PS j'ai repris le code de mapomme

mapomme · 26 Décembre 2020

Re 🙂,

ShrekL à dit:
il semble que le problème provienne du fait que je ne peut désigner plusieurs feuilles de calcul dans la fonction sheets

Pour le fun :

une variable publique ToutBord contenant les feuilles qu'on désire.
cette variable ToutBord est de type collection. La clef (key) est le nom de la feuille, l'élément (item) la feuille portant le nom de la clef.
InitToutBord au début de chaque procédure s'assure que ToutBord a bien été initialisé.

nb : 2ème fil où je semble être devenu transparent 🙄

Staple1600 · 26 Décembre 2020

Bonsoir le fil

=>mapomme
A transparent, transparent et demi
😉

Staple1600 à dit:
Bonsoir le fil

[Précisions]
Le seul parfait qu'on peut rapprocher de Staple1600
C'est le parfait ...au café qu'il lui arrive de déguster devant son écran en écrivant de sa main libre des codes VBA faillibles (comme tout ce que produit l'être humain, car oui Staple1600 n'est pas qu'une agrafe, c'est aussi un être de chair et de sang 😉
[/Précisions]

😉

=>mapomme
Je ne suis pas pour l'exclusion.
Surtout en ce soir de Noel, je suis pour l'inclusion 😉
(sauf pour l'écriture inclusive qui heurte un peu l'enfant de primaire que je fut dans les seventies)

=>laurent950
Je te remercie pour ta sollicitude.
Mon code fonctionne parfaitement dans les conditions de mon test.
Et j'avais zappé cette histoire du protection de feuilles (puisque vain et inutile)

ShrekL · 26 Décembre 2020

mapomme à dit:
Re 🙂,

Pour le fun :

une variable publique ToutBord contenant les feuilles qu'on désire.

cette variable ToutBord est de type collection. La clef (key) est le nom de la feuille, l'élément (item) la feuille portant le nom de la clef.

InitToutBord au début de chaque procédure s'assure que ToutBord a bien été initialisé.

nb : 2ème fil où je semble être devenu transparent 🙄

C'est exactement ça. Un peu plus complexe que ce que j'espérais mais ça devait être la pensé magique de Noël qui me faisait croire que ça serait aussi simple. Haha!

Un énorme merci à tous

ShrekL · 24 Janvier 2021

Bon... Voici que je reviens à la charge avec cette demande. Je vais tenter de clarifier ma demande puisqu'il semble que ce ne soit pas clair.

Après plusieurs heures d'essais et erreur, j'ai réussi à appliquer la réponse de "mapomme" pour faire !"en partie" ce que je veux faire. Mais il me reste quelques problèmes à régler.

Pour commencer, voici "UNE PARTIE" de mon projet VBA (je ne veux pas tout mettre pour éviter de se perdre):

VB:

Const MesBordereaux = "2X1,2X2,2X3,2X4,2X5,2X6,2X7,2X8,2X9,2Y1,2Y2,2Y3,2Y4,2Y5,2Y6"
Const Ajustements = "Ajustement carburant,Ajustement bitume,Ajustement acier"
Const AutresFeuilles = "LISTES,INSTRUCTION,INFO PROJET,SOMMAIRE DES BORDEREAUX"

Sub Enlev_protect_tous_onglets()
'
' ENLEVER LA PROTECTION SUR TOUS LES ONGLETS
'
Dim x
For Each x In Split(MesBordereaux, ",")
Sheets(x).Unprotect
Next

Dim y
For Each y In Split(Ajustements, ",")
Sheets(y).Unprotect
Next

Dim z
For Each z In Split(AutresFeuilles, ",")
Sheets(z).Unprotect
Next


End Sub

Sub Protéger_tous_onglets()
' PROTÉGER TOUS LES ONGLETS
'
Dim x
For Each x In Split(MesBordereaux, ",")
Sheets(x).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next

Dim y
For Each y In Split(Ajustements, ",")
Sheets(y).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next

Dim z
For Each z In Split(AutresFeuilles, ",")
Sheets(z).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next


Sheets("2X1").Select

End Sub

Il faut aussi comprendre qu'il y a plusieurs autres commandes que je veux appliquer seulement aux "MesBordereaux" et d'autres que j'appliquerai seulement à "Ajustement". Par exemple, certaines commandes demanderont de masquer ou afficher certaines colonnes dans "MesBordereaux". Voilà donc pour quoi de tien à scinder en plusieurs constantes.

Voici les problèmes qui me restent à résoudre :
1 - Les feuilles dans "MesBordereaux" seront renommées et je ne peux savoir le nom qu'elles porteront. Je devrais donc appeler les noms de feuille d'origine (avant la parenthèse dans la liste d'objet Excel) comme "Feuil1 (2X1), Feuil6 (2X2), Feuil7 (2X3), etc." afin que peu importe le nom qu'elle porte, la constante désigne toujours le bon objet. Est-ce possible?

2 - Dans mon code plus haut, je me doute bien qu'il y a une possibilité de "simplifier" davantage l'appel des commandes. Je pensais à quelque chose du genre :

Code:

Sub Enlev_protect_tous_onglets()
'

Dim x, y, z
For Each x In Split(MesBordereaux, ",")
For Each y In Split(Ajustements, ",")
For Each z In Split(AutresFeuilles, ",")
Sheets(x).Unprotect
Sheets(y).Unprotect
Sheets(z).Unprotect
Next


End Sub

Mais ça me donne un "bug"

Je suis donc bien ouvert aux suggestions.

3- Pour finir, je me demandais si vous étiez en mesure de m'aider à comprendre la syntaxe de base proposée par "mapomme"

Code:

Const MesFeuilles = "2X1,2X2,2X3,2X4,2X5,2X6,2X7,2X8,2X9,2Y1,2Y2,2Y3,2Y4,2Y5,2Y6"
Dim x
For Each x In Split(MesFeuilles, ","): Sheets(x).Unprotect: Next

J'ai l'impression de spécifier une variable pour désigner une constante. N'y a-t-il pas moyen d'appliquer une commande directement à la constante? Du genre :

Code:

Sub Enlev_Protect()
Sheets(MesFeuilles).Unprotec
End Sub

Je sais que c'est très lourd comme question, mais soyez assuré que j'apprécie votre aide et vos commentaires constructifs.

Salutations,

Staple1600 · 24 Janvier 2021

Re

En rédigeant le code ainsi, cela fonctionnera

VB:

Sub Enlev_protect_tous_onglets()
MesBordereaux = "Feuil1,Feuil2"
Ajustements = "Feuil2,Feuil4"
t = Split(MesBordereaux, ",")
tt = Split(Ajustements, ",")
For x = LBound(t) To UBound(t)
Sheets(t(x)).Unprotect
Sheets(t(x)).Unprotect
Next
End Sub

NB: Ici cela fonctionne avec une seule boucle parce il y a le même nombre d'éléments dans les deux tableaux t et tt.

NB: J'ai testé avec un classeur contenant 4 feuilles.

Patrice33740 · 25 Janvier 2021

Bonjour,

Vu que le nombre de feuille de chaque tableau est différent il n'y a que peu de simplification :

VB:

Option Explicit
Const MesBordereaux = "2X1,2X2,2X3,2X4,2X5,2X6,2X7,2X8,2X9,2Y1,2Y2,2Y3,2Y4,2Y5,2Y6"
Const Ajustements = "Ajustement carburant,Ajustement bitume,Ajustement acier"
Const AutresFeuilles = "LISTES,INSTRUCTION,INFO PROJET,SOMMAIRE DES BORDEREAUX"

Sub Enlev_protect_tous_onglets()
'
' ENLEVER LA PROTECTION SUR TOUS LES ONGLETS
'
Dim x
  For Each x In Split(MesBordereaux, ","): Sheets(x).Unprotect: Next
  For Each x In Split(Ajustements, ","): Sheets(x).Unprotect: Next
  For Each x In Split(Ajustements, ","): Sheets(x).Unprotect: Next

End Sub

Sub Protéger_tous_onglets()
' PROTÉGER TOUS LES ONGLETS
'
Dim x
  For Each x In Split(MesBordereaux, ","): Sheets(x).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True: Next
  For Each x In Split(Ajustements, ","): Sheets(x).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True: Next
  For Each x In Split(AutresFeuilles, ","): Sheets(x).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True: Next

End Sub

eriiic · 26 Janvier 2021

Bonjour à tous,

puisque tu es appelé à renommer tes feuilles, tu peux aussi changer leur codename à leur création pour les reconnaitre facilement ensuite.
Par exemple pour les bordereaux :

VB:

sh.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = "B_" & i + 1

Il te suffit, en bouclant sur toutes les feuilles, de regarder la gauche du codename ("B_") pour les traiter tous.
eric

Microsoft 365 Attribuer une valeur multiple à une variable objet

ShrekL

XLDnaute Junior

mapomme

ShrekL

XLDnaute Junior

dysorthographie

XLDnaute Accro

mapomme

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

ShrekL

XLDnaute Junior

ShrekL

XLDnaute Junior

Staple1600

XLDnaute Barbatruc

Patrice33740

XLDnaute Impliqué

eriiic

XLDnaute Barbatruc

Discussions similaires

Microsoft 365 Attribuer une valeur multiple à une variable objet

XLDnaute Junior

XLDnaute Junior

XLDnaute Accro

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Junior

XLDnaute Junior

XLDnaute Barbatruc

XLDnaute Impliqué

XLDnaute Barbatruc

Discussions similaires

Privacy & Transparency

Privacy & Transparency