Office Guru

Interakció a felhasználóval Excelben UserForm segítségével

2016. február 23. - Office Guru

Rövidebb kihagyás után egy, Exceles automatizációk során elengedhetetlen elemmel fogunk megismerkedni, mert a UserFormok használata jelentősen javíthatja a felhasználóinkkal az interakciót, sőt, ezzel instruálhatjuk is őket bizonyos lépések végrehajtására.

Hogy kezdjünk neki? Első lépésként lépjünk a Visual Basic Editorba a Ribbonról vagy az Alt+F11 billentyűkombináció lenyomásával:

s01.jpgHa bent vagyunk az Editorban, akkor az Insert menü UserForm parancsával szúrjuk be a felületünket:

s02.jpgMost majd erre a formra fogjuk elhelyezni mezőinket illetve azok neveit. A mezők elnevezéseit értelemszerűen a Label control gombjára való kattintással tudjuk beszúrni:

s03.jpg

s04.jpgEzután következhet a nevekhez tartozó, adatbevitelt lehetővé tevő mező, azaz a Textbox:

s05.jpgÉrtelemszerűen jobb gombbal kattintva formunk bármely elemén, annak tulajdonságait a megjelenő Properties ablakban változtatni tudjuk:

s06.jpgA UserFormmal kapcsolatban lehetünk teljesen kreatívak, olyat készítünk, amilyet csak akarunk vagy amilyenre szükségünk van, én most még egy gombot fogok elhelyezni ezen a formon, amivel a megadott adatokat egy Excel-táblázatba fogom rögzíteni. Ezt a Controlok közül a CommandButton beszúrásával érem el:

s07.jpgÍgy néz ki most a form:

s08.jpgHa a felület elkészült, meg kell határoznunk a mögötte lévő kódot, ami jelen esetben a Hozzáadás gombra való kattintás esetén futna le. Ezt a CommandButtonon jobb gombbal katintva előhívható menüből a View Code parancs használatával határozhatjuk meg:

s09.jpgMit is várunk el a kódtól a gombra való kattintás után? Az elvárásaink szerint meghatározott sorok meghatározott celláiba a UserFormon megadott értékeket rögzítse be. Ehhez nagyjából tudnunk kell, hogy hol van a következő üres sor a táblában majd a kitöltés után értelemszerűen egyenlővé kell tennünk a megfelelő cellákat a megfelelő mezőkbe felvitt értékekkel.

Kezdésként definiáljunk két változót, az elsőt sor néven az aktuális, még üres sorunk meghatározására Integer típussal, a másodikat pedig ws néven Worksheet típussal, hiszen ez fogja meghatározni, hogy melyik munkalapon fogja a formunk frissíteni a cellákat. Ez utóbbi változónak már be is állíthatjuk az értékét a Sheet1 munkalapra.

s10.jpgSor változónk kezdő értékének meghatározásához a Range.Find metódust, jelen esetünkben a ws.Cells.Find metódust kell használnunk, hiszen a Sheet1 cellái között keressük azokat, amelyek tartalmaznak valamit (What:="*"), soronként keressük (SearchOrder:=xlRows - a másik opciónk az xlColumns lehetne) az utolsó (SearchDirection:=xlPrevious - ez még xlNext lehetne, ha a következőt keresnénk) értéket (LookIn:=xlValues) tartalmazó sort, majd ehhez a számhoz hozzáadunk egyet, hogy megkapjuk az első üres sort:

sor = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Innentől kezdve pedig alapvetően egyszerű a dolgunk, hiszen a mezőkbe beírt értékeinkkel kell egyenlővé tenni a sor változónkban lévő sorszám megfelelő számú celláinak értékét.

Mezőink nevét a UserFormon előhívott Properties ablakból is megtudhatjuk, ha esetleg nem standard elnevezéseket használtunk végig.

Azaz ws.Cells(sor, 1).Value (ez Sheet1 első üres sorának első oszlopában lévő cella) legyen egyenlő Me.TextBox1.Value értékkel, ami az első mezőnkbe beadott érték. Me minden esetben arra a szülő objektumra utal, amiben a kódunk "van", jelen esetben a UserFormra utal, de ha egy Sheeten írnánk a kódunkat, akkor arra utalna.

s11.jpgItt célszerű esetleg informálni a usert arról, hogy mi történt a bevitt adatával, tehát az MsgBox utasítás segítségével írjunk ki neki egy üzenetet. Ezen a felugró üzenetablakon most csak egy OK gomb legyen (vbOKOnly paraméter), de ezt értelemszerűen az MsgBox további paraméterei segítségével úgy tuningoljuk, ahogy akarjuk.

s12.jpgHa ez megvan, akkor szépen töröljük ki mezőinkből a bevitt értékeket (hiszen azok már a megfelelő cellákban vannak) egyszerűen úgy, hogy az Me.TextBox1.Value értékét a semmivel tesszük egyenlővé. Ezután pedig a SetFocus paraméter segítségével célszerű visszaugrasztani a kurzort az első mezőbe.

s13.jpgAlapvetően minden kis kódot a lehetséges hibák minimalizálásával célszerű megírni, ezért érdemes esetleg arra beszúrni egy If elágazást, hogy üresen hagyott mezők esetén ne csináljon semmit a UserForm. Hogy csináljuk ezt meg? Egyszerűen fordítsuk át gondolatainkat a kódra, azaz ha (If) a szóközök eltávolítása (Trim) után első mezőnk értéke (Me.TextBox1.Value) egyenlő a nagy semmivel (= "") akkor (Then) írjon ki egy üzenetet (MsgBox "A form nincs kitöltve!"), majd lépjen ki (Exit Sub) és ezzel le is zárhatjuk az If elágazást (End If).

If Trim(Me.TextBox1.Value) = "" Then
MsgBox "A form nincs kitöltve!"
Exit Sub
End If

Itt a kód szövegként:

Private Sub CommandButton1_Click()
Dim sor As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
sor = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(sor, 1).Value = Me.TextBox1.Value
ws.Cells(sor, 2).Value = Me.TextBox2.Value
ws.Cells(sor, 3).Value = Me.TextBox3.Value
MsgBox "Berögzítve", vbOKOnly
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox1.SetFocus
End Sub

Mi van még hátra? Hát annak meghatározása, hogy mi "triggerelje" a kis kódunkat, azaz hogy induljon el. Itt beállíthatunk eseményeket vagy billentyűkombinációt is akár, én most szimplán hozzárakom egy gombhoz a kód indulását. A Ribbonunk Developer füle alatt található Controls szekció Insert utasítása segítségével szúrjunk be egy gombot:

s14.jpgDesign Mode-ban duplán kattintva a gombra átjutunk a Visual Basic Editorba, ahol a kattintás eseményéhez mindössze az általam Adatbevitelformként elnevezett UserForm megmutatására (Show utasítás) van szükség:

s15.jpgÉs ezzel kész is vagyunk, a gombra való kattintással feljön a UserForm, amelynek kitöltése után az adatok bekerülnek a megfelelő cellákba.

s16.jpgEz most egy roppant módon lebutított kis példa volt, de remélhetőleg elég ötletet és támaszt adott egy-két Olvasónak, hogy komolyabb irányba induljon saját kis projektjével. Kritika, javaslat, ötlet, mint mindig, most is szívesen látott. 

A bejegyzés trackback címe:

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

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.

I_Isti 2016.02.25. 11:43:43

Ez (mármint az userform kezelése) az excel 2000-ben még _nagyon_ bugos volt.
Az excel 2003-ban még mindig nagyon bugos volt.
Az excel 2007-ben pedig már csak nagyon bugos.
...
Ebből következően egyszerűen nem hiszek abban, hogy bármelyik excel verzióban nem bugos...

lepereg 2016.05.08. 14:25:33

A téma érdekes, különösen azért, mert egy adatsor beírására jó a módszer. Amikor azonban megpróbálunk egymás után beírni több adatsort, akkor meglepő dolgot tapasztalunk. Az előző adatok ott maradnak a szövegmezőkben, akkor is, ha a cellákból kitöröljük ezeket. Arra, keresek választ, hogy hogyan törölhetem a már beírt adatokat a szövegmezőkből is?

exbattery 2016.06.28. 12:13:11

Nagyon köszi, hogy megosztod a tudásod velünk! :)
süti beállítások módosítása