Zamknutie buniek tabuľky Excelu

Zapisovať dáta v tabuľke Excelu môže aj menej skúsený užívateľ. Vtedy ale skúsenejší kolega/kolegyňa musí zabezpečiť tabuľku tak, aby podľa možnosti začínajúci užívateľ nič nepokazil. V programe Microsoft Excel je mnoho spôsobov, ako obmedziť používateľov pri prezeraní a zmene údajov v zošitoch a hárkoch. Prvou možnosťou je využitie príkazu Údaje-overenie, ktorý máme v tabuľkách Excelu k dispozícii. Z obr.1 a z obr.2 je jasné, že môžeme zadať povolené hodnoty, prípadne medze. Pri chybnom zadaní sa objaví chybové hlásenie a údaje je treba zadať znova.

Druhou možnosťou je zamknúť zvolené bunky (najmä tie, v ktorých sú vzorce - aby menej skúsený užívateľ náhodou vzorce nevymazal) a zadať ochranu listu Excelu heslom. Môžeme to urobiť priamo v tabuľke cez Formát-bunky-ochrana. Zamkneme potrebné bunky, prípadne necháme skryť vzorce. Zamknutie bude účinné len ak zadáme Nástroje-ochrana-zabezpečiť hárok. Môžeme zadať aj heslo, ale nie je to povinné.
Tretia možnosť je zamknutie buniek a zabezpečenie listu prostredníctvom programu vo VBA. Túto možnosť ilustruje príklad, ktorý vidíme na obr. 3 a 4. Na obr. 3 je nezamknutá oblasť A1:E7, v riadku vzorcov je je viditeľný vzorec, ktorý je v bunke E2. Na obr. 4 je zobrazená tabuľka, ktorá bola zamknutá prostredníctvom kód VBA Triedi(). Zamknuté sú len tie bunky, v ktorých je vzorec a je usporiadaná podľa prvého stĺpca tabuľky, vzostupne podľa abecedy. V riadku vzorcov nevidíme vzorec ako na obr. 3, lebo sme ho nechali skryť. Zamknúť môžeme aj bez triedenia - kód VBA zamkni(). Napokon krátky program odomkni() zabezpečí odomknutie buniek. Užívateľ, ktorý nemá k dispozícii program, ktorým bol hárok zamknutý a opatrený heslom, ak vie heslo, môže aj bez programu bunky odomknúť.


Sub zamkni()
  ActiveSheet.Cells.Select
  Selection.Locked = False  ' odomkne bunky v celom liste
  ActiveSheet.Range("A2:E7").Select
  ' oblasť A2:E7 si zmeňte všade podľa Vašich potrieb
  For i = 1 To 7 ' riadky
        For j = 1 To 5 'stĺpce
            If Cells(i, j).HasFormula = True Then
            ' ak sú v makre bunky so vzorcami
                Selection.FormulaHidden = True
                ' vzorce skryje
                Cells(i, j).Locked = True
                ProtectContents = True
                ' bunky so vzorcami zamkne
            End If
        Next j
  Next i
  ActiveSheet.Protect "avozarm"
  ' opatrí list Excelu heslom avozarm
End Sub

Sub odomkni()
    ActiveSheet.Unprotect "avozarm"
    Range("A2:E7").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
End Sub

Sub triedí()
' odomkne a zoradí a potom zamkne len tie bunky, kde sú vzorce
    ActiveSheet.Unprotect "avozarm"
    Range("A2:E7").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  For i = 1 To 7 ' riadky
        For j = 1 To 5 'stĺpce
            If Cells(i, j).HasFormula = True Then
                Selection.FormulaHidden = True
                Cells(i, j).Locked = True
                ProtectContents = True
            End If
        Next j
  Next i
    ActiveSheet.Protect "avozarm"
End Sub

Príklady boli vypracované v Excel 2000 pod OS Windows XP
Horeuvedené makrá sú v súbore zamok.zip.
Stiahnite si zamok.zip Veľkosť: 10 kB

Overenie buniek