XL 2019 Private Sub Worksheet_Change(ByVal Target As Range)

pat66 · 8 Juillet 2020

Pourriez vous m'aider à contruire cette instruction avec Private Sub Worksheet_Change(ByVal Target As Range)

Quand je change grâce a un userform, qui propose 4 durées la valeur dans AE5, je souhaite que cela enclenche une vérification

SI AE5 = 9 = rien ne se passe
SI AE5 <> 9
MsgBox("Si vous changez la durée d'utilisation, le montant de AS17 sera réinitialisé à zero ! ", vbYesNo + vbQuestion, "Excel")
Si clic sur Non rien ne se passe
Si clic sur Oui alors 3 instructions
AS17 = 0
AE5 = 9
on masque la colonne entière ("AT")

fin d'instruction

un grand merci

sylvanu · 8 Juillet 2020

Bonsoir Patrick,
Sans fichier test, juste un essai en PJ sans réelles vérifs :

VB:

Sub Worksheet_Change(ByVal T As Range)
    If T.Count > 1 Then Exit Sub
    If Not Intersect(T, Range("AE5")) Is Nothing Then
        If T = 9 Then Exit Sub
        If MsgBox("Si vous changez la durée d'utilisation, le montant de AS17 sera réinitialisé à zero ! ", vbYesNo, "Question à l'utilisateur") = vbYes Then
            [AS17] = 0
            [AE5] = 9
            Range("AT:AT").EntireColumn.Hidden = True
        End If
    End If
End Sub

pat66 · 9 Juillet 2020

Bonjour sylvanu

Merci de ton aide
c'est presque çà !!! mais il faudrait que lorqu'on clique sur "non" dans le message box, la cellule AE5 conserve la valeur qui y est déjà présente

merci encore

Patrick

sylvanu · 9 Juillet 2020

Bonsoir,
C'est déjà le cas, car si on répond "non" on n'effectue strictement aucun changement. On n'effectue un traitement que si on appuie sur Oui.

pat66 · 9 Juillet 2020

Sylvanu

j'ai un bug avec If T.Count > 1 Then Exit Sub
la boite me dit = objet requis !!

voila la macro complète :
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Planning Treso").Unprotect ("SC6")
Application.ScreenUpdating = 0

Rows("31:50").EntireRow.Hidden = False
If Range("ae5").Value = 6 Then Rows("31:50").EntireRow.Hidden = True
If Range("ae5").Value = 9 Then Rows("34:50").EntireRow.Hidden = True
If Range("ae5").Value = 12 Then Rows("37:50").EntireRow.Hidden = True
If Range("ae5").Value = 15 Then Rows("40:50").EntireRow.Hidden = True
If Range("ae5").Value = 20 Then Rows("45:50").EntireRow.Hidden = True
If Range("ae5").Value = 25 Then Rows("50:50").EntireRow.Hidden = True

If T.Count > 1 Then Exit Sub
If Not Intersect(T, Range("AE5")) Is Nothing Then
If T = 9 Then Exit Sub
If MsgBox("Si vous changez la durée d'utilisation, le montant de AS17 sera réinitialisé à zero ! ", vbYesNo, "Question à l'utilisateur") = vbYes Then
[AS17] = 0
[AE5] = 9
Range("AT:AT").EntireColumn.Hidden = True
End If
End If

Worksheets("Planning Treso").Protect ("SC6")
End Sub

sylvanu · 9 Juillet 2020

Evidemment, avez vous remarqué que la cible est appelée Target dans votre macro mais T dans la mienne.
On ne peut rarement faire du copié collé des macros.
Donc remplacez tous les T, et mettez le T.count >1 dés le début :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Worksheets("Planning Treso").Unprotect ("SC6")
Application.ScreenUpdating = 0

Rows("31:50").EntireRow.Hidden = False
If Range("ae5").Value = 6 Then Rows("31:50").EntireRow.Hidden = True
If Range("ae5").Value = 9 Then Rows("34:50").EntireRow.Hidden = True
If Range("ae5").Value = 12 Then Rows("37:50").EntireRow.Hidden = True
If Range("ae5").Value = 15 Then Rows("40:50").EntireRow.Hidden = True
If Range("ae5").Value = 20 Then Rows("45:50").EntireRow.Hidden = True
If Range("ae5").Value = 25 Then Rows("50:50").EntireRow.Hidden = True


If Not Intersect(Target, Range("AE5")) Is Nothing Then
If Target = 9 Then Exit Sub
    If MsgBox("Si vous changez la durée d'utilisation, le montant de AS17 sera réinitialisé à zero ! ", vbYesNo, "Question à l'utilisateur") = vbYes Then
        [AS17] = 0
        [AE5] = 9
        Range("AT:AT").EntireColumn.Hidden = True
    End If
End If

Worksheets("Planning Treso").Protect ("SC6")
End Sub

Cependant votre macro est bizarre car dès qu'une valeur va changer dans la feuille toute la première partie sera exécutée. Ca va ralentir le système.
Si vous vouliez limiter l’exécution de la macro quand AE5 change de valeur alors la première partie doit se trouver après le If Not Intersect.
Mais évidemment cela dépend de ce que vous voulez faire.

pat66 · 9 Juillet 2020

sylvanu à dit:
Bonsoir,
C'est déjà le cas, car si on répond "non" on n'effectue strictement aucun changement. On n'effectue un traitement que si on appuie sur Oui.

Non actuellement avec ta macro, si AE5 = 9 et que je change la valeur ex 12, le mesBox s'affiche bien mais si je clique sur "non", AS17 aucun changement = parfait, mais AE5 devient quand même 12, je souhaiterai qu'en cliquant sur "non" AE5 reste à 9

merci

sylvanu · 9 Juillet 2020

Oh, ça je ne sais pas faire.
Vous ne voulez pas que AE5 garde sa valeur ( qui serait 12) mais qu'il revienne à sa valeur précédente.
Puisque la macro est appelée sur changement de valeur. L'ancienne valeur n'est pas mémorisée. la macro est appelée avec sa valeur actuelle.
Je n'ai pas de solution à vous proposer, mais peut être que quelqu'un de plus futé vous trouvera une solution.

pat66 · 9 Juillet 2020

en fait voila la macro que j'utilises et qui a cette fonction, mais le problème c'est qu'elle m'enlève la protection de la feuille, un mystère pour moi !!!

If Not Application.Intersect(Target, Range("AE5")) Is Nothing Then
If Range("AE5") <> "9" Then
'Columns("AT").EntireColumn.Visible = False
'Else
Columns("AT").EntireColumn.Hidden = True
End If
End If
Application.ScreenUpdating = -1
If Target.Address <> "$AE$5" Then Exit Sub
If Target.Value = "9" Then
ActiveSheet.Shapes("OK").Visible = True
Else
ActiveSheet.Shapes("OK").Visible = False
End If
If Range("AE5").Value <> "9" Then
ret = MsgBox("Si vous changez la durée d'utilisation, le montant de l'épargne écourté sera réinitialisé ! ", vbYesNo + vbQuestion, "Excel")
If ret = vbYes Then
Range("AS17").Value = 0
Else
Range("AE5").Value = 9
End If
End If

sylvanu · 9 Juillet 2020

Cette macro ne touche pas à la protection de la feuille.
N'avez vous pas d'autres macros ?
NB : Votre second If Range("AE5").Value <> "9" Then est inutile puisqu'il y en a un déjà précédemment.
Mais dans votre macro du post #5 vous aviez : Worksheets("Planning Treso").Unprotect ("SC6") il a disparu ?

Concernant l'autre point, peut être une solution avec un Worksheet_SelectionChange qui est appelée quand on clique la cellule AE5, alors la valeur est mémorisée dans une variable public.
A tester.

pat66 · 9 Juillet 2020

C'est vraiment sympa de m'aider Sylvanu, vous avez trouvé la solution, euréka !!
oui c'est vrai j'ai aussi cette fonction sur la page et c'est la qu'était le problème, j'avais oublié la protection à
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("AE5")) Is Nothing Then
UserForm1.Show
End If
End Sub

je viens de tester avec la protection, cela fonctionne bien, la feuille reste protégée et je vais en profiter pour nettoyer mo Private Sub Worksheet_SelectionChange(ByVal Target As Range)

un grand merci

bonne soirée

sylvanu · 9 Juillet 2020

Renvoyez moi votre Worksheet_SelectionChange(ByVal Target As Range) je pense qu'on mettre ça plus propre.

pat66 · 9 Juillet 2020

c'est super !!, le voici en l'état actuel
'Private Sub Worksheet_Change(ByVal Target As Range)
' Worksheets("Planning Treso").Unprotect ("SC6")
' Application.ScreenUpdating = 0
' If Not Application.Intersect(Target, Range("AE5")) Is Nothing Then
' If Range("AE5") <> "9" Then
' 'Columns("AT").EntireColumn.Visible = False ' mon souhait pour la colonne AT, elle se cache si AE5 est<> de 9 ou s'affiche si AE5 = 9
' 'Else
' Columns("AT").EntireColumn.Hidden = True
' End If
' End If
'
' Application.ScreenUpdating = -1
'
' If Target.Address <> "$AE$5" Then Exit Sub
' If Target.Value = "9" Then
' ActiveSheet.Shapes("OK").Visible = True
' Else
' ActiveSheet.Shapes("OK").Visible = False
' End If
'
' 'le même code avec choix oui non annulé
' If Range("AE5").Value <> "9" Then
' ret = MsgBox("Si vous changez la durée d'utilisation, le montant de l'épargne écourté sera réinitialisé ! ", vbYesNo + vbQuestion, "Excel")
' If ret = vbYes Then
' Range("AS17").Value = 0
' Else
' Range("AE5").Value = 9
' End If
' End If
'
' Worksheets("Planning Treso").Protect ("SC6")
'End Sub

et aussi :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("AE5")) Is Nothing Then
UserForm1.Show
End If
Worksheets("Planning Treso").Protect ("SC6")
End Sub

sylvanu · 9 Juillet 2020

Bonsoir,
On peut optimiser car il y a plusieurs IF AE5<>9 then
on peut les regrouper.
J'ai aussi rajouter la restitution de la valeur si la réponse est Non.
Par contre je n'ai pas testé à cause des UserForm et Shapes que je n'ai pas.
Essayez, si ça coince conserver la votre si elle marche.

VB:

Public ValeurAE5    ' mémorise la valur de AE5 pour la restituer
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Planning Treso").Unprotect ("SC6")
Application.ScreenUpdating = False
If Target.Address <> "$AE$5" Then Exit Sub
If Not Application.Intersect(Target, Range("AE5")) Is Nothing Then
    If Range("AE5") <> "9" Then
        Columns("AT").EntireColumn.Visible = False ' mon souhait pour la colonne AT, elle se cache si AE5 est<> de 9 ou s'affiche si AE5 = 9
        ActiveSheet.Shapes("OK").Visible = True
        ret = MsgBox("Si vous changez la durée d'utilisation, le montant de l'épargne écourté sera réinitialisé ! ", vbYesNo + vbQuestion, "Excel")
        If ret = vbYes Then
            Range("AS17").Value = 0
        Else
            Range("AE5").Value = ValeurAE5  ' comme la réponse est non, on restitue la valeur initiale
        End If
    Else    ' ici AE5=9
        Columns("AT").EntireColumn.Hidden = True
        ActiveSheet.Shapes("OK").Visible = False
    End If
End If
Worksheets("Planning Treso").Protect ("SC6")
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("AE5")) Is Nothing Then
        ValeurAE5 = [AE5]              ' sauvegarde AE5 pour la restituer plus tard si réponge msgbox est non.
        UserForm1.Show
    End If
    Worksheets("Planning Treso").Protect ("SC6")
End Sub

pat66 · 10 Juillet 2020

Bonsoir Sylvanu, bonsoir le forum

un grand merci pour ton aide qui m'a fait gagner un temps fou, car c'est exactement ce dont j'avais besoin

Bonne soirée

Patrick

XL 2019 Private Sub Worksheet_Change(ByVal Target As Range)

pat66

XLDnaute Impliqué

sylvanu

XLDnaute Barbatruc

Pièces jointes

pat66

XLDnaute Impliqué

sylvanu

XLDnaute Barbatruc

pat66

XLDnaute Impliqué

sylvanu

XLDnaute Barbatruc

pat66

XLDnaute Impliqué

sylvanu

XLDnaute Barbatruc

pat66

XLDnaute Impliqué

sylvanu

XLDnaute Barbatruc

Pièces jointes

pat66

XLDnaute Impliqué

sylvanu

XLDnaute Barbatruc

pat66

XLDnaute Impliqué

sylvanu

XLDnaute Barbatruc

pat66

XLDnaute Impliqué

Discussions similaires

XL 2019 Private Sub Worksheet_Change(ByVal Target As Range)

XLDnaute Impliqué

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Impliqué

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Impliqué

Discussions similaires

Privacy & Transparency

Privacy & Transparency