Kako sa VBA kodom vremenski
ograničiti korištenje ili upotrebu Sheeta ili Workbook u
Excelu 2007 do određenog ili specifičnog datuma
U ovom tutorijalu prikupio sam sa
interneta (raznih autora) nekoliko VBA makronaredbi koje su
vezane za ograničavanje korištenja radne knjige (Workbook)
ili radnog lista (Worksheet). Da se razumijemo, ova
ograničenja su za prosječnog korisnika Excel datoteke.
Korisnik sa naprednim znanjem može zaobići ova ograničenja
ako zna i ima vremena. Ako ste administrator određenih
datoteka pokušajte što bolje zaštititi pristup u VBE vašeg
Excela. Ovaj tutorijal ispod namijenjen je korisnicima
Excela 2007. Makronaredbe možete samo obrađivati,
mijenjati i prilagođavati vašim potrebama i znanju. Ja sam
složio funkcionalan tutorijal a ostalo je na vama i vašim
potrebama. Želim odmah napomenuti da nema 100% zaštite i svaka se može ukloniti ako se netko zainati i da si truda.
Imamo slijedeću situaciju: Tutorijal pisan
7.4.2013
Jednu Workbook koristi više korisnika a može samo jedan
(nebitno). Prilikom
otvaranja Workbook potrebno
je znati lozinku (password) koja je u ovom
slučaju 123, da bi se Workbook mogla uopće
koristititi (ovo takođe možemo datumski ograničiti). U samoj
Workbook nalazi se više Sheets (Radnih listova). Prilikom
otvaranja Workbook uvijek smo pozicionirani na Sheet1 jer na
njemu su napisane upute za rad sa aplikacijom koju smo
izradili u dotičnoj Workbook.
Pojedinim Sheets ograničili smo pristup sa lozinkom abc i qa
ovisno koji Sheet želimo zaštititi (u ovom tutorijalu
zaštićeni su Sheet1 (lozinka abc) i Sheet2 (lozinka qa -
ovo je lozinka QA ali malim slovima). Za pristup
svakom pojedinačnom Sheetu možemo postaviti ograničenje tj.
zabraniti pristup nakon određenog ili specifičnog datuma.
Kada određeni datum prođe korisniku Workbook prilikom
pristupa na pojedini Sheet može se pojaviti poruka da je rok
upotrebe ili pristupa istekao i da kontaktira autora Excel
datoteke ili administratora. Ovaj file (datoteka) kreiran je
u Excelu 2007 i snimljen kao *.xlsm (jer koristi
makronaredbe). Također je potrebno
blokirati pristup u VBE preko
lozinke (passworda)
NAPOMENA: Nema 100% zaštite putem
makronaredbe jer napredni korisnik može zaobići zaštitu
putem
povećanja sigurnosti u Excelu
ili pritiskom tipke SHIFT prilikom pokretanja datoteke ili
već njemu znanim načinima "hakiranja".
Ograničenje pristupa u Workbook pomoću VBA prilikom
pokretanja Excel datoteke (limitirana do nekog određenog datuma)
Samo ograničenje već je postavljeno
pristupnom lozinkom prilikom
pokretanja datoteke i pristupa u Workbook gdje
možemo pristupiti ako znamo lozinku koja je postavljena na
globalnoj razini cijele Workbook. Inače ovu zaštitu sa
lozinkom na globalnoj razini možemo postaviti i preko
makronaredbe (što je po meni manje sigurna)
Osim ovog načina možemo
ograničiti uopće pristup kada prođe neki datum koji smo
uzeli kao limitirani vremenski period. Uzmimo za primjer da
želimo spriječiti pokretanje Workbook ako je datum prošao.
Tada otvorimo VBE (Visaul Basic Editor) preko ALT+F11
kombinacije tipki i u modul ThisWorkbook postavimo
ovakvu ili sličnu makronaredbu. Ako želimo samo pristup u
Workbook sa lozinkom bez limitiranja jednostavno zadnji dio
naredbi koji se odnosi na ograničenje postavimo u komentar
(dodamo apostrof ispred)
Pristup u Workbook via VBA
Private Sub Workbook_Open()
'pristup u workbook na
globalnoj razini
Sheets("Sheet1").Activate
'naredba za pozicioniranje na Sheet1
Dim pswrd As String, rok As Date
'postavljanje varijabli za pswrd i rok
rok = DateSerial(2012, 5, 30)
'varijabla rok jednaka je
postavljenom datumu tj. limitirani datum za
otvaranje Workbook u ovom slučaju je to 30.5.2012
'datum poisanja ovog tutorijala je 7.4.2013
'ako ostavimo ovaj datum tada će se Workbook automatski
zatvoriti jer je stariji od današnjeg
'ali ako postavimo datum (2020, 5, 30) tada ćemo
pristupiti u Workbook jer do isteka roka tj. navedenog
datuma ima još vremena
'--------------------------------------------
pswrd = InputBox("UPIŠITE LOZINKU ZA PRISTUP U
WORKBOOK")
'dijalog prozor koji
se pojavi prilikom otvaranja Workbook
If
pswrd = "123" Then
'prvi IF uvjet - ako je
pasword ispravno upisan tada nastavi dalje na slijedeći
uvjet
If Date > rok Then
'drugi IF uvjet - ako je današnji datum veći od limitiranog tj. ako je
istekao tada uradi po naredbi ispod
MsgBox "ISTEKAO JE DATUM ZA KORIŠTENJE! kontaktirajte
autora" 'u slučaju da
je datum istekao pojavljuje se poruka
ActiveWorkbook.Close 'zatvara workbook ako je datum istekao
- u ovom dijelu možemo postaviti i drugi događaj
End If
'zatvaranje drugog IF-a
End If
'zatvaranje prvog IF-a
End Sub 'zatvaranje
procedure tj. kraj procedure
Dakle ova makronaredba iznad, prvi nivo zaštite može nam
služiti kao potreba upisa lozinke za pristup a također kao
limitirana upotreba.
Ograničenje pristupa u WorkSheet pomoću VBA
prilikom klika na naziv radnog lista (limit do nekog određenog datuma)
S obzirom na situaciju koju sam opisao na početku tutorijala
dolazimo do ulaska i pristupa u samu Workbook pod
pretpostavkom d asu nam svi uvjeti pozitivni. Pozicionirani
smo na Sheet1 kao startnu poziciju za rad u dotičnoj
Workbook (radnoj knjizi). Slijedeći naš korak je npr.
pristupiti na Sheet2 da bi dodali nekakve podatke u tablicu.
Kliknemo na Sheet2, pojavi nam se dijalog prozor na kojem
nas Excel upozorava da upišemo pristupnu lozinku.
Makronaredba koja postavlja password (lozinku) za pristup na
Sheet2 (vrijedi općenito na bilo koji Sheet uz male izmjene
koda) i također gleda da li je istekao rok upotrebe tj. je
li današnji datum veći od limitiranog datuma koji smo
postavili kao granicu za korištenje i pristup na Sheet2. Da
se odmah razumijemo, za svaki Sheet pojedinačno možemo
postaviti različite specifične ili limit datume.
Makronaredba koja je upotrijebljena za ovaj tutorijal
postavljena je u Sheet2 (klik desnom tipkom miša na naziv
Sheet2 => View Code ili odradite to
kroz VBE sa ALT+F11).
Dakle ovu makronaredbu (macro) ispod
zalijepite u
Sheet2 u Excel VBE
Pristup na Sheet2 via VBA
Private Sub Worksheet_Activate()
'naredba za aktiviranje
dogadjaja prilikom pristupa na Sheet2
Dim pswrd As String
'postavljanje varijable pswrd
Dim rok As Date 'postavljanje
varijable rok
pswrd = "abc"
'lozinka jednaka je upisanome - postavljanje lozinke ili passw
rok = DateSerial(2020, 5, 30)
'varijabla rok jednaka je
postavljenom datumu tj. limitirani datum za
otvaranje Workbook u ovom slučaju je to 30.5.2012
'datum pisanja ovog tutorijala je 7.4.2013
'ako ostavimo ovaj datum tada ćemo moći pristupiti u Worksheet
'ali ako postavimo datum (2010, 5, 30) tada nećemo moći
pristupiti u Workbook jer je istekao rok
'--------------------------------------------
pswrd = InputBox("UPIŠITE VAŠU LOZINKU ZA PRISTUP U
WORKSHEET 2") 'dijalog prozor
za upis lozinke ili password
If pswrd = "abc" Then
'prvi IF uvjet - ako je
pasword ispravno upisan tada nastavi dalje na slijedeći
uvjet
If Date > rok Then
'drugi IF uvjet - ako je današnji datum veći od limitiranog tj. ako je
istekao tada uradi po naredbi ispod
MsgBox "ISTEKAO JE DATUM ZA KORIŠTENJE! kontaktirajte
autora" 'u slučaju da je
datum istekao pojavljuje se poruka
Sheets("Sheet1").Activate
'pozicionira se na Sheet1 ako je datum istekao tj. ako nije
ispunjen prethodni uvjet
'ActiveWorkbook.Close 'alternativa
prethodnog događaja Sheets - zatvara workbook ako je datum istekao
tj. ako nije ispunjen prethodni uvjet
End If
'zatvaranje drugog IF-a
End If
'zatvaranje prvog IF-a
End Sub 'zatvaranje
procedure tj. kraj procedure
Uočite limit date tj. specifičan datum do kojega sam odredio
dozvoljen pristup na Sheet2. U slučaju da sam postavio datum
(2010, 5, 30) tada ne bi imao pristup na Sheet2 (ovaj slučaj
ću opisati u nastavku tutorijala). S obzirom da rok nije
istekao nakon upisa lozinke za pristup možemo raditi izmjene
na Sheetu2 u tablici podataka ili već svašta-nešto. Također
uočite liniju naredbe koja zatvara cijelu Workbook ako
želimo u slučaju da je limit date istekao. dovoljno je
ukloniti prvi apostrof ispred te linije a po želji postaviti
apostrof u naredbenu liniju iznad (što nije nužno jer će se
svakako workbook zatvoriti)
Slijedeća moguća situacija je pristup Sheetu4 na kojem nema
ograničenja pa možemo pristupati bez problema. No imamo
situaciju kada pristupamo na Sheet3 gdje opet imamo
ograničenje ali sa drugom pristupnom lozinkom. Netko će sada
pitati se "zašto različite pristupne lozinke?". Pa uzmimo za
primjer da na jednoj Workbook koja je sherana (a i ne mora
biti) radi više korisnika. Svatko pristupa nekom određenom
Sheetu i vrši upis svojih podataka u tablice i slično. Zato
je za svakog korisnika postavljena njegova lozinka (šifra
ili password) koju drugi korisnici ne znaju.
Dakle pristup na Sheet3 je opet preko pristupne
lozinke uz ograničenje
Pristup na Sheet3 via VBA
Private Sub Worksheet_Activate()
'naredba za aktiviranje
dogadjaja prilikom pristupa na Sheet3
Dim pswrd As String
'postavljanje varijable pswrd
Dim rok As Date 'postavljanje
varijable rok
pswrd = "qa"
'lozinka jednaka je upisanome - postavljanje lozinke ili passw
rok = DateSerial(2021, 5, 30)
'varijabla rok jednaka je
postavljenom datumu tj. limitirani datum za
otvaranje Workbook u ovom slučaju je to 30.5.2012
'datum pisanja ovog tutorijala je 7.4.2013
'ako ostavimo ovaj datum tada ćemo moći pristupiti u Worksheet
'ali ako postavimo datum (2011, 5, 30) tada nećemo moći
pristupiti u Workbook jer je istekao rok
'--------------------------------------------
pswrd = InputBox("UPIŠITE VAŠU LOZINKU ZA PRISTUP U
WORKSHEET 3") 'dijalog prozor za upis lozinke ili password
If pswrd = "qa" Then
'prvi IF uvjet - ako je
pasword ispravno upisan tada nastavi dalje na slijedeći
uvjet
If Date > rok Then
'drugi IF uvjet - ako je današnji datum veći od limitiranog tj. ako je
istekao tada uradi po naredbi ispod
MsgBox "ISTEKAO JE DATUM ZA KORIŠTENJE! kontaktirajte
autora" 'u slučaju da je
datum istekao pojavljuje se poruka
Sheets("Sheet1").Activate
'pozicionira se na Sheet1 ako je datum istekao tj. ako nije
ispunjen prethodni uvjet
'ActiveWorkbook.Close 'alternativa
prethodnog događaja Sheets - zatvara workbook ako je datum istekao
tj. ako nije ispunjen prethodni uvjet
End If
'zatvaranje drugog IF-a
End If
'zatvaranje prvog IF-a
End Sub 'zatvaranje
procedure tj. kraj procedure
Uočite limit date tj. specifičan datum do kojega sam odredio
dozvoljen pristup na Sheet3. U ovom slučaju postavio sam
limit date (2021, 5, 30) ili 30.5.2021 što je razlika u
odnosu na Sheet2. Dakle na ovaj način možemo ograničiti
pristup samo određenim Sheets dok pristup na ostale Sheets
može biti dostupan.
U slučaju da sam postavio datum
(2012, 5, 30) tada ne bi imao pristup na Sheet3. S obzirom
na limit date i da rok nije
istekao pa nakon upisa lozinke za pristup možemo raditi izmjene
na Sheet3. Također uočite liniju naredbe koja zatvara cijelu
Workbook ako želimo u slučaju da je limit date istekao.
Pristup na Sheet odbijen istekao ograničeni datum - Access
to the Worksheet denied limit date has expired
U slijedećem opisu i slikama ispod pogledajte kako izgleda
situacija kada je istekao limit date za pristup na Sheet2.
Dakle prilikom klika na naziv Sheet2 pojavljuje nam se
dijalog prozor za upis lozinke za pristup na Sheet2.
Bez obzira na upis ispravne lozinke (passworda ili šifre)
Excel nam odbija dati pristup na Sheet2 (i automatski nas
pozicionira na Sheet1 ili zatvara cijelu Workbook) jer je
istekao datum za pristupanje na Sheet2. Uočite da sam u
samom Sheetu2 napisao u ćelijama A1:B2 lozinku i limit date,
tako da se u ovom tutorijalu lakše snalazite (inače tu su
tablice i naši podaci). Dakle gdje je ograničenje, pa
postavio sam limit date u makronaredbi koju sam umetnuo na
Sheet2 modul u VBE.
I što na kraju reći osim kratki rezime.
Mogućnosti:
- Ograničiti pristup sa limitiranim datumom na cijelu
Workbook (radnu knjigu)
- Ograničiti pristup sa limitiranim datumom na sve Sheets
(radne listove)
- Ograničiti pristup sa limitiranim datumom na određene
Sheets (radne listove)
Kao i to da su ovo samo jednostavniji primjeri makronaredbi
jer ja kao niže-prosječni korisnik Excela nisam htio ulaziti
u kompleksnije makronaredbe, to prepuštam vama i vašim
potrebama.
Naravno ako vam ovaj tutorijal nije dovoljan vi prema svojim
potrebama
proguglajte i
potražite makronaredbe prema svojim potrebama ili
angažirajte stručnjaka koji zna programirati u
VBA za Excel ili
sami proučite kako se radi sa makronaredbama u Excelu. I na
kraju skinite si ove dvije datoteke pomoću kojih sam izradio
ovaj tutorijal. Datoteke su formata
*.xlsm za Excel 2007. (pazite
kome vjerujete kada pokrećete datoteke koje sadrže
makronaredbe bilo za Excel ili Word)
U nastavku pogledajte neke makronaredbe povezane sa
tematikom tutorijala.
Sprječavanje pristupa nekom Sheet-u u otvorenoj Workbook
nakon određenog datuma koji se postavlja u neku ćeliju
Ovaj Macro kopirajte
u ThisWorkbook vašeg VBE.
Uočite dio naredbe za postavljanje datuma ili vremenskog
perioda do kojeg se može koristiti pristup nekom Sheetu.
Dakle datum koji je granica upotrebe Sheeta postavite u A1
ili na neki drugi Sheet u npr: AZ800 i još ga postavite kao
bijeli font tako da bude nevidljiv korisnicima datoteke.
Sub workbook_open()
Dim pswrd As String
Dim ExpDate As Date
ExpDate = Range("A1").Value
If Date < ExpDate Then
Exit Sub
Else
pswrd = inputbox("Whats the password")
If pswrd = "12345" Then
'Opcija 1 - postavljanje 10
godina u budućnosti
Range("A1").Value = Range("A1").Value + 3650
'3650 je broj dana od datuma
postavljenog u ćeliju A1
'Opcija 2 - postavljanje datuma
'Range("A1").Value = inputbox("Enter the new
Expiration Date" _
'& " In the format MM/DD/YY", "Worksheet Expired " & ExpDate)
Exit Sub
Else
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
End If
End Sub
Postavljanje lozinke (passworda)
samo za prvo otvaranje Workbook
Ako kreirate jednu datoteku (Workbook)
i želite je nekome poslati na daljnje korištenje a uz to
želite spriječiti da je netko otvori prije osobe kojoj ste
poslali, možete priskočiti jednom malom triku.
Prilikom otvaranja makronaredba
će tražiti password za otvaranje. Nakon otvaranja i pristupa
workbook i worksheets osoba koja zna pristupnu lozinku može
mijenjati podatke i nakon prvog snimanja Workbook više neće
morati upisivati lozinku. Vodite računa da u VBE imate samo
jedan module sa nazivom Module1 jer će dotični module biti
obrisan nakon prvog snimanja.
Ovaj Macro kopirajte
u Module1 vašeg VBE ili kopirajte ovu makronaredbu u
Code Window
Sub Auto_open()
Dim pswrd As String
' u ovom dijelu naredbe
eksperimentirajte sa If Date > ili If Date < ili If Date =
If Date < DateSerial(2015, 7, 15) Then
'datum u obliku YYYY, MM, DD - doticni
limitirani datum je 15.7.2015
Exit Sub
Else
pswrd = InputBox("Upisite lozinku za pristup")
'tekst pitanje
If pswrd = "12345" Then
'pristupna lozinka koje vise nece trebati nakon prvog
otvaranja
DeleteModule 'poziva proceduru
Sub DeleteModule
Exit Sub
Else
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
End If
End Sub |
|
Ovaj Macro kopirajte
u Module2 vašeg VBE.
Sub
DeleteModule() 'uklanja tj.
brise Module1 nakon prvog snimanja Workbook
Dim VBComp As Object
Set VBComp = ThisWorkbook.VBProject.VBComponents("Module1")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub
Sprječavanje pristupa u Workbook
nakon određenog datuma - postavljen password za
pristup
Još jedna interesantna i jednostavna makronaredba koja se
samim otvaranjem Workbook aktivira.
Dakle,
ovaj Macro kopirajte
u VBE u ThisWorkbook
Sub Workbook_Open()
Dim pswrd As String, rok As Date
rok = DateSerial(2015, 5, 30)
'datum postavljen kao
30.5.2015
pswrd = InputBox("Upišite lozinku")
If pswrd = "123" Then
'u ovom dijelu
naredbe eksperimentirajte sa If Date > ili If Date <
If Date > rok Then
MsgBox "ISTEKAO JE DATUM ZA UPOTREBU! kontaktirajte
autora"
ActiveWorkbook.Close
End If
Else
ThisWorkbook.Close
End If
End Sub
PAŽNJA! Autor ovog tutorijala
se ograđuje od eventualno nastalih problema korištenjem
navedenih makronaredbi. Pazite što radite i kome
vjerujete ;-)
SVE OVE MAKRONAREDBE ISPROBAJTE PRVO NA PRIMJERIMA ILI
KOPIJAMA VAŠE DATOTEKE!
Ako koristite Excel 2007 -
obratite pažnju ako vam ne radi događaj Workbook_Open() ili
Auto_Open() tada nemojte snimati datoteku u *.xls
formatu već isprobajte u *.xlsm formatu
Također pogledajte tutorijale:
Kako
riješiti probleme sa
Workbook_Open događajem kada se ne pokreće
(aktivira)
Neki
načini zaštite u Excelu
pomoću VBA
Kako
kopirati u VBE makronaredbu
sa internet stranice
Kako
zabraniti pokretanje
makronaredbi
Kako zaključati određene
ćelije u Excelu |
|
|