[XL-2003] Commande VBA pour Lancer le Menu "Renommer/Feuille" (Excel)

[epsilon99]

Hello à tous,

Explication du contexte :

Suite à une petite Appli que j'ai faite en VBA, J'ai un classeur dans lequel j'ai une Feuille que j'ai nommé "Mon_Menu"

Dans celle-ci, j'ai mis des boutons qui exécutent du code vba quand on clique dessus.

Le pb est que cette feuille ("Mon_Menu") ne doit pas être supprimée pour pouvoir fonctionner. (normal, puisque c'est la feuille qui constitue le menu....)

En parcourant le net, j'ai réussi à détourner le menu "Supprimer" pour cette feuille qui porte le nom de "Mon_Menu".. (Feuille = Onglet)
Cad Désactivzer ces 2 Menus : Clic droit sur le nom de la Feuille (ou sur le Nom de l'Onglet)/Supprimer, ou Menu Edition/Supprimer Feuille

Code :

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
------------------------------------------------------------
Dans "ThisWorkbook"
Private Sub Workbook_Open()
'Appel de la procédure qui détourne le menu Supprimer Feuille d'Excel
ModifierSupprimerFeuille
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'Appel à la précédure qui permet de rétablir la configuration originale du menu d'Excel "Supprimer Feuille"
RetablirSupprimerFeuille
End Sub
------------------------------------------------------------

------------------------------------------------------------
Dans "Module1"
Sub ModifierSupprimerFeuille()
On détourne l'action originale de Supprimer Feuille du menu d'Excel (c.OnAction)
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=847)
c.OnAction = "SupFeuil"
Next c
End Sub

Sub SupFeuil()
'Ajout du message personnalisé quand on lance le menu de Suppression d'une Feuille
'Si cette Feuille se nomme "Mon_menu", On affiche un message et on bloque la Suppression
'Dans le cas contraire on redonne la main à l'utilisateur pour permettre la Suppression (ActiveSheet.Delete) 
If ActiveSheet.Name = "Mon_Menu" Then
   MsgBox "Vous ne pouvez pas supprimer cette feuille!", vbc + vbOKOnly + vbExclamation, "INFORMATION"
   Exit Sub
   Else
   ActiveSheet.Delete
   End If
  Exit Sub
End Sub

Sub RetablirSupprimerFeuille()
'Rétablissement à l'origine du menu Supprimer Feuille d'Excel
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=847)
c.OnAction = ""
Next c
End Sub
------------------------------------------------------------

Mon souci est aussi d’empêcher le renommage de cette Feuille car mon Code VBA est basé dessus...

Ayant pris comme base d'exemple de code VBA le Blocage du menu Supprimer, j'ai essayer d'adapter et faire de même pour bloquer le menu "Renommer Feuille"
Cad désactiver ces 2 Menus : Clic droit sur le nom de la Feuille (ou sur le Nom de l'Onglet)/Renommer, ou Menu Format/Feuille//Renommer

Code :

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
------------------------------------------------------------
Dans "ThisWorkbook"
Private Sub Workbook_Open()
'Appel de la procédure qui détourne le menu Renommer Feuille d'Excel
ModifierRenommerFeuille
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'Appel à la procédure qui permet de rétablir la configuration originale du menu d'Excel "Renommer Feuille"
RetablirRenommerFeuille
End Sub
------------------------------------------------------------

------------------------------------------------------------
Dans "Module1"
Sub ModifierRenommerFeuille()
On détourne l'action originale de Renommer Feuille du menu d'Excel (c.OnAction)
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=889)
c.OnAction = "Renfeuil"
Next c
End Sub

Sub RenFeuil()
'Ajout du message personnalisé quand on lance le menu de Renommage d'une feuille
'Si cette Feuille se nomme "Mon_menu", On affiche un message et on bloque le Renommage
'Dans le cas contraire on redonne la main à l'utilisateur pour permettre la renommage
If ActiveSheet.Name = "Mon_Menu" Then
   MsgBox "Vous ne pouvez pas renommer cette feuille!", vbc + vbOKOnly + vbExclamation, "INFORMATION"
   Exit Sub
   Else
   ActiveSheet.ReName
   End If
  Exit Sub
End Sub

Sub RetablirRenommerFeuille()
'Rétablissement à l'origine du menu Renommer Feuille d'Excel
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=889)
c.OnAction = ""
Next c
End Sub
------------------------------------------------------------

Pour le code "ID:=889", j'ai réussi à le trouver ici
ce qui me bloque, c'est la ligne à mettre dans le code qui permet de lancer le menu de Renommage d'une feuille
=> dans la procédure Sub RenFeuil(), "ActiveSheet.ReName" ne fonctionne pas.
(j'ai tenté cette ligne de commande car je n'ai pas trouvé la commande sur le Net)

Pour Explication :
En VBA, pour lancer le menu d'Excel de suppression d'une feuille, c'est "ActiveSheet.Delete"
Pour lancer le Menu de Renommage d'une feuille ???
Je ne suis pas arrivé a trouver la bonne commande

d'où cette tentative..."ActiveSheet.Rename", mais cela passe pas...
Cela doit être autre chose mais quoi ????

Attention :
Par cette commande, je souhaite faire "comme si" l'utilisateur faisait "clic droit sur le Nom de la feuille/Renommer", cela met alors le Nom de la Feuille en Blanc sur Fond Noir et permet à l'utilisateur de mettre un autre nom...si il le désire...
Allure d'une Feuille ou Onglet "Normal" :
Allure d'une Feuille ou Onglet suite à un "Clic droit/Renommer" :

Donc, suite à cette ligne de commande, je souhaite avoir Le Nom de la Feuille en Blanc sur Fond Noir, un point c'est tout, ensuite, c'est l'utilisateur qui décide de la suite a donner..

D'où ma question...
Y aurait-il une âme charitable pour me donner le nom de cette commande...
j'ai essayé de chercher sur le net à propos de c.OnAction mais je n'ai rien trouvé ....

Merci donc a ceux qui pourront me donner ce coup de main...
car j'avoue que là, ....je fais de très grandes brasses.......
Et au cas ou.... un lien pour avoir des infos sur ces "c.OnAction"

Merci de votre Aide.

[mercatog]

Une proposition pour ne pas construire une usine à gaz.
Au lieu de travailler avec le nom de la feuille (qui peut être modifié par l'utilisateur), travaille plutôt avec le CodeName de la feuille qui apparait dans ton éditeur vba.

Par exemple la feuille nommée aaa à un codeName Feuil1. Dans ton éditeur vba, cela apparait comme ceci Feuil1(aaa)

Dès lors

Code :

Worksheets("aaa").Range("A1").Value="Toto"

Sera remplacé par

Code :

Feuil1.Range("A1").Value="Toto"

La deuxième ligne fonctionnera indépendamment du nom de la feuille.

[epsilon99]

Merci mercatog de ta réponse,

Je vais tester car "CodeName", je ne connaissais pas ...
Car, en effet, si ce CodeName ne change jamais, il suffit en effet de bloquer la suppression...

Mais, est-ce la même chose que "Index" ?

Car si c'est idem, en cas de déplacement ou d'insertion d'une nouvelle feuille, l'index change, donc cela retire un Pb, mais cela en crée un autre..

Et...., connais tu le Nom de la commande qui permet de lancer le menu de renommage d'une Feuille ?
Elle doit bien exister puisque Excel est capable de lancer la commande suite à "un Clic droit/Renommer"

Ce serait intéressant de connaître cette commande au cas ou, mais surtout un lien internet qui permettrait de connaître la liste de ces commandes.
Pour ma part, je n'ai pas trouvé sur le Net..


Aussi,
Connais tu des liens qui donne des infos sur les Propriétés / Méthode de "Application.CommandBars("Ply")"

Exemple :
Application.CommandBars("Ply").Enabled = False 'dans ce cas je pense que c'est une propriété....
aussi
Application.CommandBars.FindControls(ID:=847)
c.OnAction = "" 'méthode je pense...

Je te demande cela car j'ai pas mal cherché mais je n'ai pas trouvé...
ou si peu, (des morceaux de code par-ci par-là...avec explications très succinctes)

Merci de votre aide

EDIT....

Dans mon code, (La feuille dont le CodeName = "Feuil1" porte le nom de "Mon_Menu"), si je remplace
If ActiveSheet.Name = "Mon_Menu" Then
par
If ActiveSheet.CodeName = "Feuil1" Then

En effet, cela fonctionne

et ceci que l'on renomme ou pas la Feuille...
donc c'est une première chose d'acquise...
Ouf..........

reste plus que la commande de renommage...
semble pas facile du tout à trouver ......

HELPAVOUS

[mercatog]

L'index, il suffit de déplacer la feuille pour changer son index (donc pas pratique)

Le CodeName peut être changé uniquement si on a accès à l'éditeur vba.

[epsilon99]

Re,

Je viens de voir cela.
Donc cela fonctionne avec CodeName. Merci.

Pour le nom de la commande pour renommer, sais tu ?

Et connais tu des liens qui donne des infos sur les Propriétés / Méthode de "Application.CommandBars("Ply")"

Application.CommandBars("Ply").Enabled = False 'dans ce cas je pense que c'est une propriété....
-
Application.CommandBars.FindControls(ID:=847), c.OnAction = "" 'méthode je pense...

Je te demande cela car j'ai pas mal cherché mais je n'ai pas trouvé...
ou si peu, (des morceaux de code par-ci par-là...avec explications vraiment très très succinctes...)

Merci de ton aide.

[epsilon99]

Re,

Personne ne connait cette commande ?
(Commande qui permet de lancer le Menu de Renommage d'une Feuille ?)

Merci de Votre aide.

[mercatog]

889

[epsilon99]

Re,

On ne s'est pas bien compris ....
Quand je détourne le menu Supprimer/Feuille d'Excel, je peux, si besoin, ponctuellement, lancer la commande qui active l'action que j'ai annulé précédemment

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sub ModifierSupprimerFeuille()
On détourne l'action originale de Supprimer Feuille du menu d'Excel
' => (c.OnAction ="") par (c.OnAction = "SupFeuil")
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=847)
c.OnAction = "SupFeuil"
Next c
End Sub

Sub SupFeuil()
'Ajout du message personnalisé quand on lance le menu de Suppression d'une Feuille
'Si cette Feuille se nomme "Mon_menu", On affiche un message et on bloque la Suppression
'Dans le cas contraire on redonne la main à l'utilisateur pour permettre la Suppression (ActiveSheet.Delete) 
If ActiveSheet.Name = "Mon_Menu" Then
   MsgBox "Vous ne pouvez pas supprimer cette feuille!", vbc + vbOKOnly + vbExclamation, "INFORMATION"
   Exit Sub
   Else
   Par cette commande je rétablis ponctuellement l'action d'Excel Supprimer Feuille
   ActiveSheet.Delete
   End If
  Exit Sub
End Sub

Je procède de la même manière avec le menu Renommer/Feuille....
cad que je désactive le menu ID 889

Mais je ne connais pas la commande qui permet ponctuellement d'activer le Menu D'Excel Renommer/Feuille
ActiveSheet. ???????

Vois tu ce que je veux dire?

[mercatog]

Désolé pour ne pas te venir en aide, un sujet que je ne connais guère.
Je n'ai jamais travaillé comme ceci (en modifiant l'interface d'excel).
Par contre, tu peux protéger ton classeur (et cocher structure). l'utilisateur ne pourra plus modifier la structure (déplacer, renommer, supprimer feuilles)

En vba pour protéger

Code :

ThisWorkbook.Protect Password:="toto", Structure:=True

pour déprotéger

Code :

ThisWorkbook.Unprotect Password:="toto"

[epsilon99]

Re,

Ok, mais le pb est que mon code VBA agit parfois en Masquant/Démasquant des Feuilles et en crée aussi parfois...

Or si je protège la structure du classeur, je ne peux ni Ajouter, ni Masquer/Démasquer aucune feuille.

C'est pour Cela que je voulais agir de la manière que j'ai expliqué..
A savoir, Désactiver le Menu et pouvoir le lancer via code VBA ponctuellement si besoin ..

Penses tu qu'il y a une personne qui connait cette commande sur ce forum?



Je ne vois plus ou demander .....

[mercatog]

Je t'avais donné comment protéger déprotéger un classeur

Tu peux toujours ajouter par code des feuilles, les masquer, les renommer en déprotégeant d'abord le classeur puis en le protégeant à nouveau.

Exemple purement pédagogique

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Test()
Const MdP As String = "Toto"
 
'protection
ThisWorkbook.Protect Password:=MdP, Structure:=True
 
'je ne peux plus ajouter manuellement une feuille
'mais par code si en déprotégeant le classeur
ThisWorkbook.Unprotect Password:=MdP
Worksheets.Add
ActiveSheet.Name = "Blabla"
 
'Ensuite je re protège
ThisWorkbook.Protect Password:=MdP, Structure:=True
End Sub

[epsilon99]

Ok,

Vu ton dernier post, je note que cela permet aux macros de créer masquer et autre.
Suffit de déprotéger quand on en a besoin et Reprotéger ensuite...

Par contre, dernier point, cela bloque un utilisateur si il souhaite insérer ou copier Une Feuille.

Donc cela résout une mauvaise manip de l'utilisateur (à savoir bloquer suppression et autre du Menu)
.... mais par contre le bloque si il veut ajouter une Nouvelle Feuille "Blanche" par Exemple

C'est cela ou pas ?
-
-

Dans mon dernier post je disais ....

C'est pour Cela que je voulais agir de la manière que j'ai expliqué..
A savoir, Désactiver le Menu et pouvoir le lancer via code VBA ponctuellement si besoin ..

...Suite à ta réponse

Je t'avais donné comment protéger déprotéger un classeur

(J'avais vu cela sur ton avant dernier post.)

Ma question portait non pas sur la commande VBA de Protection/Déprotection d'un classeur mais sur la commande associé à l'action de Renommage du menu D'excel

Equivalent de "Activesheet.Delete" (qui lance le menu d'Excel)
Cette commande Lance le Menu D'excel , La preuve, on arrive sur la boite de dialogue demandant confirmation


Pour le Renommage.....
Quand On fait sur Une Feuille "Clic droit/Renommer" :
On obtient Ceci (Ecriture Blanche su Fond noir)

j'aurais souhaité connaître la commande qui permette d'aboutir à avoir un Onglet de Feuille ainsi sélectionné
Cela redonne en fait la main à l'utilisateur afin de lui proposer de renommer en mode "manuel"

C'est pour cela que je te demandais ton avis afin de savoir ou demander si je ne trouve pas cette ligne de commande sur ce forum

car je ne vois plus ou demander .....

[bbil]

bonjour, ...

et pourquoi pas simplement désactiver la commande ?

Code :

 c.Enabled = false

[epsilon99]

Bonjour bbil,

On le "code" comment et, le mettre ou ?
Car l'avantage de l'exemple cité plus haut substitue un menu par un autre au lancement et pendant toute la durée de l'ouverture du classeur.
-
et, pb, ... je ne sais pas comment on utilise c.Enable ...
un exemple si tu avais .....
-
c'est aussi pour cela que je demandais des liens pour me documenter..
je prends des bouts de code par-ci par-la et fais des essais pour voir si cela me convient, mais je ne connais pas les propriétes/Methodes et commande de CommandBar ni de FindControl(ID:XXX), j'ai pourtant cherché via net, mais je n'ai pas trouvé...
(peut être mal cherché...)

D'où, pourrais tu me donner un exemple.

Rappel du contexte :
Soit 2 Feuilles dans Un classeur avec la 1° "Menu", 2° "Feuil2"
On veut bloquer L'action de Renommage sur la Feuille 1 cad ("Menu") dont CodeName = "Feuil1"

et permettre le Renommage sur la Feuille2 ("Feuil2")

(et permettre a l'utilisateur d'insérer une Nouvelle Feuille ou de Faire une copie)

Merci de votre aide

[bbil]

à l'activation d'une feuille dans thisworkbook, à l'endroit ou tu voulais modifier le OnAction..

Code :

1
2
3
4
5
6
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 
For Each c In Application.CommandBars.FindControls(ID:=889)
c.Enable = sh.name <> "Mafeuille"
Next c
End Sub

[epsilon99]

Re,

à l'activation d'une feuille dans thisworkbook, à l'endroit ou tu voulais modifier le OnAction..

Je reprends car moi, là je commence a brasser....
je ne mettais rien dans "Workbook_SheetActivate" avant .....
--
je te montre ce que je fais et dis moi si c'est Ok ..si possible...
Nom de la Feuille qui constitue le Menu => Name = "Mon_Menu"
CodeName de la Feuille "Mon_Menu"=> CodeName = "Feuil1"

Dans le code ci-dessous, j'ai marqué "C'est ICI que tu veux que je mette le code ?"
est-ce bon endroit ou pas.
sinon, dois-le modifier "Sub RenFeuil()",
car .... je ne peux pas la supprimer cette procédure ...
celle elle qui prend le pas sur le menu d'Execl et renvoi un MsgBox si on tente de renommer "Mon_Menu" dont le CodeName est "Feuil1"

Code :

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
------------------------------------------------------------
Dans "ThisWorkbook"
Private Sub Workbook_Open()
'Appel de la procédure qui détourne le menu Renommer Feuille d'Excel
ModifierRenommerFeuille
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'Appel à la procédure qui permet de rétablir la configuration originale du menu d'Excel "Renommer Feuille"
RetablirRenommerFeuille
End Sub
------------------------------------------------------------

------------------------------------------------------------
Dans "Module1"
Sub ModifierRenommerFeuille()
On détourne l'action originale de Renommer Feuille du menu d'Excel (c.OnAction)
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=889)
c.OnAction = "Renfeuil"
Next c
End Sub

Sub RenFeuil()
'Ajout du message personnalisé quand on lance le menu de Renommage d'une feuille
'Si cette Feuille se nomme "Mon_menu", On affiche un message et on bloque le Renommage
'Dans le cas contraire on redonne la main à l'utilisateur pour permettre la renommage
If ActiveSheet.Name = "Mon_Menu" Then
ou
If ActiveSheet.CodeName = "Feuil1" Then
   MsgBox "Vous ne pouvez pas renommer cette feuille!", vbc + vbOKOnly + vbExclamation, "INFORMATION"
   Exit Sub
   Else
   ActiveSheet.ReName Ne Marche pas.
   
C'est ICI que tu veux que je mette le code ?
   
   End If
  Exit Sub
End Sub

Sub RetablirRenommerFeuille()
'Rétablissement à l'origine du menu Renommer Feuille d'Excel
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=889)
c.OnAction = ""
Next c
End Sub
------------------------------------------------------------

Moi, je mettais du code dans
ThisWorkBook

• Sub Workbook_Open()
• Sub Workbook_BeforeClose(Cancel As Boolean)

Et le module1 :

• Sub ModifierRenommerFeuille()
• Sub RenFeuil()
• Sub RetablirRenommerFeuille()

Ta solution c'est de supprimer certaines choses, en ajouter ?
car je ne vois pas...
je ne suis pas un expert en VBA....

Dois-je supprimer certaines choses ou pas ?

De plus, Quand je met ton Code..
(Evidemment j'ai renommé la Feuille en "Mafeuille", comme indiqué dans ton post)
Message d'erreur comme tu peux le voir ?
fais-je une erreur ?

[bbil]

Ben, c'est une autre solution ... plutôt que modifier les commandes supprimer/renommer ti les masques..ou désactives ;

tout ce passe dans Thisworkbook

Code :

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
Private Sub Workbook_Activate()
   MajMenus
End Sub
 
Private Sub Workbook_Deactivate()
    MajMenus True
End Sub
 
Private Sub Workbook_Open()
   MajMenus
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MajMenus
End Sub
Private Sub Workbook_Close()
   MajMenus True
End Sub
 
Private Sub MajMenus(Optional bForceEnable As Boolean = False)
Dim c As CommandBarControl
For Each c In Application.CommandBars.FindControls(ID:=889)
    c.Enabled = (ActiveSheet.CodeName <> "Feuil2") Or bForceEnable
    c.Visible = c.Enabled 'on peu même faire disparaitre l'option
Next c
For Each c In Application.CommandBars.FindControls(ID:=847)
    c.Enabled = (ActiveSheet.CodeName <> "Feuil2") Or bForceEnable
    c.Visible = c.Enabled 'on peu même faire disparaitre l'option
Next c
 
End Sub

[mercatog]

J'insiste sur ce que je t'avais proposé (c'est ma dernière proposition)

Code à mettre dans le module Thisworkbook

Code :

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
Option Explicit
Const MdP As String = "toto"
 
Sub Proteger()
 
ThisWorkbook.Protect Password:=MdP, Structure:=True
End Sub
 
Sub Deproteger()
 
ThisWorkbook.Unprotect Password:=MdP
End Sub
 
Private Sub Workbook_Open()
 
Proteger
End Sub
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 
If Sh.Name = "Mon_Menu" Then
    Proteger
Else
    Deproteger
End If
End Sub

Teste sur un nouveau classeur avec la feuille à ne pas supprimer ni renommer est MonMenu

[epsilon99]

Re bbil,

Ok, Cela marche.
Ton Code Active/Désactive, Fait Disparaître/Réapparaître les Menus Supprimer et Renommer pour la feuille donc le CodeName est "Feuil2"

Merci pour tout., c'est super.

Pour Info, connais tu des liens pour essayer de comprendre le fonctionnement des c.Enabled, c.OnAction, c.Visible et autre ....
et surtout pour connaitre les commandes ...

Car je n'arrive pas a trouver d'infos dessus...(En Français ce serait mieux si possible)

Enfin, Juste cette dernière Question....
Le pb est résolu en faisant autrement ....

Mais..
Sais tu si il y a t-il une commande qui permette de redonner la main à l'utilisateur afin de lui permettre de renommer une feuille si il le désire ?
Cad, avoir, suite à une commande VBA du style Activesheet.Rename,
redonner la main à l'utilisateur afin d'avoir l'onglet Feuille inscrit en blanc sur fond noir.
comme ceci :

-
En Fait, Renommer une Feuille correspond à un double clic sur le nom de la Feuille, c'est la même chose....
Donc si il n'y a pas de commande, est-il possible de simuler un double clic sur le nom d'un onglet ?

[bbil]

ben commence déjà par l'aide VBA


ben ce qui va être difficile à simuler c'est la saisie du nouveau nom sur l'onglet..

mais sinon pour renommer il suffit de modifier la propriété name de ta feuille :

Code :

ActiveSheet.name = InputBox("Nouveau nom feuile")