Készítettetek már olyan adatbázist, riportot (stb.), amit aztán több ember is használt? Ha igen, valószínűleg előfordult már veletek, hogy egyszer csak nem működött a fájl: az összegzés nem a várt eredményt hozta, néhány kitöltött adat nem a megfelelő cellába került. Erre lehet megoldás adott munkalap(ok) celláinak monitorozása, naplózása.
Angolul erre számos kifejezés van, mint pl. “user log” vagy “user activity tracker”. Ezt ismertetném a mai alkalommal.
A VBA-s módszerem az alábbiakat végzi:
- Egy adott munkalap táblázat formátumának celláit “figyeli” és ha módosítás történik valamelyik cellában, akkor a változásokat egy nagyon rejtett (ún. “very hidden”) munkalapra kilistázza
- Azt is monitorozza, hogy a cella eredeti értéke változik-e, magyarán, ha a cella értékét ugyanarra frissítjük, mint ami volt, azt nem veszi figyelembe
- Akkor is működik, ha összefüggő vagy nem összefüggő tartományokat jelölünk ki és egyszerre frissítjük ezek értékeit (CTRL+Enter-rel)
Szemléltetés animáción keresztül:
A fenti animáción az első módosításnál vizuálisan is kiemeltem, miként működik az automatikus monitorozás. A további módosításoknál megfigyelhető, hogy a “UserLog” nevű munkalapon a változtatások miatt folyamatosan hozzáadódnak újabb és újabb sorok.
A módszer lényege az eseménykezelés, jelen esetben a “Worksheet_Change”. Ez azt jelenti, hogy ha a “Budget_2019” munkalapon változás történik, akkor a munkalaphoz hozzáadott “Private Sub Worksheet_Change(ByVal Target As Range)” makró automatikusan lefut és kilistázza a változtatásokat + néhány hozzáadott kategóriát a “UserLog” nevű munkalapon:
Az adatokat tartalmazó lap egyébként táblázat formátumú, de lehetne egyszerű tartomány is, persze akkor a VBA kódokat módosítani kellene.
A makrók:
Az adott munkafüzetben nyissuk meg a Visual Basic szerkesztőjét (ALT + F11-gyel). Majd kattintsunk duplán arra a munkalap névre, melyhez a kódot hozzá szeretnénk adni (itt a lap neve: “Budget_2019”) és másoljuk bele:
|
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 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
Dim Eredeti 'Készítette: XLMotyo (https://xlmotyo.hu) Private Sub Worksheet_SelectionChange(ByVal Target As Range) Eredeti = Target.Value End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim BudgetRange As Range, UserLogRange As Range, i As Long Set BudgetRange = Sheet1.ListObjects("BudgetTable").Range Set UserLogRange = Sheet2.UsedRange If Target.Cells.Count = 1 Then If Not Intersect(Target, BudgetRange) Is Nothing And Target.Value <> Eredeti Then Application.ScreenUpdating = False Sheet2.Unprotect "" i = UserLogRange.Rows.Count + 1 Sheet2.Cells(i, 1) = Sheet1.Name Sheet2.Cells(i, 2) = Now() Sheet2.Cells(i, 3) = Target.Address Sheet2.Cells(i, 4) = Eredeti Sheet2.Cells(i, 5) = Target.Value Sheet2.Cells(i, 6) = Environ("USERNAME") Sheet2.Cells(i, 7) = Application.UserName Sheet2.Protect "" Application.ScreenUpdating = True End If Else If Not Intersect(Target, BudgetRange) Is Nothing Then Application.ScreenUpdating = False Sheet2.Unprotect "" i = UserLogRange.Rows.Count + 1 Sheet2.Cells(i, 1) = Sheet1.Name Sheet2.Cells(i, 2) = Now() Sheet2.Cells(i, 3) = Target.Address Sheet2.Cells(i, 4) = "" 'Eredeti Sheet2.Cells(i, 5) = Target.Value Sheet2.Cells(i, 6) = Environ("USERNAME") Sheet2.Cells(i, 7) = Application.UserName Sheet2.Protect "" Application.ScreenUpdating = True End If End If Eredeti = Target.Value End Sub |
Az adott munkafüzetben nyissuk meg a Visual Basic szerkesztőjét (ALT + F11-gyel). Majd kattintsunk duplán a “ThisWorkbook” névre és a kódot adjuk hozzá:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
'Készítette: XLMotyo (https://xlmotyo.hu) Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call UserLogVeryHidden 'kivenni megjegyzésbõl, ha kész a fájl End Sub Private Sub Workbook_Open() Application.ScreenUpdating = False 'If Sheet2.Visible <> xlSheetVisible Then Sheet2.Visible = xlSheetVisible If Sheet2.ProtectContents = False Then Sheet2.Protect "" Application.ScreenUpdating = True Call UserLogVeryHidden End Sub |
Végezetül pedig szintén az adott munkafüzetben nyissuk meg a Visual Basic szerkesztőjét (ALT + F11-gyel). Majd szúrjunk be egy modult és az alábbi kódot adjuk hozzá:
|
1 2 3 4 5 6 7 |
'Készítette: XLMotyo (https://xlmotyo.hu) Function UserLogVeryHidden() As Boolean Application.ScreenUpdating = False If Sheet2.Visible <> xlSheetVeryHidden Then Sheet2.Visible = xlSheetVeryHidden Application.ScreenUpdating = True End Function |
Ez utóbbi a “UserLog” nevű munkalap láthatóságát állítja nagyon rejtettre (very hidden). Ez azt jelenti, hogy a változásokat listázó munkalapot Excel-ből nem, csakis VBA segítségével lehet láthatóvá tenni és a tartalmát megnézni.
Habár maga a téma egyszerűnek tűnik, korántsem az, hiszen:
- Beszúrhatunk/törölhetünk egész sor(ok) at vagy oszlop(ok)at, mégpedig többféleképpen: menüből, parancsikon menüből (mikor jobb egérgombbal kattintunk), makróval, billentyűparanccsal. Ezek felismerésére, illetve azonosítására nincs beépített Excel/VBA parancs.
- Levédhetjük a munkalapot és letilthatjuk a sorok/oszlopok beszúrását, illetve törlését, viszont számos esetben a felhasználónak hozzá kell adnia új sort a tartományhoz, valamint az adatbeviteli cellák módosítását akkor is nézni kell
- Ne feledkezzünk meg a különböző Excel verziókról sem, ahol mondjuk a parancsikon menük elemeinek más lehet az azonosítója beszúrásnál/törlésénél
- S akkor még mindig ott van az, amikor a felhasználó letiltja a makrókat és úgy nyitja meg a munkafüzetet
A fentiek ismeretében nem véletlen, hogy nem találni olyan kódot, amely minden kívánalomnak eleget tesz. Mindezek ellenére a fentebb bemutatott technika nagy segítség, hiszen ha valaki a nagyon rejtett státuszú “UserLog” munkalapba belepiszkál, az tudja mit csinál, vagyis tudatosan manipulálja az adatokat.
Alternatív megoldási javaslatok a fent bemutatott módszert kiegészítve vagy éppen helyette:
1. Le lehet védeni a munkafüzetet, munkalapot, tartományokat és több oldalas kódokat lehet írni, hogy a munkafüzetünk minél bolondbiztosabb (szebben szólva “bullet proof”) legyen. Megtehetjük, hogy makróval letiltjuk mindenhol pl. a sorok beszúrását és saját kódot írunk rá, így beazonosítva a beszúrt sor helyét és számát. Egy ilyen kódot rendkívül időigényes megírni és akkor sem biztos, hogy a felhasználó nem tiltja le a makrókat a munkafüzet megnyitása előtt vagy nem használja olyan módon a fájlt, amely hibát generál.
2. készítsünk másolatot az adott munkalapról és ha az aktuális munkalapon változás van: hasonlítsuk össze az eredeti és az új munkalapot, majd a különbsége(ke)t listázzuk ki. Ezt azonban érdemes minél gyakrabban (akár minden változtatásnál) megtenni, különben nem lesz könnyű dolgunk, ha az utolsó összehasonlítás óta mondjuk pár sor be lett szúrva vagy törölve lett.
