Bár már az Excel egy igen előrehaladott verziójánál járunk, mégis minden egyes nap találkozunk és találkozhatunk olyan felhasználói igényekkel, amelyeket beépített funkciókkal képtelen vagyunk elérni - legyen szó akár faék egyszerűségű felvetésekről. Ilyen például az az igény, hogy egy Excel-fájlban azoknál a celláknál, ahol az eredményt formulák adják meg, ne legyen látható a formula és még esetleg az adott cella védett is legyen, tehát ne lehessen törölni a formulát. Agyonképletezett dashboardok megőrzése az utókornak vagy éppen egy tesztkérdéseket tartalmazó munkalap lehet az oka egy ilyen igénynek, amelyre nem is teljesen igaz az a válasz, hogy nincs ilyen standard megoldás.

Ahogy ezen a példán is látható, van egy egyszerű szummánk az egyik oszlopban, ami két másik cella tartalmát összesíti:

Célunk, hogy a felhasználó ne láthassa, milyen képlet van az adott cella mögött és esetleg ne is tudja azt módosítani. Első lépésként jobb gombbal a formulát tartalmazó cellára állva hívjuk elő a Context menüt, majd válasszuk a Format Cells menüpontot:

Itt pedig a Protection fül alatt kell bepipálnunk a Hidden checkboxot (és esetleg a Locked checkboxot) és már majdnem ott is vagyunk. Ami még hiányzik az az, hogy a Review ribbonfül alatt található Changes szekcióból a Protect Sheet menüt válasszuk, majd védjük le a munkalapot.

Így innentől kezdve, ha ráállunk erre a cellára, akkor többé már nem lesz látható a formula:

Amiért ezt a megoldást sokan nem szeretik az az, hogy hiába pipálunk be a levédésnél minden lehetőséget, hogy felhasználóink az összes felkínált tevékenységet meg tudják tenni, mégis van egy-két standard funkció, ami már így sem érhető el, ráadásul egy levédett munkalap mindig veszélyes, hiszen a jelszó nem minden user számára ismert (na, nem mintha ez sok VBA-guru számára a legcsekélyebb problémát is jelentené).

Erre is elég sokféle VBA-s megoldás van, amelyekben egy közös vonás mindig van, a kódok azt figyelik, hogy egy adott tartomány vagy kijelölés cellájában-celláiban van-e formula és ha igen, azonnal lockolja a cellát és elrejti a formulát, majd levédi a munkalapot - ha nincs formula a cellában, akkor értelemszerűen nem történik semmi.

A mai posztban készítünk egy roppant egyszerű kis verziót ebből a kódból, ami ugyan nem fedi le az összes hibalehetőséget, nem fog tartományokat kezelni, de remélhetőleg kiindulópontnak jó lesz azon Olvasó számára, aki pont ilyesmire keresi a választ.

Szépen lépjünk be ALT+F11 lenyomásával a VBA-editorba, majd kezdjünk is neki a kódunknak, aminél fontos, hogy az adott munkafüzet SheetSelectionChange eseményéhez rendeljük hozzá. Azaz ha változik a kijelölésünk, azaz bárhova átkattintunk a sheeten, azonnal fut a kód.

Alapesetben indítsunk azzal, hogy mindig feloldjuk a munkalap védettségét az általunk meghatározott jelszóval, majd a kijelölt cella Locked és Hidden tulajdonságát kapcsoljuk nulla fokozatba, azaz mintha a pipa nem lenne ott ebben a két checkboxban. Ezt úgy érjük el, hogy felhasználjuk az adott kijelölés (Selection) Locked és FormulaHidden tulajdonságát, amelyeket Falsera kell állítanunk.

Private Sub Workbook_SheetSelectionChange(ByVal sheet As Object, ByVal Target As Range)
sheet.Unprotect Password:="officeguru"
With Selection
.Locked = False
.FormulaHidden = False

Vegyük észre, hogy a With...End With utasítást használtuk, ami ugyebár végrehajtja azokat az egy bizonyos objektumra, struktúrára vonatkozó utasításokat, amelyek a With és az End With között helyezkednek el.

A folytatás pedig a kód lelke, azaz megnézzük, hogy a kijelölés változásával az újonnan kiválasztott cella (Target) HasFormula tulajdonsága igaz vagy hamis, azaz tartalmaz-e formulákat vagy sem.

Azaz megfogalmazva nagyjából azt fogjuk tenni, hogy ha az új kijelölésünk tartalmaz formulát (If Target.HasFormula), akkor (Then) az új kijelölés (With Target) locked és hidden tulajdonságát (.Locked és .FormulaHidden) állítsa igazra, majd lezárjuk a With utasítást.

Private Sub Workbook_SheetSelectionChange(ByVal sheet As Object, ByVal Target As Range)
sheet.Unprotect Password:="officeguru"
With Selection
.Locked = False
.FormulaHidden = False
End With
If Target.HasFormula Then
With Target
.Locked = True
.FormulaHidden = True
End With

Ami még hátra van az az, hogy ilyen esetekben (amikor az új kijelölés tartalmaz formulát) a munkalapot védjük is le a jelszavunkkal, illetve a UserInterFaceOnly argument igazra állításával engedélyezzük azt, hogy a jelszóval védett munkalapon továbbra is futtathassunk VBA-kódot.

Private Sub Workbook_SheetSelectionChange(ByVal sheet As Object, ByVal Target As Range)
sheet.Unprotect Password:="officeguru"
With Selection
.Locked = False
.FormulaHidden = False
End With
If Target.HasFormula Then
With Target
.Locked = True
.FormulaHidden = True
End With
sheet.Protect Password:="officeguru", UserInterFaceOnly:=True
End If
End Sub

És ennyi a történet. Nem a legszebb megoldás és egyértelműen nem gyorsítja a fájlunkban a munkát, de ha el akarjuk kerülni a teljes levédést és inkább ki-be kapcsolgatnánk, akkor errefelé keresgéljünk.