Śledzenie zmian za pomocą Eventu

Śledzenie zmian w VBA

Śledzenie zmian to metoda na zabezpieczenie współdzielonego pliku, w którym wiele osób może dokonywać modyfikacji. Stare narzędzie Excela nie pozwala na korzystanie z niego we współdzielonych plikach. Pomyślałem więc, że można rozwiązać ten problem za pomocą zdarzeń w VBA.

Stworzyłem plik z 4 arkuszami i ostatni z nich nazwałem RaportZmian. Wstawiłem tam nagłówki kolumn, w których wprowadzane będą informacje dotyczące dokonanych modyfikacji.

SheetChange

SheetChange to event (inna nazwa to zdarzenie – będę ich zamiennie używał), który reaguje na zmiany dokonywane we wszystkich arkuszach skoroszytu. Aby go wprowadzić klikam dwukrotnie w Ten_skoroszyt w oknie projektów edytora VBA. Z rozwijanej listy po lewej stronie okna głównego wybieram Workbook i pojawia się pierwszy domyślny event Open. Zostawię go, ponieważ jeszcze mi się przyda, ale z listy po prawej stronie wybieram SheetChange.

To zdarzenie rejestruje dokonanie zmiany w dowolnym arkuszu w tym skoroszycie. Tworzy wtedy obiekt sh, czyli arkusz i obiekt target, czyli komórka (ewentualnie komórki). Wykorzystam je do uzyskania informacji na potrzeby mojego RaportuZmian.

Na początek muszę znaleźć pierwszy pusty wiersz do wprowadzenia nowego rekordu dlatego używam własności CurrentRegion i liczę ile zawiera wierszy. Oczywiście dodaję 1, aby uzyskać numer pierwszego wolnego wiersza. Deklaruję zmienną i jej przypisywać będę tę liczbę.

Teraz wprowadzam polecenia wypełniające pola w tabelce. Za każdym razem odwołuję się do arkusza, ponieważ zmiany będą rejestrowane na różnych arkuszach. Bez tego makro domyślnie wprowadzałoby wartości do aktywnego arkusza.

Nazwę użytkownika pobiorę za pomocą polecenia Environ(„Username”), a datę i godzinę za pomocą funkcji Now. Nazwę arkusza pobiorę z obiektu sh, a adres i aktualną wartość komórki z obiektu Target.

Muszę jeszcze zabezpieczyć makro przed działaniem w przypadku zmian dokonanych w arkuszu RaportZmian. Dodaję warunek, który na początku eventu sprawdza, czy nazwa arkusza, w której zarejestrowano zmiany jest inna niż RaportZmian.

Poprzednia wartość komórki

Żeby uzyskać informację o tym jaka była poprzednia wartość w komórce, muszę trochę pokombinować. Tworzę zmienną publiczną, która będzie przenosiła wartość pomiędzy wszystkimi procedurami w tym module. Zmienna ta nie będzie miała typu (domyślnie przyjmie typ Variant), ponieważ nie wiem jaka wartość będzie pobierana.

Na początek wykorzystam event Open, który już się pojawił na początku. W momencie otwarcia pliku będzie już jakaś komórka zaznaczona, więc jej wartość przypiszę do zmiennej.

Co jeśli użytkownik nie zmodyfikuje tej komórki tylko zaznaczy inną i dopiero ją zmodyfikuje? Wtedy wartość zmiennej powinna przed modyfikacją przyjąć wartość zaznaczonej przez użytkownika komórki. W tym celu dodaję zdarzenie SheetSelectionChange, które będzie wartość zaznaczonej komórki pobierało do zmiennej.

I ostatni przypadek to zmiana arkusza po której użytkownik może chcieć zmodyfikować zaznaczoną już komórkę. Żeby zabezpieczyć taką sytuację dodaję event SheetActivate z takim samym poleceniem przypisania wartości aktywnej komórki do zmiennej.

W zdarzeniu wprowadzającym wartości do tabeli dodam jeszcze polecenie Cells.Columns.Autofit, które będzie dopasowywać szerokość kolumn do wprowadzanej wartości.

Teraz wystarczy dopisać nazwę zmiennej do polecenia wprowadzającego jej wartość do RaportuZmian i własne Śledzenie zmian gotowe.

Jeśli chcesz zobaczyć w jaki sposób tworzę to makro na filmie to sprawdź na YT: