Détection d'une insertion de colonne

bonjour M

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "change"
End Sub

isabelle

Bonjour

Peut-on avec la programmation évènementielle VBA détecter directement ou
indirectement l'insertion d'une colonne ?

Cordialement

M41

Bonsoir,

Après avoir nommé "repere" une cellule que tu as peu de chance d'utiliser (BA1
par exemple), cette petite bricole dans le module de la feuille concernée peut
peut-être te dépanner :

'======================== Dim x

Private Sub Worksheet_Change(ByVal Target As Range)
Dim y
y = x
x = Range("repere").Column
If x > y Then MsgBox "colonne ajoutée"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Range("repere").Column
End Sub
'========================
FS
---
Frédéric Sigonneau
http://frederic.sigonneau.free.fr

Bonjour

Peut-on avec la programmation évènementielle VBA détecter directement ou
indirectement l'insertion d'une colonne ?

Cordialement

M41

Bonjour et merci

J'avais probablement mal posé ma question
En fait mon but est de protéger une feuille en ne verrouillant par exemple
que la zone A1:J3
Cela signifie que l'utilisateur peut dans le reste faire des insertions de
lignes ou de colonnes
mais que A1:J3 est toujours respectée. Le verrouillage de la feuille
autorisant l'insertion pose
de nombreux problèmes :

On peut faire une insertion dans la zone A1:J3 donc la modifier

Private Sub Worksheet_Change(ByVal Target As Range)... détectant une action
pour créer une contre
action : pose problème si on insert une colonne en colonne K ou ligne 4
(cette solution fonctionne sinon
dans tous les autres cas)

Finalement je suis arrivé à la conclusion qu'il ne fallait pas protéger la
feuille pour la protéger selon
mes critères avec le Sub suivant !

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'protection de la zone "A1:J3"
Dim i As Long, j As Long, i1 As Long, j1 As Long, R As Range
Static CT As Boolean
If CT Then Exit Sub
CT = True: Set R = Range("J3")
On Error GoTo AAA
i = Target.Row: j = Target.Column
i1 = R.Row: j1 = R.Column
If i <= i1 And j <= j1 Then
If i = 1 Then
Cells(i, j1 + 1).Select
Else
Cells(i1 + 1, j).Select
End If
End If
CT = False
Exit Sub
AAA:
CT = False
End Sub

Principe : si l'utilisateur ne peut plus sélectioner une cellule et n'a pas
accès à VBA il ne peut rien modifier.
Une autre application Excel pourrait cependant modifier le contenu de A1:J3
mais :

Private Sub Worksheet_Change(ByVal Target As Range)

pourrait alors restituer le contenu de A1:J3 à partir d'une feuille cachée
verrouillée !

Si vous trouvez une faille à la méthode, je suis preneur...

Cordialement

M41




"Frédéric Sigonneau" <nospam@nospam> a écrit dans le message de news:
uEimHpMcIHA.4684@TK2MSFTNGP06.phx.gbl...

Bonsoir,

Après avoir nommé "repere" une cellule que tu as peu de chance d'utiliser
(BA1 par exemple), cette petite bricole dans le module de la feuille
concernée peut peut-être te dépanner :

'======================== > Dim x

Private Sub Worksheet_Change(ByVal Target As Range)
Dim y
y = x
x = Range("repere").Column
If x > y Then MsgBox "colonne ajoutée"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Range("repere").Column
End Sub
'======================== >
FS
---
Frédéric Sigonneau
http://frederic.sigonneau.free.fr

Bonjour

Peut-on avec la programmation évènementielle VBA détecter directement ou
indirectement l'insertion d'une colonne ?

Cordialement

M41

Bonsoir,
J'ai peut être mal compris ce que tu veux faire mais ton code n'empêche en
rien l'insertion d'une colonne, cellule ou ligne dans ta plage ?
Maintenant, pour faire à peut près ce que fait ton code tu peux simplifier :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A1:J3]) Is Nothing Then
Application.EnableEvents = False
Cells(4, Target.Column).Select
Application.EnableEvents = True
End If
End Sub

Si tu veux empécher l'utilisateur d'insérer une ligne, colonne ou cellule
dans la plage A1:J3, voici une piste (neutralisation des boutons et menus
quand la cellule active se trouve en colonne A à J ou en ligne 1 à 3)
Teste et adapte :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, [K4:IV65536]) Is Nothing Then
InterdireModifPlage False
Else
InterdireModifPlage True
End If
End Sub

Sub InterdireModifPlage(Etat As Boolean)
Dim Ctrl As CommandBarControls
Dim I As Integer
Dim J As Integer

On Error Resume Next
'barre des menus
With Application.CommandBars(1)
.Controls("&Edition") _
.Controls("&Supprimer...").Enabled = Etat
With .Controls("&Insertion")
.Controls("&Lignes").Enabled = Etat
.Controls("C&olonnes").Enabled = Etat
.Controls("&Cellules...").Enabled = Etat
End With
End With
'menu popup clic droit dans les cellules
With Application.CommandBars("Cell")
.Controls("&Insérer...").Enabled = Etat
.Controls("&Supprimer...").Enabled = Etat
End With
'menu popup clic droit dans les lignes
'(à gauche)
With Application.CommandBars("Row")
.Controls("&Insertion").Enabled = Etat
.Controls("&Supprimer").Enabled = Etat
End With
'menu popup clic droit dans les colonnes
'(en haut)
With Application.CommandBars("Column")
.Controls("&Insertion").Enabled = Etat
.Controls("&Supprimer").Enabled = Etat
End With
'recherche dans les barres visibles
'les contrôles boutons
'Inserer et Supprimer cellules
'lignes et colonnes
For J = 292 To 297
Set Ctrl = CommandBars.FindControls _
(msoControlButton, J, , True)
'si trouvé, les active ou désactive
If Not Ctrl Is Nothing Then
For I = 1 To Ctrl.Count
Ctrl(I).Enabled = Etat
Next I
End If
Next J
Set Ctrl = Nothing

End Sub

Hervé


"M41" <boomerang.pro00@wanadoo.fr> a écrit dans le message de news:
47b7420e$0$834$ba4acef3@news.orange.fr...

Bonjour et merci

J'avais probablement mal posé ma question
En fait mon but est de protéger une feuille en ne verrouillant par exemple
que la zone A1:J3
Cela signifie que l'utilisateur peut dans le reste faire des insertions de
lignes ou de colonnes
mais que A1:J3 est toujours respectée. Le verrouillage de la feuille
autorisant l'insertion pose
de nombreux problèmes :

On peut faire une insertion dans la zone A1:J3 donc la modifier

Private Sub Worksheet_Change(ByVal Target As Range)... détectant une
action

pour créer une contre
action : pose problème si on insert une colonne en colonne K ou ligne 4
(cette solution fonctionne sinon
dans tous les autres cas)

Finalement je suis arrivé à la conclusion qu'il ne fallait pas protéger la
feuille pour la protéger selon
mes critères avec le Sub suivant !

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'protection de la zone "A1:J3"
Dim i As Long, j As Long, i1 As Long, j1 As Long, R As Range
Static CT As Boolean
If CT Then Exit Sub
CT = True: Set R = Range("J3")
On Error GoTo AAA
i = Target.Row: j = Target.Column
i1 = R.Row: j1 = R.Column
If i <= i1 And j <= j1 Then
If i = 1 Then
Cells(i, j1 + 1).Select
Else
Cells(i1 + 1, j).Select
End If
End If
CT = False
Exit Sub
AAA:
CT = False
End Sub

Principe : si l'utilisateur ne peut plus sélectioner une cellule et n'a
pas

accès à VBA il ne peut rien modifier.
Une autre application Excel pourrait cependant modifier le contenu de
A1:J3

mais :

Private Sub Worksheet_Change(ByVal Target As Range)

pourrait alors restituer le contenu de A1:J3 à partir d'une feuille cachée
verrouillée !

Si vous trouvez une faille à la méthode, je suis preneur...

Cordialement

M41




"Frédéric Sigonneau" <nospam@nospam> a écrit dans le message de news:
uEimHpMcIHA.4684@TK2MSFTNGP06.phx.gbl...

Bonsoir,

Après avoir nommé "repere" une cellule que tu as peu de chance
d'utiliser

(BA1 par exemple), cette petite bricole dans le module de la feuille
concernée peut peut-être te dépanner :

'======================== > > Dim x

Private Sub Worksheet_Change(ByVal Target As Range)
Dim y
y = x
x = Range("repere").Column
If x > y Then MsgBox "colonne ajoutée"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Range("repere").Column
End Sub
'======================== > >
FS
---
Frédéric Sigonneau
http://frederic.sigonneau.free.fr

Bonjour

Peut-on avec la programmation évènementielle VBA détecter directement
ou

indirectement l'insertion d'une colonne ?

Cordialement

M41

Petite rectification,
La Proc "InterdireModifPlage" est à mettre dans un module satadard et il
faut rajouter les deux procs ci-dessous dans le module du classeur afin de
réactiver les boutons et menus quand on quitte le classeur :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
InterdireModifPlage True
End Sub

Private Sub Workbook_Deactivate()
InterdireModifPlage True
End Sub

Hervé

"Hervé" <hmsilve-sanspub@wanadoo.fr> a écrit dans le message de news:
u$Vo3WOcIHA.6080@TK2MSFTNGP05.phx.gbl...

Bonsoir,
J'ai peut être mal compris ce que tu veux faire mais ton code n'empêche en
rien l'insertion d'une colonne, cellule ou ligne dans ta plage ?
Maintenant, pour faire à peut près ce que fait ton code tu peux simplifier
:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A1:J3]) Is Nothing Then
Application.EnableEvents = False
Cells(4, Target.Column).Select
Application.EnableEvents = True
End If
End Sub

Si tu veux empécher l'utilisateur d'insérer une ligne, colonne ou cellule
dans la plage A1:J3, voici une piste (neutralisation des boutons et menus
quand la cellule active se trouve en colonne A à J ou en ligne 1 à 3)
Teste et adapte :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, [K4:IV65536]) Is Nothing Then
InterdireModifPlage False
Else
InterdireModifPlage True
End If
End Sub

Sub InterdireModifPlage(Etat As Boolean)
Dim Ctrl As CommandBarControls
Dim I As Integer
Dim J As Integer

On Error Resume Next
'barre des menus
With Application.CommandBars(1)
.Controls("&Edition") _
.Controls("&Supprimer...").Enabled = Etat
With .Controls("&Insertion")
.Controls("&Lignes").Enabled = Etat
.Controls("C&olonnes").Enabled = Etat
.Controls("&Cellules...").Enabled = Etat
End With
End With
'menu popup clic droit dans les cellules
With Application.CommandBars("Cell")
.Controls("&Insérer...").Enabled = Etat
.Controls("&Supprimer...").Enabled = Etat
End With
'menu popup clic droit dans les lignes
'(à gauche)
With Application.CommandBars("Row")
.Controls("&Insertion").Enabled = Etat
.Controls("&Supprimer").Enabled = Etat
End With
'menu popup clic droit dans les colonnes
'(en haut)
With Application.CommandBars("Column")
.Controls("&Insertion").Enabled = Etat
.Controls("&Supprimer").Enabled = Etat
End With
'recherche dans les barres visibles
'les contrôles boutons
'Inserer et Supprimer cellules
'lignes et colonnes
For J = 292 To 297
Set Ctrl = CommandBars.FindControls _
(msoControlButton, J, , True)
'si trouvé, les active ou désactive
If Not Ctrl Is Nothing Then
For I = 1 To Ctrl.Count
Ctrl(I).Enabled = Etat
Next I
End If
Next J
Set Ctrl = Nothing

End Sub

Hervé


"M41" <boomerang.pro00@wanadoo.fr> a écrit dans le message de news:
47b7420e$0$834$ba4acef3@news.orange.fr...

Bonjour et merci

J'avais probablement mal posé ma question
En fait mon but est de protéger une feuille en ne verrouillant par
exemple

que la zone A1:J3
Cela signifie que l'utilisateur peut dans le reste faire des insertions
de

lignes ou de colonnes
mais que A1:J3 est toujours respectée. Le verrouillage de la feuille
autorisant l'insertion pose
de nombreux problèmes :

On peut faire une insertion dans la zone A1:J3 donc la modifier

Private Sub Worksheet_Change(ByVal Target As Range)... détectant une
action

pour créer une contre
action : pose problème si on insert une colonne en colonne K ou ligne 4
(cette solution fonctionne sinon
dans tous les autres cas)

Finalement je suis arrivé à la conclusion qu'il ne fallait pas protéger
la

feuille pour la protéger selon
mes critères avec le Sub suivant !

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'protection de la zone "A1:J3"
Dim i As Long, j As Long, i1 As Long, j1 As Long, R As Range
Static CT As Boolean
If CT Then Exit Sub
CT = True: Set R = Range("J3")
On Error GoTo AAA
i = Target.Row: j = Target.Column
i1 = R.Row: j1 = R.Column
If i <= i1 And j <= j1 Then
If i = 1 Then
Cells(i, j1 + 1).Select
Else
Cells(i1 + 1, j).Select
End If
End If
CT = False
Exit Sub
AAA:
CT = False
End Sub

Principe : si l'utilisateur ne peut plus sélectioner une cellule et n'a
pas

accès à VBA il ne peut rien modifier.
Une autre application Excel pourrait cependant modifier le contenu de
A1:J3

mais :

Private Sub Worksheet_Change(ByVal Target As Range)

pourrait alors restituer le contenu de A1:J3 à partir d'une feuille
cachée

verrouillée !

Si vous trouvez une faille à la méthode, je suis preneur...

Cordialement

M41




"Frédéric Sigonneau" <nospam@nospam> a écrit dans le message de news:
uEimHpMcIHA.4684@TK2MSFTNGP06.phx.gbl...

Bonsoir,

Après avoir nommé "repere" une cellule que tu as peu de chance
d'utiliser

(BA1 par exemple), cette petite bricole dans le module de la feuille
concernée peut peut-être te dépanner :

'======================== > > > Dim x

Private Sub Worksheet_Change(ByVal Target As Range)
Dim y
y = x
x = Range("repere").Column
If x > y Then MsgBox "colonne ajoutée"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Range("repere").Column
End Sub
'======================== > > >
FS
---
Frédéric Sigonneau
http://frederic.sigonneau.free.fr

Bonjour

Peut-on avec la programmation évènementielle VBA détecter directement
ou

indirectement l'insertion d'une colonne ?

Cordialement

M41

RE

Voilà la solution finalement trouvée et qui fonctionne :
elle répond probablement à un besoin assez fréquent

Existe t il encore des causes de mauvais fonctionnement ? La question est
ouverte...
Cordialement

M41

---------------------------------

'Dans la feuille
Private CT As Boolean
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' protection de la zone "A1:J3"
' "A1:I2" verrouillable dans la feuille sinon insertion en K1:K3 vérouillée
!
' verrouillage avec toutes les actions possibles dont
l'insertion/destruction
Dim i As Long, j As Long, i1 As Long, j1 As Long, R As Range, C As Boolean
If C Or CT Then Exit Sub
Set R = Range("J3")
i = Target.Row: j = Target.Column
i1 = R.Row: j1 = R.Column
If i <= i1 And j <= j1 Then
If i = 1 Then
C = True
Cells(i, j1 + 1).Select
C = False
Else
C = True
Cells(i1 + 1, j).Select
C = False
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, i1 As Long, j1 As Long, R As Range, C As Boolean
If C Or CT Then Exit Sub
CT = True: C = True
Set R = Range("J3")
i = Target.Row: j = Target.Column
i1 = R.Row: j1 = R.Column
If i <= i1 And j <= j1 Then
R.Value = "X" 'simulation autoréparation de la zone, "J3" contenant "X"
End If
C = False: CT = False
End Sub


'Dans un module
Sub ECRIRE()
Range("J3").Value = "" 'simule une action externe
End Sub

------------------------------FIN



"M41" <boomerang.pro00@wanadoo.fr> a écrit dans le message de news:
47b72005$0$853$ba4acef3@news.orange.fr...

Bonjour

Peut-on avec la programmation évènementielle VBA détecter directement ou
indirectement l'insertion d'une colonne ?

Cordialement

M41

Bonjour
Il est vrai que l'action des boutons permet l'insertion et que ma solution
ne bloque que les actions par sélection. On peut cependant ouvrir Excel sans
lignes de boutons....ce qui laisse toute la fenêtre au tableur. Il resterait
les raccourcis de clavier. Ma 1ère solution testée était très proche de
celle de F Sigonneau mais en choisissant J3 comme cellule de détection : il
y avait aussi des problèmes....
C'est d'ailleurs parceque j'avais choisi initialement cette méthode en la
modifiant ensuite que mon code est resté lourd comme me l'a fait justement
remarqué Hervé.
Cordialement
M41


"Hervé" <hmsilve-sanspub@wanadoo.fr> a écrit dans le message de news:
eXdcGbOcIHA.1184@TK2MSFTNGP04.phx.gbl...

Petite rectification,
La Proc "InterdireModifPlage" est à mettre dans un module satadard et il
faut rajouter les deux procs ci-dessous dans le module du classeur afin de
réactiver les boutons et menus quand on quitte le classeur :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
InterdireModifPlage True
End Sub

Private Sub Workbook_Deactivate()
InterdireModifPlage True
End Sub

Hervé

"Hervé" <hmsilve-sanspub@wanadoo.fr> a écrit dans le message de news:
u$Vo3WOcIHA.6080@TK2MSFTNGP05.phx.gbl...

Bonsoir,
J'ai peut être mal compris ce que tu veux faire mais ton code n'empêche
en
rien l'insertion d'une colonne, cellule ou ligne dans ta plage ?
Maintenant, pour faire à peut près ce que fait ton code tu peux
simplifier
:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A1:J3]) Is Nothing Then
Application.EnableEvents = False
Cells(4, Target.Column).Select
Application.EnableEvents = True
End If
End Sub

Si tu veux empécher l'utilisateur d'insérer une ligne, colonne ou cellule
dans la plage A1:J3, voici une piste (neutralisation des boutons et menus
quand la cellule active se trouve en colonne A à J ou en ligne 1 à 3)
Teste et adapte :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, [K4:IV65536]) Is Nothing Then
InterdireModifPlage False
Else
InterdireModifPlage True
End If
End Sub

Sub InterdireModifPlage(Etat As Boolean)
Dim Ctrl As CommandBarControls
Dim I As Integer
Dim J As Integer

On Error Resume Next
'barre des menus
With Application.CommandBars(1)
.Controls("&Edition") _
.Controls("&Supprimer...").Enabled = Etat
With .Controls("&Insertion")
.Controls("&Lignes").Enabled = Etat
.Controls("C&olonnes").Enabled = Etat
.Controls("&Cellules...").Enabled = Etat
End With
End With
'menu popup clic droit dans les cellules
With Application.CommandBars("Cell")
.Controls("&Insérer...").Enabled = Etat
.Controls("&Supprimer...").Enabled = Etat
End With
'menu popup clic droit dans les lignes
'(à gauche)
With Application.CommandBars("Row")
.Controls("&Insertion").Enabled = Etat
.Controls("&Supprimer").Enabled = Etat
End With
'menu popup clic droit dans les colonnes
'(en haut)
With Application.CommandBars("Column")
.Controls("&Insertion").Enabled = Etat
.Controls("&Supprimer").Enabled = Etat
End With
'recherche dans les barres visibles
'les contrôles boutons
'Inserer et Supprimer cellules
'lignes et colonnes
For J = 292 To 297
Set Ctrl = CommandBars.FindControls _
(msoControlButton, J, , True)
'si trouvé, les active ou désactive
If Not Ctrl Is Nothing Then
For I = 1 To Ctrl.Count
Ctrl(I).Enabled = Etat
Next I
End If
Next J
Set Ctrl = Nothing

End Sub

Hervé


"M41" <boomerang.pro00@wanadoo.fr> a écrit dans le message de news:
47b7420e$0$834$ba4acef3@news.orange.fr...

Bonjour et merci

J'avais probablement mal posé ma question
En fait mon but est de protéger une feuille en ne verrouillant par
exemple

que la zone A1:J3
Cela signifie que l'utilisateur peut dans le reste faire des insertions
de

lignes ou de colonnes
mais que A1:J3 est toujours respectée. Le verrouillage de la feuille
autorisant l'insertion pose
de nombreux problèmes :

On peut faire une insertion dans la zone A1:J3 donc la modifier

Private Sub Worksheet_Change(ByVal Target As Range)... détectant une
action

pour créer une contre
action : pose problème si on insert une colonne en colonne K ou ligne 4
(cette solution fonctionne sinon
dans tous les autres cas)

Finalement je suis arrivé à la conclusion qu'il ne fallait pas protéger
la

feuille pour la protéger selon
mes critères avec le Sub suivant !

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'protection de la zone "A1:J3"
Dim i As Long, j As Long, i1 As Long, j1 As Long, R As Range
Static CT As Boolean
If CT Then Exit Sub
CT = True: Set R = Range("J3")
On Error GoTo AAA
i = Target.Row: j = Target.Column
i1 = R.Row: j1 = R.Column
If i <= i1 And j <= j1 Then
If i = 1 Then
Cells(i, j1 + 1).Select
Else
Cells(i1 + 1, j).Select
End If
End If
CT = False
Exit Sub
AAA:
CT = False
End Sub

Principe : si l'utilisateur ne peut plus sélectioner une cellule et n'a
pas

accès à VBA il ne peut rien modifier.
Une autre application Excel pourrait cependant modifier le contenu de
A1:J3

mais :

Private Sub Worksheet_Change(ByVal Target As Range)

pourrait alors restituer le contenu de A1:J3 à partir d'une feuille
cachée

verrouillée !

Si vous trouvez une faille à la méthode, je suis preneur...

Cordialement

M41




"Frédéric Sigonneau" <nospam@nospam> a écrit dans le message de news:
uEimHpMcIHA.4684@TK2MSFTNGP06.phx.gbl...

Bonsoir,

Après avoir nommé "repere" une cellule que tu as peu de chance
d'utiliser

(BA1 par exemple), cette petite bricole dans le module de la feuille
concernée peut peut-être te dépanner :

'======================== >> > > Dim x

Private Sub Worksheet_Change(ByVal Target As Range)
Dim y
y = x
x = Range("repere").Column
If x > y Then MsgBox "colonne ajoutée"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Range("repere").Column
End Sub
'======================== >> > >
FS
---
Frédéric Sigonneau
http://frederic.sigonneau.free.fr

Bonjour

Peut-on avec la programmation évènementielle VBA détecter
directement
ou

indirectement l'insertion d'une colonne ?

Cordialement

M41