A mai posztban ismét szemezgetek a beérkező kérdésekből, ötletekből (bárkinek bármi Office-kérdése van, bátran írjon!), hiszen elég sokfajta problémával küzdünk nap mint nap, tehát lehet, hogy másnak is jól fog jönni, ha egy-egy kérdés válaszát közzéteszem itt.

A mai téma egy egészen friss kérdés: a kedves beküldő azt szerette volna elérni, hogy egy neveket és lejárati dátumokat tartalmazó táblázatban valahogy kiemelésre kerüljenek a már lejárt dátumok.

Azaz ha a lenti mintatáblázatot nézzük, akkor belépéskor egyértelműen lássuk, hogy mely dolgozók szerződése van lejárt állapotban, sőt még jobb lenne, ha erről belépéskor már üzenetet is kapnánk.

Az alapmegoldás egyértelműen a Conditional Formatting használata, szépen jelöljük ki a megfelelő dátumot tartalmazó oszlopot (célszerű egész oszlopot és nem tartományt kijelölni, hogy a későbbi bővítés ne okozzon pluszmunkát), majd a Ribbonunk Home füle alatt található Styles szekcióból hívjuk elő a Conditional Formatting legördülő menüjét és kattintsunk a New Rule menüpontra:

Itt válasszuk a második szabálytípust, azaz Format only cells that contain típust, majd a szabályt úgy állítsuk fel, hogy azon cellaértékek esetén, amelyek mondjuk kisebbek mint a mai dátum, állítsa a cella hátterét pirosra:

Használjuk bátran a már korábban megismert =TODAY() függvényt, amely szépen megadja mindig a mai napot a szabályunkba. Ha ezt leokézzuk, azonnal láthatóvá válik az eredmény:

Értelemszerűen ezt bármikor csiszolhatjuk még azzal, hogy a mai nap beleszámít-e vagy sem, de akár percre pontosan is belőhetjük a formázási feltételt stb.

Na de ez még nem lenne annyira szuper, ha nem építenénk be egy aprócska kis VBA-kódot az Excel fájlunkba, ami annyit fog intézni nekünk, hogy a fájl minden egyes megnyitásakor apró pop-up üzenetben ki fogja írni azon dolgozóink nevét, akiknek már lejárt a szerződése.

Úgyhogy menjünk is át gyorsan a VBA szerkesztőbe, majd itt a fájlunkon belül található ThisWorkbook objektumba vigyük fel a következőt:

Private Sub Workbook_Open()
Call popupuzenet
End Sub

Ez annyit fog csak tenni, hogy minden egyes megnyitáskor meghívja a popupuzenet subot, ami majd értelemszerűen a pop-up üzenetet fogja feldobni - persze általában nem ez a sorrend szokott lenni, de most biztosra akarok menni, nehogy menet közben elmaradjon.

Ezután pedig hozzuk létre a Sheet1 objektumon belül a popupuzenet subot, hogy az megcsinálja nekünk a szükséges felugró ablakot és üzenetet. Miután korábbi posztokban már kódolgattunk kicsit VBA-ban, egy-két dolgot annyira részletesen már nem fogok bemutatni, mint például most a változók meghatározását. Legyen elég annyi, hogy hozzunk létre három változót, egyet oszlopunk maximális sorszámának tárolására, egyet az aktuális sorunk számának tárolására és egyet szövegként, konkrétan az üzenetünk szövegének tárolására:

Ha ezzel megvagyunk, akkor töltsük fel az uzi és sorszam változókat a kezdőértékeikkel! Uzi változónk tartalmazza majd azt a szöveget, amelyhez hozzárakjuk a már lejárt szerződéssel bíró dolgozóink nevét, sorszam változónk pedig azt az értéket fogja tartalmazni, ahány sorunk van a lejárati dátumot tartalmazó, jelen esetben G oszlopunkban:

Erről azért még beszéljünk egy kicsit, az uzi változó feltöltése valószínűleg nagyjából egyértelmű, azt érdemes megjegyezni, hogy a vbCrLf utasítás sortörést hajt végre, azaz mintha nyomnánk egy ENTER-t szövegünk után. A sorszam változó feltöltése sem annyira komplikált, mint elsőre tűnik, fogjuk az első sheetünket a ThisWorkbook.Worksheets("Sheet1") utasítással, Range("G" & Rows.Count) segítségével pedig G oszlopunkban megszámoljuk a sorok számát (ergó akár írhatnánk azt is, hogy Range("G5:G15"), csak azzal deklarálnánk a tartományunk maximális mértékét, majd End(xlUp).Row segítségével megszerezzük, hogy pontosan hányadik az utolsó olyan cella az oszlopban, amely még tartalmaz értéket - mintha nyomnánk egy CTRL+Lefelé nyilat a tartományunkon belül. Ergó ezzel a sorszam változót feltöltöttük tartományunk nagyságával, tehát meddig terjed "lefelé".

A következő és egyben nagyjából az utolsó lépés is a megoldásban az, hogy egy For..Next ciklus segítségével minden egyes cellánkban ellenőrizzük le, hogy a dátum korábbi-e mint a mai nap vagy sem és ahol már lejárt a határidő, ott töltsük be az ehhez tartozó nevet az uzi változónkba, hogy meg tudjuk jeleníteni.

A For...Next utasítás a közötte szereplő utasításokat hajtja végre annyiszor ahányszor erre utasítjuk, azaz például a

For a = 5 to sorszam
...
Next a

egészen addig fogja a ... helyére írt utasításokat végrehajtani az ötös értéktől kezdve, amíg el nem éri a sorszam változó értékét. A mi esetünkben ezt még majd egy kicsit meg kell tuningolni egy If utasítással is és valahogy így fog majd kinézni a megoldás:

Azaz miután az ötödik sortól kezdődik a tartományunk, így ettől a sortól kezdve egészen a sorszam változóban szereplő értékig ismételje az If..End If közé zárt utasítást, tehát nézze meg, hogy aktuális sheetünk G oszlopának éppen a változóban szereplő sorában a dátum és a mai dátum különbsége nullánál nagyobb vagy kisebb-e:

ThisWorkbook.Worksheets("Sheet1").Range("G" & a) - Date <= 0 Then

VBA-ban a Date utasítás jelenti a mai dátumot, tehát mintha TODAY() függvényt használnánk Excelben.

Szóval ha a különbség kisebb, mint nulla akkor (Then) az uzi változóban eddig szereplő üzenetünket (amit az előbb már beírtunk konstansként az uziba) bővítse ki a sheetünk F oszlopának aktuális sorában szereplő nevével és adjon hozzá még egy szóközt, majd folytassa a ciklust.

uzi = uzi & ThisWorkbook.Worksheets("Sheet1").Range("F" & a).Value & " "

Ezzel már csak egy lépés maradt hátra, MsgBox utasítással (ez használható üzenetek megjelenítésére a képernyőn, rengeteg különféle paramétere van), írassuk ki az uzi változót a képernyőre a Workbook minden egyes elindulása esetén, valahogy így: