Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Obsługa zależnych zdarzeń Change i SelectionChange

Maciej Gonet

Analizując kod z innego wątku spotkałem się z problemem ogólniejszej natury, który może zainteresować szersze grono czytelników. Chodzi o równoczesną obsługę zdarzeń Change i SelectionChange. Nie zawsze pamiętamy, że gdy wzbudzane jest zdarzenie Change równocześnie z nim aktywowane jest zdarzenie SelectionChange, lecz jego obsługa jest odłożona aż do zakończenia obsługi zdarzenia Change. Jeżeli w trakcie obsługi zdarzenia Change spowodujemy kolejne zdarzenie SelectionChange, ono zostanie obsłużone od razu. Może to spowodować niekorzystne skutki uboczne, bo już po zmianie komórki aktywnej będzie obsługiwane zdarzenie odnoszące się do poprzedniego adresu.
Aby zablokować niepożądane wykonanie "nieaktualnego" zdarzenia SelectionChange można zastosować zmienną logiczną o charakterze flagi, ustawianą na końcu obsługi zdarzenia Change.
A oto prosty przykład kodu:

Kod:

Option Explicit
Dim niepowtarzaj As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Zmiana " & Target.Address
Target.Offset(2).Activate
niepowtarzaj = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If niepowtarzaj Then niepowtarzaj = False: Exit Sub
MsgBox "Selekcja " & Target.Address
End Sub

W przykładowym skoroszycie w Arkuszu1 wariant bez zmiennej pomocniczej, a w Arkuszu2 z tą zmienną. Proszę sobie przetestować kolejność wzbudzania zdarzeń w obu przypadkach.

Change_SelChange.xlsm

Pobierz Plik ściągnięto 23 raz(y) 15.05 KB

Rafał B. · Wysłany: 19-10-2020, 14:05

Cytat:

Nie zawsze pamiętamy, że gdy wzbudzane jest zdarzenie Change równocześnie z nim aktywowane jest zdarzenie SelectionChange, lecz jego obsługa jest odłożona aż do zakończenia obsługi zdarzenia Change.

Raczej nie jest to prawdą w sensie reguły, że zawsze tak jest - przykładem jest wybranie czegoś z listy rozwijalnej, które wywołuje _Change, ale już nie wywołuje _SelectionChange. Co tak na marginesie jest elementem jedynej znanej mi metody sprawdzenia czy lista walidacyjna na arkuszu została użyta. Ale temat sam w sobie ciekawy, i rozumiem, że dotyczy innych częstszych wariantów jak enter lub strzałka w bok przy wyjściu z trybu edycji. I faktycznie w tych sytuacjach trzeba podchodzić z ostrożniością do ciągu tych zdarzeń podobnie jak w Userformach- analizując ciąg zdarzeń powiązanych i podobnie: z użyciem flagi monitorującej zapobiegającej zdarzeniom niechcianym.

Maciej Gonet · Wysłany: 19-10-2020, 14:12

Ja to jeszcze muszę dokładniej przetestować, bo wczoraj chyba się trochę pospieszyłem z tym tekstem. Muszę to jeszcze przetestować w różnych wersjach Excela i przy różnych sposobach zatwierdzania zmiany. Sprawa jest chyba bardziej złożona niż myślałem.

Rafał B. · Wysłany: 19-10-2020, 14:43

Mnie się wydaje, że dobrze to opisałeś (pomijając to zdanie o nierozerwalnej koincydencji zdarzeń). Może tylko dla lepszego zobrazowania, wyobrażenia co się dzieje i w jakiej kolejności do testów w Twoim pliku zamiast Enter zleciłbym raczej użycie strzałki w bok. SelectionChange wywołuje się dwukrotnie: raz przez akcję klawiatury (enter/ strzałka w dół), a drugi przez kod - metodę .Activate. Jedyne co tu może być dla niektórych dziwne, to kolejność, którą właśnie dobrze opisałeś jako odwrotną do wyżej wymienionej: bo najpierw mamy wynikającą z .offset i .activate, a dopiero później z klawiatury.

Maciej Gonet · Wysłany: 20-10-2020, 13:04

Trochę to jednak bardziej skomplikowane niż myślałem wcześniej. Rzecz w tym, że zmiany w komórce można zatwierdzać naciśnięciem klawisza (Enter, Tab, Strzałki i podobne), a można też myszką przez kliknięcie w pole Wpis (ten haczyk przed paskiem formuły). W tym drugim przypadku komórka wybrana się nie zmienia i nie generuje się zdarzenie SelectionChange towarzyszące zdarzeniu Change.
Natomiast po użyciu klawisza do zatwierdzenia zmiany zawsze generuje się towarzyszące zdarzenie SelectionChange, nawet jeśli de facto nie było zmiany komórki wybranej (można wyłączyć domyślną opcję przeniesienia zaznaczenia po naciśnięciu klawisza Enter).
Jeśli ktoś będzie korzystał wyłącznie z klawiatury do zatwierdzania zmian, to może korzystać ze sposobu opisanego powyżej. Jeśli ktoś będzie korzystał wyłącznie z pola Wpis, to powinien zrezygnować z tej dodatkowej zmiennej (flagi), bo jej użycie w tym przypadku może spowodować "zgubienie" jednej selekcji.
Jak to rozwiązać w ogólnym przypadku - nie wiem - bo nie wiem jak programowo w prosty sposób rozróżnić, czy wyjście z edycji (akceptacja) nastąpiło przez użycie klawiatury czy myszki.
Jak ktoś ma na to w miarę prostą receptę, to proszę o podpowiedź.
W wątku, który mnie zainspirował do napisania tych wywodów, użycie myszki było niemożliwe, bo twórca tamtego arkusza ukrył pasek formuły, a wraz z nim pole Wpis. Zatem pozostawało tylko użycie klawiatury. Ale przy pozostawieniu pełnej funkcjonalności arkusza problem istnieje.

Rafał B. · Wysłany: 20-10-2020, 15:21

Maciej Gonet · Wysłany: 21-10-2020, 09:23

Nie analizowałem przypadku gdy arkusz jest chroniony. Przy jednej komórce odblokowanej dość trudno się pracuje. :-(

Więc to raczej "sztuka dla sztuki" :->

W ogóle przy chronionym arkuszu program zachowuje się trochę nietypowo. Gdy jest odblokowana więcej niż jedna komórka, niby można wyłączyć opcję "Przenieś zaznaczenie po naciśnięciu klawisza Enter", ale program zachowuje się tak jakby pamiętał poprzedni wariant opcji, to znaczy kierunek przenoszenia, jaki był wcześniej ustawiony i jeszcze do tego trochę inaczej obsługuje warianty "w lewo" i "w górę", a inaczej "w prawo" i "w dół".
Generalny wniosek jest taki, że wszystkie rozwiązania trzeba dokładnie testować w warunkach, w jakich mają być użyte, bo trudno przewidzieć wszystkie możliwe kombinacje ustawień i wersji programu.

Rafał B. · Wysłany: 21-10-2020, 09:57

Napisałem na szybko taki kod, pewnie trzeba poprawić obsługę błędów, ale wydaje się faktycznie rozróżniać czy nastąpiła zmiana selekcji. Użycie to oczywiscie przekopiowanie całego kodu do kodu arkusza, a właściwe makro nalezy podmienić w onSelectionChange. Ale pewnie wydajność może ucierpieć, wiec lepiej logikę programu inaczej poprawadzić...

Kod:

Option Explicit

Private Type TChange

Changed As Boolean
Range As Range
End Type

Private ChangeEvent As TChange
' -------------------------------------------------------//

Private Sub Worksheet_Change(ByVal Target As Range)

' set the flag
With ChangeEvent
.Changed = True
Set .Range = Target
End With
End Sub
' -------------------------------------------------------//

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' check if proceed
On Error GoTo RunMacro 'or check if .Range is not nothing
With ChangeEvent
If .Changed And Target.Address = .Range.Address Then
.Changed = False
Exit Sub
End If
End With

RunMacro:
Err.Clear
On Error GoTo OnExit
onSelectionChange rng:=Target

OnExit:
' set the flag back
ChangeEvent.Changed = False
End Sub
' -------------------------------------------------------//

Private Sub onSelectionChange(ByVal rng As Range)

' here's main macro on selection event
' better place this outside worksheet module
Debug.Print rng.Address
MsgBox "selection changed!"
End Sub
' -------------------------------------------------------//

Maciej Gonet · Wysłany: 24-10-2020, 22:27

Dziękuję za kod przesłany przez Rafała B., ale nie potrafię go zastosować w omawianym przypadku. Przypomnę, że chodzi o to, by w przypadku gdy w toku obsługi zdarzenia Change wystąpi zmiana komórki aktywnej, to żeby ta zmiana została obsłużona przez zdarzenie SelectionChange, natomiast należy zignorować w takim przypadku zdarzenie SelectionChange związane z zatwierdzeniem zmiany w komórce. Wszystkie inne kombinacje zdarzeń powinny być obsługiwane normalnie zarówno przy użyciu klawiatury, jak i myszki.
Jedyne rozwiązanie jakie udało mi się znaleźć wykorzystuje odczyt czasu (a więc i kolejności) wykonywania poszczególnych procedur. Mam nadzieję, że to będzie działać poprawnie.
W załączniku w Arkuszu1 wersja podstawowa bez żadnych modyfikacji jako ilustracja problemu.
W Arkuszu2 moja nieudana próba implementacji kodu Rafała B.
W Arkuszu3 wariant z odczytem czasu.

Change_SelChange_RB.xlsm

Pobierz Plik ściągnięto 17 raz(y) 21.21 KB

umiejead · Wysłany: 25-10-2020, 01:11

Dziękuję za kod przesłany przez Rafała B., ale nie potrafię go zastosować w omawianym przypadku.

Przypomnę, że: chodzi o to, by w przypadku gdy w toku obsługi zdarzenia Change wystąpi zmiana komórki aktywnej, to żeby ta zmiana została obsłużona przez zdarzenie SelectionChange, natomiast należy zignorować w takim przypadku zdarzenie SelectionChange związane z zatwierdzeniem zmiany w komórce.

Wszystkie inne kombinacje zdarzeń powinny być obsługiwane normalnie zarówno przy użyciu klawiatury, jak i myszki.

Jedyne rozwiązanie jakie udało mi się znaleźć wykorzystuje odczyt czasu (a więc i kolejności) wykonywania poszczególnych procedur. Mam nadzieję, że to będzie działać poprawnie.

Załączniki:
W załączniku w Arkuszu1 wersja podstawowa bez żadnych modyfikacji jako ilustracja problemu.
W Arkuszu2 moja nieudana próba implementacji kodu Rafała B.
W Arkuszu3 wariant z odczytem czasu.

---
Edit:
Pisane "jednym tchem".. heh....
(ciut poprawiłem).
umiejead
.

Rafał B. · Wysłany: 26-10-2020, 18:16

Sprytne i proste to rozwiązanie z Timerem 8-)

Oczywiście wymagałoby dodatkowych zabezpieczeń np. przed resetem o północy, ale to wiadomo.

Natomiast mój powyższy kod dotyczył jedynie prawdziwego rozpoznania zmiany selekcji (np. odporny na "fake" po Enterze, opisany przez Ciebie). Co do samego zadania opisanego w otwierającym wątku i w ostatniej wiadomości, to można też użyć dwóch flag. Z góry mówię, że dla większej czytelności rozwiązanie jest dla jednej komórki; dla większego zmienianego zakresu wymagana jest odpowiednia adaptacja. Wprowadziłem też enuma, chociaż wystarczy zwykła zmienna boolean- również dla większej czytelności.

Kod:

Option Explicit

Private ForceSkipOnSelection As Boolean
Private ChangeEvent As NChangeEvent

Private Enum NChangeEvent

WithSelectionChange
NoSelectionChange
End Enum
' ------------------------------------------------------------------//

Private Sub Worksheet_Change(ByVal Target As Range)

' set event status
ChangeEvent = VBA.IIf(Selection.Address = Target.Address, _
NoSelectionChange, WithSelectionChange)

' main event macro run
onChange Target

' set the flag
ForceSkipOnSelection = True
End Sub
' ------------------------------------------------------------------//

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' check if proceed
If ChangeEvent = WithSelectionChange Then
If ForceSkipOnSelection Then
ForceSkipOnSelection = False
Exit Sub
End If
Else
ForceSkipOnSelection = False
End If

' main event macro run
onSelectionChange Target
End Sub
' ------------------------------------------------------------------//

Private Sub onChange(ByVal CRng As Range)

Debug.Print CRng.Address
MsgBox "Zmiana " & CRng.Address
CRng.Offset(2).Activate
End Sub
' ------------------------------------------------------------------//

Private Sub onSelectionChange(ByVal SRng As Range)

Debug.Print SRng.Address
MsgBox "Selekcja " & SRng.Address
End Sub
' ------------------------------------------------------------------//

Kod więc jak widać jest kopią Twojego kodu z otwierającego posta w tym wątku, z dodaną kolejną flagą rozpoznającą zmianę selekcji wynikającej z zatwierdzenia klawiaturą- przy czym jest ona ustawiana tym razem w samym zdarzeniu _Change zamiast w _SelectionChange. Pozdrawiam!

Rafał B. · Wysłany: 27-10-2020, 08:26

Poddaję się, bardziej eleganckiego rozwiązania jednak nie znajdę niż z Timerem od Macieja Gonet. Moje powyższe nie jest niezawodne i nie rozwiązuje problemu. Z pewnością zachowanie Excela w tym zakresie można to uznać za bug od Microsoftu (skoro wymaga takich rozwiązań). Jak teraz szukałem w Internecie, to bug ten znany w dość wąskim kręgu ale od bardzo dawna.

Pozwolę sobie na trochę prostszy skrótowy opis problemu, temat w trakcie po części ewoluował:

Możemy wyróżnić:
-edycję komórki z zatwierdzeniem przez zmianę zaznaczenia.
Przykład: wpisujemy coś w A1 i klikamy strzałkę w prawo.
Wyzwalają się zdarzenia _Change oraz _SelectionChange

-edycję komórki z pozostaniem w tej komórce tj. bez zmiany zaznaczenia.
I tutaj już mamy dwa warianty zachowania VBA:

a) wpisujemy coś w A1 i klikamy znak √ przy pasku formuły.
Wyzwala się zdarzenie _Change

Wszystko intuicyjnie. Ale tutaj przechodzimy w końcu do absurdu:
b) odznaczamy w opcjach zaawansowanych "Przenieś zaznaczenie po naciśnięciu Enter".
Zmieniamy A1, klikamy Enter. Dalej zaznaczoną mamy komórkę A1.
Wyzwoliły się jednak oba zdarzenia _Change oraz _SelectionChange.

W początkowej fazie Maciej Gonet sugerował, że może to mieć związek z użyciem myszy/klawiatury do zatwierdzenia edycji komórki, więc można szukać rozwiązania pod tym kątem. Jednak przedstawiony w wątku przykład z zablokowaniem arkusza pokazał, że Enter ustawiony na brak zmiany zaznaczenia może zachowywać się dwojako - mimo braku zmiany zaznaczenia w zależności od kontekstu wywołać _SelectionChange, a raz nie.

Reasumując: dlatego nie możemy zastosować żadnej flagi, gdy chcemy uniknąć automatycznego zdarzenia _SelectionChange po zmianie zawartości komórki, bo istnieje ryzyko (najczęściej w wyniku znaku zatwierdzania √), że zgubimy zdarzenie _SelectionChange konieczne do naszej obsługi, niezwiązane z ostatnim zdarzeniem _Change.

Bardzo ciekawy temat, dzięki! Jeszcze taka uwaga na marginesie: skala nieznajomości tego błędu mimo wielu lat praktyki w VBA pokazuje, że praktycznie nikt nie korzysta z √ do zatwierdzania edycji komórki... Swoją drogą ciekawe jaką te ikonki w pasku formuły ×,√,ƒx mają fachową nazwę.

Maciej Gonet · Wysłany: 27-10-2020, 10:59

Dziękuję za podsumowanie tematu. Ja też myślałem nad tym w sobotę przez kilka godzin i nic mądrzejszego od Timera nie wymyśliłem.
Problemem jest nie tyle moment ustawienia flagi, ile moment jej zdjęcia.
A co do tych ikonek

Rafał B. · Wysłany: 30-10-2020, 13:59

Mimo wszystko odświeżę, bo nie daje mi spokoju poszukiwanie "prawilnego" rozwiązania...

Spróbowałem użyć obiektu monitorującego z WithEvents przechwytującego zdarzenia arkusza. Jakbyś mógł w wolnej chwili sprawdzić czy to rozwiązanie wyczerpuje założenia, dzięki! Przykład poglądowy dla jednego arkusza.

moduł klasy
WorksheetEventHandler

Kod:

Option Explicit

Private WithEvents MySheet As Worksheet ' Worksheet to monitor

Private Type TEventsDetails

ShouldContinue As Boolean
' Target on _Change event
RangeAfterConfirm As Range
' Flag is _Change event is active
ChangeRunning As Boolean
End Type

Private this As TEventsDetails
' ---------------------------------------------------------------------//

Public Sub SetSheet(ByVal ws As Worksheet)

Set MySheet = ws
End Sub
' ---------------------------------------------------------------------//

Private Sub MySheet_Change(ByVal Target As Range)

' event occured
Set this.RangeAfterConfirm = Selection
this.ChangeRunning = True

' run change macro
onChange targetRange:=Target

' event finished
this.ChangeRunning = False
End Sub
' ---------------------------------------------------------------------//

Private Sub MySheet_SelectionChange(ByVal Target As Range)

' test 1
this.ShouldContinue = this.ChangeRunning
' test 2
If Not this.RangeAfterConfirm Is Nothing Then
this.ShouldContinue = _
Target.Address <> this.RangeAfterConfirm.Address
Else
this.ShouldContinue = True
End If

' reset range
If Not this.ChangeRunning Then _
Set this.RangeAfterConfirm = Nothing

' check if macro proceed
If Not this.ShouldContinue Then Exit Sub

' run selection change macro
onSelectionChange targetRange:=Target
End Sub
' ---------------------------------------------------------------------//

zwykły moduł
Module1

Kod:

Option Explicit

Private ChangeEventMonitor As WorksheetEventHandler

Public Sub onWorkbookOpen()

' Set object to handle _Change /_SelectionChange worksheets event
Set ChangeEventMonitor = New WorksheetEventHandler
ChangeEventMonitor.SetSheet ws:=Arkusz1
End Sub
' ---------------------------------------------------------------------//

Public Sub onSelectionChange(targetRange As Range)

MsgBox "Selekcja: " & VBA.Replace(targetRange.Address, "$", vbNullString)
End Sub
' ---------------------------------------------------------------------//

Public Sub onChange(targetRange As Range)

MsgBox "Zmiana: " & VBA.Replace(targetRange.Address, "$", vbNullString)
targetRange.Offset(2, 0).Select
End Sub
' ---------------------------------------------------------------------//

Zauważyłem, że ilekroć myślę nad tym problemem, to po 20 minutach wszystko mi się zaczyna plątać... :mrgreen:

Event_kolejnyTest2.xlsb

Pobierz Plik ściągnięto 19 raz(y) 20.53 KB

Rafał B. · Wysłany: 30-10-2020, 14:48

Szkoda, że tylko 30 minut tutaj na forum jest na edycję. Coś tam jednak działa niestabilnie w tym rozwiązaniu, pomyślę jeszcze przez weekend.