Commande VBA pour Lancer le Menu "Renommer/Feuille" (Excel)

**epsilon99** · 10/02/2012, 13h30

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 :

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
------------------------------------------------------------
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 :

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
------------------------------------------------------------
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** · 10/02/2012, 14h46

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 :

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

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

Sera remplacé par

Code :

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

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

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

**epsilon99** · 10/02/2012, 15h11

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** · 10/02/2012, 16h07

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** · 10/02/2012, 16h25

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.