Lejárati dátumriasztó - az alapmegoldás és a VBA-s popup trükk

Egyikhez sem kell komoly tapasztalat és képzettség, csak engedjük szabadon gondolatainkat

2015. november 01. - Office Guru

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.

350.jpgAz 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:

351.jpgItt 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:

352.jpgHaszná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:

353.jpgÉ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.

354.jpgEzutá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:

355.jpgHa 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:

356.jpgErrő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:

357.jpgAzaz 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:

358.jpg

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr348041644

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Mercel 2016.08.18. 14:04:40

Hello!

Uhh ez egy nagyon jó poszt és nagyon jó a leírás is - Pont valami hasonlót kerestem, bár más célra.
Köszi!:)

blomi76 2018.10.09. 12:43:07

Szia!
Szuper poszt, pont erre lenne szükségem, de valamiért még sem működik.

(Első oszlopomban szerződés nevek vannak, másodikban meg a dátumok, második sornál kezdődnek az adatok.)

Ez van a VBA-ban:

Workbook/open:

Private Sub Workbook_Open()
Call popupuzenet
End Sub

General/popupuzenet:

Sub popupuzenet()
Dim sorszam As Integer
Dim a As Integer
Dim uzi As String
uzi = "A következo szerzodések lejártak: " & vbCrLf & vbCrLf
sorszam = ThisWorkbook.Worksheets("Munka1").Range("B" & Rows.Count).End(xlUp).Row
For a = 2 To sorszam
If ThisWorkbook.Worksheets("Munka1").Range("B" & a) - Date < 0 Then
uzi = uzi & ThisWorkbook.Worksheets("Munka1").Range("A" & a).Value & " "
End If
Next a
MsgBox uzi
End Sub

Ha megnyitom, mindig hibára fut.
A makróba meg automatikusan beírodik ez a (General)-hoz:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Ha ezt a pár sort kitörlöm, akkor jól lefut. De ha becsukok mindent és újra megnyitom, akkor megint előjön a probléma. :-(

Tudsz segíteni?
Köszi előre is!

Office Guru 2018.11.11. 15:57:09

@blomi76: Beraktam a ThisWorkbook objecthez a kódod, A oszlopra szerződészámokat, B oszlopba dátumokat írtam, nekem simán, elvárásnak megfelelően lefutott. Úgyhogy át kellene küldened a fájlod...