Microsoft 365 Générer bouton commande sur l'ensemble des Feuils d'un classeur

[spike29]

Bonjour,

J'ai un code qui permet de créer un bouton de commande sur la Feuil active. J'aimerai modifier ce code afin qu'il génère ce bouton de commande sur l'ensemble des Feuils du classeur.

J'ai un début de piste avec :

 For i = 1 To Sheets.Count
 
 ' ****code****
 
 Set objBut = Sheets(i).Buttons.Add(PosG, PosH, Longueur, Hauteur)

Ma maitrise des boucles étant ce qu'elle est, je vous demande un coup de pouce sur le sujet.

Code fonctionnel que j'utilise pour insérer le bouton dans la Feuil active

Sub InsererDesBoutons()

Dim objBut As Object

Dim i As Integer
LigDeb = 2

 
 With Range("B" & LigDeb)
    PosG = .Left
    PosH = .Top
    End With
Hauteur = 50
Longueur = 150

 ActiveSheet.Unprotect Password:="sandman"
    ActiveWorkbook.Unprotect Password:="sandman"


With ActiveSheet.Buttons.Add(PosG, PosH, Longueur, Hauteur)
    .OnAction = "ArrêtProtec"                           'Nom d'un sub
    .Caption = "Déprotéger Feuil"
End With


End Sub

En PJ un fichier d'exemple avec mon code.

Une fois le code fonctionnel je le placerai dans une procédure évènementiel Workbook_open.


Merci d'avance pour votre aide sur le sujet.

Bonne journée

 

[juvaxe]

Bonjour

Ton VBA est protégé, donc impossible d'y regarder ...

As-tu envisagé de créer une Barre de Menu (CommandBar) qui serait valable pour tout le classeur et qui éviterait d'encombrer les feuilles avec un bouton qui est en fait le même partout.


Sinon tu as la boucle suivante qui pourrait correspondre à ta demande :

For Each Feuil In ThisWorkbook.Worksheets
'
'
Next Feuil

Bonne réception

 

[Bernard_XLD]

Bonjour Spike29, juvaxe, le forum

juvaxe, il y a assez d'explications et de code pour se passer du classeur

Bien cordialement, @+

Sub InsererDesBoutons()
    Dim objBut As Object
    Dim i As Integer
    LigDeb = 2
    With ActiveWorkbook
        .Unprotect Password:="sandman"
        For i = 1 To .Worksheets.Count
            With .Worksheets(i)
                With .Range("B" & LigDeb)
                    PosG = .Left
                    PosH = .Top
                End With
                Hauteur = 50
                Longueur = 150
                .Unprotect Password:="sandman"
                With .Buttons.Add(PosG, PosH, Longueur, Hauteur)
                    .OnAction = "ArrêtProtec"                           'Nom d'un sub
                    .Caption = "Déprotéger Feuil"
                End With
            End With
        Next i
    End With
End Sub

 

[Bernard_XLD]

re le fil

et comme tu veux faire cela dans une évènementielle Workbook_open, moi, je renommerai le bouton à sa création pour pouvoir tester son existence avant d'en recréer un à chaque ouverture de ton fichier

Bien cordialement, @+

Sub InsererDesBoutons()
    Dim objBut As Object
    Dim i As Integer
    Dim Test_Buttons As Boolean
    ligdeb = 2
    With ThisWorkbook
        .Unprotect Password:="sandman"
        For i = 1 To .Worksheets.Count
            With .Worksheets(i)
                With .Range("B" & ligdeb)
                    PosG = .Left
                    PosH = .Top
                End With
                Hauteur = 50
                Longueur = 150
                If Not .Buttons.Count = 0 Then
                    For Each objBut In .Buttons
                        If objBut.Name = "Deprotect" Then Test_Buttons = True: Exit For
                    Next objBut
                End If
                If Not Test_Buttons Then
                    .Unprotect Password:="sandman"
                    Set objBut = .Buttons.Add(PosG, PosH, Longueur, Hauteur)
                    With objBut
                        .Name = "Deprotect"
                        .OnAction = "ArrêtProtec"                           'Nom d'un sub
                        .Caption = "Déprotéger Feuil"
                    End With
                End If
            End With
        Next i
    End With
End Sub

 

[spike29]

Bonsoir à tous et un grand merci pour vos réponses !

@Yeahou c'est parfaitement ce qu'il me fallait ! Au top ! Le fait de tester l'existence du bouton à chaque fois est super, cela évite d'avoir une succession de bouton les uns à la suite des autres.

@juvaxe, le fait d'associer mon code à un élément de la barre d'outils que l'on rajouterai (par exemple une clef) était mon idée de base.
Cela fonctionne sur mon classeur, en revanche dès qu'un autre utilisateur ouvre le fichier l'élément que j'avais créé manuellement dans sur la barre d'outils (picto de clef) n'est plus présent.
Il ne peut donc pas exécuter le code associer (en l'occurrence une déprotection du workbook).

Ma solution du bouton de commande directement dans la Feuil est plus une solution par défaut que j'avais imaginé, mais à l'initial je cherchais bien un moyen d'associer systématiquement et pour l'ensemble des utilisateurs du fichier ma macro avec une des commandbar de la barre de tâches (voir en créer une par défaut via du VBA et lui associer le code souhaité).

Pensez vous que ça puisse être réalisable ?

Merci d'avance pour vos réponses et bonne fin de journée

 

[Bernard_XLD]

Bonjour

"Pensez vous que ça puisse être réalisable ?"
Bien sur, mais il faudra activer les macros pour que le fichier fonctionne, le nom de ta macro, c'est "ArrêtProtec" ?
[édition: on peut le faire pour un fichier sans toucher à la config d'Excel ou aux barres d'outils personnalisées, si tu veux, je modifie ton fichier exemple (que je n'ai pas ouvert et que je n'ai toujours pas besoin d'ouvrir), je t'explique comment modifier VBA pour l'utiliser et comment modifier le code que j'aurai intégré dans ton classeur]

Cordialement, @+

 

[juvaxe]

Bonjour

Pour la barre d'outil personnalisée ma proposition n'était pas de créer manuellement un bouton supplémentaire dans une barre d'outils existante; ce qui amène effectivement à l'inconvénient que tu évoques.

Ma proposition était de compléter le "WorkBook_Open" pour créer une barre d'outils dite Complémentaire (supplémentaire en fait) contenant les seuls boutons nécessaires au fonctionnement spécifique du Classeur en cours. Cette solution est portable sans problème.

Cette barre d'outils, créée à l'ouverture est supprimée automatiquement par Excel à la fermeture du classeur.

Si tu veux que je complète ton exemple, merci de me donner le moyen d'accéder à ton VBA.

Bonne réception

 

[spike29]

Bonjour à tous les deux et merci pour vos retours.

@Yeahou, oui le nom de la macro c'est bien "ArrêtProtec"

@juvaxe , la solution que tu décris me paraît pleinement répondre à mes besoins

Je vous met en PJ le fichier avec l'accès à mon VBA.

Bon début d'après-midi et encore merci.

 

[Bernard_XLD]

Bonjour le fil, le forum

@juvaxe , la solution que tu décris me paraît pleinement répondre à mes besoins

je le laisse faire.

Bien cordialement, @+

 

[spike29]

Moi qui pensais que c'était impossible d'influer sur le ruban.

J'attend avec impatience de voir comment faire

@Yeahou , vous semblez avoir la même piste toi et juvax (d'où mon commentaire peut-être maladroit) mais si tu as une piste je suis largement preneur.

Bonne soirée à vous.

 

[juvaxe]

Bonjour

Voilà .. la solution arrive.

En retour la version de ton fichier complétée de ma proposition.

Je n'ai pas touché à ce que tu as fait, Dans le workbook_open j'ai seulement mis un appel à la création d'un menu complémentaire; ensuite j'ai tout installé dans un nouveau module qui porte mon peudo.

A toi de faire le tri entre ce que tu veux garder et supprimer ...

J'ai conservé le mot de passe que tu avais mis; j'ai également proposé deux façons de mettre les boutons en place dans la barre de menu; c'est à titre d'exemple en cas de reprise de la solution pour une autre application.

A l'ouverture du classeur il y a "Compléments" qui vient s'ajouter dans le menu principal; il n'y a qu'à cliquer dessus. Le nouveau menu reste en place jusqu'à la fermeture d'Excel, ensuite il disparaît comme il est venu.

A ta disposition en cas de besoin. J'ai mis des commentaires ... ceux qui ont l'expérience savent qu'on y comprend jamais rien !!!

Bonne réception

 

[spike29]

Bonsoir,

Super solution. ça fonctionne parfaitement et les commentaires sont aux top !
Un grand merci à tous les deux de vous être penchés sur ma problématique et surtout pour vos réponses très précises.

Du coup deux questions bonus histoire d'aller plus loin pour ma culture :

1) Le nom du ruban créer est "Complément" à quel niveau dans le code il faut intervenir pour venir renommer ce ruban de la manière que l'on souhaite, exemple : " Déprotection" ?

Sub Creation_menu_Spike()
'
Dim Barre As CommandBar
Dim Groupe As CommandBarPopup, PopUp As CommandBarPopup
Dim Bouton As CommandBarButton

Dim Nombarre As String

' Nom donné à la barre de menu ... à personnaliser (comme tout les noms donnés bien évidemment
'
Nombarre = "Menu dédié CORPROD " & Mid(ThisWorkbook.Path, 4, 4)

' L'option True lors de la création du menu va faire que ce menu va disparaitre lors de le fermeture d'Excel.
' Il faut donc prévoir le cas ou le classeur est fermé et ouvert dans la foulée sans passer par une fermeture d'excel.
' Dans cette hypothèse le menu est encore disponible et ne doit donc
' pas donner lieu à une nouvelle création => d'où l'Exit Sub ci-dessous



For Each Barre In Application.CommandBars
    If Barre.Name = Nombarre Then
    
    Barre.Visible = True
    Exit Sub  ' On vient de traouver le menu, inutile de le recréer
    
    End If
Next Barre


' Création de la barre de menu
'
Set Barre = Application.CommandBars.Add(Nombarre, 1, , True)          '  le "True" undique une barre temporaire
'                                                                     '   qui est supprimée à la femeture de l'application (exel)
'                                                                      ' pour la conserver mettre True

Barre.Visible = True
Barre.Left = 30

'
'
'
'Deux solutions : au Choix. A titre d'exemple; il faudra supprimer la formule non retenue

' Formule 1 : deux boutons séparés
'

' 1er bouton
Set Bouton = CommandBars(Nombarre).Controls.Add(Type:=msoControlButton)  ' , ID:=280)
    Bouton.Style = msoButtonCaption
    Bouton.Caption = "Mettre protection "  'Nom du bouton
    Bouton.OnAction = "Protect_On" 'Définit la macro associée au bouton.

' 2ème bouton
Set Bouton = CommandBars(Nombarre).Controls.Add(Type:=msoControlButton)   '  , ID:=280)
    Bouton.Style = msoButtonCaption
    Bouton.Caption = "Enlever protection "  'Nom du bouton
    Bouton.OnAction = "ArrêtProtec" 'Définit la macro associée au bouton.


  
'
'Formule 2 : deux boutons regroupés
'
Set Groupe = Barre.Controls.Add(msoControlPopup)
    Groupe.Caption = "   Protection des feuilles    "
    Groupe.Width = 70
    Set Bouton = Groupe.Controls.Add(msoControlButton)
        Bouton.Caption = "Activer"
        Bouton.OnAction = "Protect_On"
    Set Bouton = Groupe.Controls.Add(msoControlButton)
        Bouton.Caption = "Enlever"
        Bouton.OnAction = "ArrêtProtec"
    
'
'
'
End Sub




Sub Protect_On()
' routine de protection des feuilles du classeur

'Dim Feuil As Excel.Worksheet

'For Each Feuil In ThisWorkbook.Worksheets
'    Feuil.Protect Password:="sandman", DrawingObjects:=True, _
'    Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
'    'Feuil.EnableSelection = xlNoSelection
'Next Feuil
End Sub

Sub Protect_Off()
' La protection des feuilles va être levée

Dim Feuil As Excel.Worksheet

For Each Feuil In ThisWorkbook.Worksheets
    Feuil.Unprotect "sandman"
'   ActiveWorkbook.Unprotect "secret"
    
Next Feuil
End Sub

2) Pour aller encore plus loin et gagner en rapidité ( oui on frôle la sur-qualité mais je suis curieux ^^)pour l'utilisateur est-il possible de personnaliser la barre d'outils accès rapide directement via le code VBA ?
L'idée, faire en sorte que la partie de code "Enlever protection" puisse directement être déclencher depuis un picto présent dans la barre d'accès rapide du ruban, ici une clef; Voir image ci-dessous.

Encore merci et bonne soirée à tous !

 

[bonoboas]

Bonjour le fil
Une autre solution avec custom UI avec icônes, à vous de juger
Bonne réception

 

[juvaxe]

Bonjour

Pour répondre à Spike29, je n'ai pas la réponse à la possibilité de personnaliser le nom du bouton "Compléments", il faudrait faire des recherches complémentaires ou attendre une autre réponse.

Les boutons que j'ai ajoutés peuvent être améliorés dans la mesure où je suis resté sur l'option de base "bouton - texte", il existe les options "bouton - icône + texte" et "bouton - icône" .

Sinon la proposition de bonoboas est sympathique. J'ai Office 2010 sur mon ordi; je ne dispose pas de Custom UI dont je ne connais que le nom; je ne peux pas avoir accès à la personnalisation faite; le code fait en VBA doit être remplacé par autre chose ailleurs (en HTML?).

Cordialement

 

[spike29]

Bonjour à tous,

Désolé pour la réponse tardive. Un grand merci pour vos différentes solutions qui fonctionnent toutes parfaitement et répondent à mon besoin.

@juvaxe j'ai testé les options boutons + icône + texte ça marche.

@bonoboas , merci pour ta solution les icônes sont parfaites !

Merci à tous et bon Lundi de Pâques