VBA - Griser les commande "Inserer" (dans menu contextuel) / Inserer-Supprimer lignes et colonnes

Version imprimable

Bonjour,

Je viens vers vous car je rencontre un problème que je ne réussi pas à résoudre.

J'ai un classeur Excel dans lequel j'ai fait un peu de VBA. Mais pour que mon code reste entièrement fonctionnel il ne faut absolument pas que soit supprimer/ajouter une cellule, une ligne ou une colonne

Pour griser la commande "Supprimer" du menu contextuel (clic droit sur une cellule) j'ai utiliser le code suivant (code trouvé après moultes recherches sur ce forum ;)):
Code:

1 2 3 4 5 6 7 8 Private Sub Workbook_Activate() 'Désactivation de la commande 'Supprimer' ' (Ne pas oublier de rétablir la commande au moment de la désactivation du classeur -> Workbook_Deactivate) 'dans le menu principal Application.CommandBars(1).Controls("Edition").CommandBar.Controls("Supprimer...").Enabled = False 'dans le menu contextuel Application.CommandBars("Cell").Controls("Supprimer...").Enabled = False End Sub
Avec ce code ci-dessous pour ré-activer la commande "Supprimer" (sur une cellule) :
Code:

1 2 3 4 5 6 7 Private Sub Workbook_Deactivate() 'Ré-activation de la commande 'Supprimer' 'dans le menu principal Application.CommandBars(1).Controls("Edition").CommandBar.Controls("Supprimer...").Enabled = True 'dans le menu contextuel Application.CommandBars("Cell").Controls("Supprimer...").Enabled = True End Sub
Ca fonctionne super bien.

Par contre, je ne réussis pas à faire la même chose pour la commande "Insérer" du menu contextuel (clic droit sur une cellule). Y a-t-il une solution svp ?

De plus, l’icône "Supprimer" (dans le bandeau"Accueil") permet toujours de supprimer une cellule/ligne/colonne.
Y a-t-il une solution pour griser cette icône (et le dé-griser en sortant du Woorkbook) ?

De même l’icône "Insérer" (dans le bandeau"Accueil") permet d'insérer une cellule/ligne/colonne.
Y a-t-il une solution pour griser cette icône (et le dé-griser en sortant du Woorkbook) ?

Enfin, est-il possible de griser les commandes "Supprimer" et "Insertion" du menu contextuel qui apparait lorsque l'on clic droit sur une entête de colonne ou une entête de ligne ?

A noter que je ne peux pas utiliser "Protect" car j'ai besoin que les cellules soient modifiables à tous moment ^^

Bref.... Au secours !!! :)

19/11/2013, 17h17
parmi

Bonjour,

En lisant ceci, tu trouveras peut-être une solution.
Ce n'est pas simple travailler avec les menus depuis 2007...
20/11/2013, 09h39
TheYoMan

Merci du tuyau, je vais jeter un œil attentif à tous ça ;)

Bonjour,

Pour version Excel 2007 et plus, une piste avec la démarche suivante.

1) CODE VBA - copiez le code suivant dans la fenêtre de code de 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
Private Sub Workbook_Activate()
With Application.CommandBars("Cell")
  .FindControl(ID:=3181).Enabled = False
  .FindControl(ID:=292).Enabled = False
End With
With Application.CommandBars("Column")
  .FindControl(ID:=3183).Enabled = False
  .FindControl(ID:=294).Enabled = False
End With
With Application.CommandBars("Row")
  .FindControl(ID:=3183).Enabled = False
  .FindControl(ID:=293).Enabled = False
End With
End Sub
 
Private Sub Workbook_Deactivate()
With Application.CommandBars("Cell")
  .FindControl(ID:=3181).Enabled = True
  .FindControl(ID:=292).Enabled = True
End With
With Application.CommandBars("Column")
  .FindControl(ID:=3183).Enabled = True
  .FindControl(ID:=294).Enabled = True
End With
With Application.CommandBars("Row")
  .FindControl(ID:=3183).Enabled = True
  .FindControl(ID:=293).Enabled = True
End With
End Sub

2) CODE XML - copiez le code suivant dans "Custom UI Editor For Microsoft Office" (voir le lien obligeamment fourni par parmi)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <!-- Désactive les boutons -->
  <commands> 
   <!--## Supprimer ##-->
    <command idMso="CellsDeleteSmart" enabled="false" /> 
    <command idMso="CellsDelete" enabled="false" /> 
    <command idMso="SheetRowsDelete" enabled="false" /> 
    <command idMso="SheetColumnsDelete" enabled="false" /> 
    <command idMso="SheetDelete" enabled="false" /> 
   <!--## Insérer ##-->
    <command idMso="InsertCellstMenu" enabled="false" />
  </commands>
</customUI>

Si vous êtes en Excel 2010, adaptez la 1ère ligne du code XML.

Le code 1 désactive des boutons du menu contextuel.
Le code 2 désactive les éléments Cellules/Insérer et Cellules/Supprimer du menu "Accueil" du ruban.

Je mets un classeur exemple pour que vous puissiez vous rendre compte.

20/11/2013, 20h32
TheYoMan

Merci de votre aide, j'avance à grands pas :ccool:

J'ai un peu galérer pour implémenter le code N°2 mais après avoir étudié l'article linké par Parmi et fait plusieur tests avec "Custom UI Editor For Microsoft Office" ça fonctionne :mrgreen:

C'est exactement ce que je souhaitais : Les options grisées dans les menus contextuels et les bouton grisés dans le ruban "Accueil". Et ceci, uniquement pour le document en question.

Merci beaucoup de votre aide :)

Bonsoir,
Il y a peu y avoir insertion ou suppression par de nombreuses méthodes et notamment les raccourcis claviers

"ctrl"+"shift"+"=" pour insèrer
"ctrl"+"-" pour supprimer

le mieux c'est d'utiliser "protéger la feuille" pour éviter la suppression des lignes, colonnes etc... ( en décochant les cases adéquates)
et lorsque tu executes ta macro tu supprimes cette protection si besoin puis tu la remets à la fin.
Code:

1 2 3 4 5 6 7 8 9 10 ActiveSheet.Unprotect ("toto") 'TON CODE ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True, Password:="toto"
où avec les événements sur la feuille par exemple
Code:

1 2 3 4 5 6 7 8 9 10 11 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, [a2:a30]) Is Nothing Then Application.EnableEvents = False If Target.Address = Target.EntireRow.Address Then Application.Undo MsgBox "Interdit" End If End If Application.EnableEvents = True End Sub

20/11/2013, 22h32
TheYoMan

L'idée est bonne (elle m'avait même traversée la tête) mais il est nécessaire de pouvoir faire des saisies manuelles, des suppression de contenu de cellule, de commentaire, et des changement manuel de couleur... donc c'est pas trop possible.

Quoi que... à moins de dé-protéger dés le début de chaque événement et re-protéger ensuite... mais dans ce cas la il faudrait alors griser le bouton "Ôter la protection de la feuille" du ruban "Révision"...

Pourquoi pas...

Je verrais ça dans une V2 ;)

En tous cas, merci du conseil :)
20/11/2013, 22h48
Oliv-

si tu mets un mot de passe il faut juste proteger ton VBE, l'utilisateur qui voudra désactiver la protection de la feuille devra connaitre ce mdp, et selon les options de .protect tu peux faire presque tout (regarde l'aide sur Référence du développeur Excel
Worksheet.Protect, méthode )

tu peux aussi pour éviter l'insertion d'une ligne , colonne ajouter du contenu à la dernière cellule de ta feuille soit range("IV65536") soit range("XFD1048576") en xlsx.
c'est sans programmation par contre cela n'empeche pas la suppression.
edit : pour éviter l'insertion d'1 cellule il faudrait du contenu dans toutes les cellules de la dernière ligne et dernière colonne