Protezione e verifica degli accessi ad una cartella

Il problema

Come ho promesso la volta scorsa torniamo al nostro VBA presenterò una serie di protezioni da applicare ad una cartella di Excel.

Ma non fatevi illusioni: un utente un pò esperto troverà più di un modo per eludere queste protezioni. Uno dei sistemi più banali è quello di disabilitare le macro quando Excel ci mostra la finestra " Avviso di Protezione". Il vero scopo di queste protezioni è quello di difendere noi stessi dalle nostre distrazioni.

 

Per giustificare l'intervento che sto per proporre poniamo questo problema.
Siamo in un'azienda. Nella nostra azienda c'è la gestione di una qualche attività (Magazzino, Archivio, Clienti, Ospiti o quant'altro) che viene espletata da diversi nostri collaboratori. Il lavoro, ovviamente, viene svolto in una cartella di Excel.
Il responsabile della gestione in questione vuole che a lavorare sulla cartella siano solo alcuni collaboratori e vuol sapere da chi e quando viene aperta la cartella. Ma vuole anche che quando sarà lui ad aprire la cartella questa non presenti alcuna delle protezioni progettate.

Per raggiungere lo scopo vuole organizzare l'attività in modo che:  

  1. Ad ogni apertura della cartella di lavoro venga chiesto, dopo la classica richiesta della o delle Password di apertura della Cartella che normalmente si ottiene con Strumenti / Opzioni ---> Protezione e che è uguale per tutti (vedi le prime 2 immagini qui in basso), il nome o codice dell'utente che sta iniziando il lavoro (è la terza immagine).

  2. Se il nome o il codice dell'utente è sbagliato la cartella si richiude dopo 3 tentativi.

  3. Se il nome è quello di uno dei collaboratori vengono attivate alcune protezioni

  4. Se il nome è quello del responsabile vengono tolte tutte le protezioni

  5. In un file di testo esterno viene registrato il nome dell'utente che ha aperto la cartella, la data e l'ora:
    Mike  07/04/2006   23.15.31
    Pippo 07/04/2006   23.16.04
    Mike  09/04/2006   20.27.11
    Pippo 10/04/2006   13.57.55

  6. Alla chiusura della cartella viene eseguito il salvataggio automatico della cartella stessa

 

All'apertura della cartella queste sono le finestre iniziali che sono quelle proprie di Excel

   

 

ottenute inserendo le Password con Strumenti / Opzioni ---> scheda Protezione oppure, da codice:

ActiveWorkbook.Password = "mic"
ActiveWorkbook.WritePassword = "mac"

 

e questa è la nostra richiesta di Password personalizzata ottenuta con la nostra UserForm

 

Nella tabella che segue sono riportate le protezioni impostate alla cartella di Excel e viene mostrato:

il codice, l'eventuale comando eseguibile dai veri menù, e l'effetto.

Codice

Comando da menù

Effetto

ActiveWorkbook.Password = "mic"

ActiveWorkbook.WritePassword = "mac"

Strumenti /

Opzioni --->

scheda Protezione

La prima istruzione permette l'apertura della cartella di Excel. Se non viene fornita la cartella si richiude

La seconda consente la modifica del contenuto della cartella. Se non viene fornita la cartella viene aperta ma le eventuali modifiche non vengono salvate

With Application.CommandBars("Worksheet menu bar")

.Controls("strumenti").Controls("Opzioni...").Enabled = False

.Controls("strumenti").Controls("Protezione").Enabled = False

.Controls("strumenti").Controls("Macro").Enabled = False

.Controls("Formato").Controls("Celle...").Enabled = False

End With

 

Vengono disabilitate le voci di menù:
 

Strumenti / Opzioni
 

Strumenti / Protezione
 

Strumenti / Macro
 

Formato Celle

(Questi controlli debbono essere riattivati all'uscita dalla cartella)

ActiveWindow.DisplayWorkbookTabs = False

Strumenti / Opzioni ---> Visualizza ... metti o togli la spunta in Schede

Nasconde le etichette dei fogli

Application.OnKey "%{F11}", ""

ALT + F11

Disabilita la combinazione dei tasti ALT + F11 per andare a curiosare nel codice VBA

With Worksheets("Foglio1")
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, Password:="Mike"
.EnableSelection = xlUnlockedCells
End With
 

Strumenti / Protezione / Proteggi Foglio e quindi:

Spunta su

     Seleziona celle sbloccate
Spunta su

     Formato celle
Togliere spunta a

     Seleziona celle bloccate

Protegge il foglio con PassWord

Permette la selezione delle celle non bloccate

Permette di formattare le celle non protette

Non permette la selezione delle celle protette

Range("A1").CurrentRegion.NumberFormat = ";;;"

Formato / Celle ---> Numero ---> Personalizzato

Una cella così formattata nasconde ad occhi indiscreti il contenuto delle celle sottoposte a questo trattamento

 

Soluzione

 

Lavori preliminari:

Nel foglio2, a partire dalla cella A1, il responsabile creerà l'elenco dei collaboratori che avranno accesso alla cartella ed il suo nome dovrà essere il primo (se così non fosse deve tenerne conto nelle istruzioni che andrà a scrivere in VBA: "If Dove = 1 Then ..."

Un possibile elenco potrebbe essere il seguente:

Mike

Pippo

Pluto

Paperino

Paperone

 

Compiuta questa prima operazione possiamo passare al codice.

Qui facciamo uso di:

Qualcuno potrebbe obiettare che basterebbe uno dei due eventi Workbook_Open e BeforeClose. Questo è parzialmente giusto in quanto all'apertura della cartella ci verremmo comunque a trovare col lavoro protetto. Tuttavia ci sarebbero molti modi di bloccare l'esecuzione delle macro o all'apertura della cartella o alla sua chiusura.

Perciò ho optato di usare entrambi gli eventi.

 


Punto primo: l'evento Workbook_Open del modulo ThisWorkbook per abilitare le varie protezioni sul foglio prima dell'apertura dalla cartella.

 

Private Sub Workbook_Open()
'si inizia col disabilitare il tasto Alt + F11
Application.OnKey "%{F11}", ""
'si continua col nascondere le etichette dei fogli
ActiveWindow.DisplayWorkbookTabs = False
'la serie di istruzioni racchiuse nel blocco With ... End With
'l'abbiamo vista nell'articolo "Barra di comando: disabilitare una voce o un pulsante"
'e serve per disabilitare, nell'ordine:
'Strumenti / Opzioni tramite la quale si può accedere alle schede
'    Visualizza per mostrare le etichette dei fogli
'    Protezione per escludere le due Password iniziali
'Strumenti / Protezione tramite la quale si può togliere la protezione al foglio
'Strumenti / Macro tramite la quale si può andare a sbirciare nel codice

'Formato / Celle tramite la quale si può accedere alle schede
'    Protezione per sbloccare le celle bloccate
'    Numero per togliere la formattazione personalizzata ";;;" (3 punto e virgola)
'        che nasconde il contenuto delle celle
With Application.CommandBars("Worksheet menu bar")
    .Controls("strumenti").Controls("Opzioni...").Enabled = False
    .Controls("strumenti").Controls("Protezione").Enabled = False
    .Controls("strumenti").Controls("Macro").Enabled = False
    .Controls("Formato").Controls("Celle...").Enabled = False
End With
'per le istruzioni racchiuse tra i due seguenti blocchi With ... End With vi invito
'a leggere la nota che vedete alla fine di questo listato

'qui anticipiamo solo che in questi 2 blocchi viene effettuato il formato cella personalizzato

'già visto nel precedente articolo
With Worksheets("Foglio2")
    .Unprotect "Mike"
    .Cells.Locked = False
    .Cells.FormulaHidden = False
    .Range("A1").CurrentRegion.NumberFormat = ";;;"
    .Range("A1").CurrentRegion.Locked = True
    .Range("A1").CurrentRegion.FormulaHidden = True
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, Password:="Mike"
    .EnableSelection = xlUnlockedCells
End With
With Worksheets("Foglio1")
    .Unprotect "Mike"
    .Cells.Locked = False
    .Cells.FormulaHidden = False
    .Range("A1").NumberFormat = ";;;"
    .Range("A1").Locked = True
    .Range("A1").FormulaHidden = True
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, Password:="Mike"
    .EnableSelection = xlUnlockedCells
End With
'con questo si concludono le azioni da svolgere all'apertura della cartella
Worksheets("Foglio1").Select
'e viene mostrata la UserForm per la richiesta del nome dell'utente
UserForm1.Show
End Sub

 

Negli ultimi due blocchi With ... End With viene applicato alle celle interessate il formato celle personalizzato per renderne invisibile il contenuto. Le istruzioni necessarie a questo scopo sarebbero le sole:

.Range("A1").NumberFormat = ";;;" per il foglio1 e

.Range("A1").CurrentRegion.NumberFormat = ";;;" per il foglio2

 

Tuttavia ci sono da fare alcune considerazioni sull'uso delle istruzioni inserite prima e dopo queste due.

 


Punto secondo: uso dell'evento BeforeClose del modulo ThisWorkbook per ristabilire le varie protezioni sul foglio prima dell'uscita dalla cartella.

 

Attenzione: è necessario che all'uscita dall'applicazione siano riabilitate tutte le voci di menù precedentemente disabilitate per non trovarsi con un Excel mutilato.

 

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'riabilito la combinazione di tasti Alt + F11
Application.OnKey "%{F11}"
'riabilito tutte le voci di menù precedentemente disabilitate
ActiveWindow.DisplayWorkbookTabs = False
With Application.CommandBars("Worksheet menu bar")
    .Controls("strumenti").Controls("Opzioni...").Enabled = True
    .Controls("strumenti").Controls("Protezione").Enabled = True
    .Controls("strumenti").Controls("Macro").Enabled = True
    .Controls("Formato").Controls("Celle...").Enabled = True
End With
'continuo con le stesse protezioni appena viste
With Worksheets("Foglio2")
    .Unprotect "Mike"
    .Cells.Locked = False
    .Cells.FormulaHidden = False
    .Range("A1").CurrentRegion.NumberFormat = ";;;"
    .Range("A1").CurrentRegion.Locked = True
    .Range("A1").CurrentRegion.FormulaHidden = True
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, Password:="Mike"
    .EnableSelection = xlUnlockedCells
End With
With Worksheets("Foglio1")
    .Unprotect "Mike"
    .Cells.Locked = False
    .Cells.FormulaHidden = False
    .Range("A1").NumberFormat = ";;;"
    .Range("A1").Locked = True
    .Range("A1").FormulaHidden = True
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, Password:="Mike"
    .EnableSelection = xlUnlockedCells
End With
'applico le due Password necessarie per aprire la cartella
ActiveWorkbook.Password = "mic"
ActiveWorkbook.WritePassword = "mac"
ActiveWorkbook.Save
Application.Quit
End Sub

 

 


Punto terzo: la richiesta della Password tramite una UserForm

La UserForm da usare è molto semplice come si nota dalla seguente figura

 

 

In fase progettazione è tuttavia necessario compiere due operazioni preliminari, come viene mostrato dall'immagine qui sotto

 

 

Compiute queste operazioni preliminari possiamo passare alla stesura del codice da scrivere nel modulo relativo alla UserForm. Di questa UserForm useremo il solo evento Click del CommandButton1.

 

 

Attenzione:

Nota 1

Siccome concediamo all'utente tre possibilità per digitare correttamente il proprio codice di accesso abbiamo bisogno di un flag che conti i vari tentativi. Il compito di questo flag è affidato ad una variabile di tipo variant (potrebbe anche essere di tipo integer, ma questo è un altro discorso). Perchè questo flag compia correttamente il suo compito è necessario che la variabile a cui si appoggia sia dichiarata nella zona generale del modulo, ossia nelle primissime righe del modulo, prima di ogni altra routine o macro (variabile pubblica a livello di modulo).

 

Nota 2

Per non appesantire molto la routine usata per il pulsante OK della UserForm ho provveduto a scrivere in un modulo standard la routine Sprotezione che viene usata se chi apre la cartella è il responsabile dei lavori. Chi apre la cartella viene individuato come responsabile perchè il suo nome figura nella prima cella (la A1) del foglio2 della cartella.

 

Fatta questa precisazione liquidiamo subito il fatto col fare la nostra bella dichiarazione nella zona generale del modulo abbinato alla UserForm1:

Dim Flag As Integer

 

In un modulo standard viene scritta questa routine che provvede a togliere tutte le protezioni e tutte le restrizioni applicate nel "Private Sub Workbook_Open()" perciò sono ad esse contrapposte:

Verrà richiamata dal pulsante OK nel caso venga riconosciuto il nome del responsabile per lasciare l'applicazione senza alcuna protezione o restrizione.

 

Sub Sprotezione()
Application.OnKey "%{F11}"
ActiveWindow.DisplayWorkbookTabs = True
With Application.CommandBars("Worksheet menu bar")
    .Controls("strumenti").Controls("Opzioni...").Enabled = True
    .Controls("strumenti").Controls("Protezione").Enabled = True
    .Controls("strumenti").Controls("Macro").Enabled = True
    .Controls("Formato").Controls("Celle...").Enabled = True
End With
With Worksheets("Foglio2")
    .Unprotect "Mike"
    .Cells.Locked = False
    .Cells.FormulaHidden = False
    .Range("A1").CurrentRegion.NumberFormat = "General"
End With
With Worksheets("Foglio1")
    .Unprotect "Mike"
    .Cells.Locked = False
    .Cells.FormulaHidden = False
    .Range("A1").NumberFormat = "General"
End With
End Sub

 

Fatto questo passiamo ad esaminare quel che succede quando premiano il pulsante OK della UserForm.

  1. Si inizia col controllare se il nome che è stato digitato o non digitato nella casella di testo è presente nella lista degli utenti autorizzati del foglio2 passando in rassegna tutto l'intervallo a partire dalla cella A1. Se viene trovata corrispondenza tra i 2 nomi viene posta a True la variabile Booleana Trovato.
    Trovato = False
    With Sheets("Foglio2")
        Riga = .Range("A1").End(xlDown).Row
        For A = 1 To Riga
            If .Cells(A, 1) = TextBox1.Text Then
                Trovato = True
                Dove = A
                Exit For
            End If
        Next
    End With
  2. Se il valore di Trovato rimane uguale a False si incrementa il valore del flag e se ne controlla il valore. Se ha raggiunto un valore uguale o superiore a 3 viene chiusa la cartella di lavoro, altrimenti ci si prepara per un altro inserimento
    If Not Trovato Then
        Flag = Flag + 1
        If Flag >= 3 Then
            ThisWorkbook.Close Savechanges:=False
            Exit Sub
        Else
            TextBox1.Text = ""
            TextBox1.SetFocus
            Exit Sub
        End If
    End If
  3. Superato questo primo controllo si va a scrivere in una cella del foglio1 il nome immesso nella casella di testo. Ma dobbiamo tener presente che la cella che dovremo usare è protetta e nemmeno da codice è permesso scrivere in questa locazione. Perciò dovremo
    • prima sproteggere il foglio
    • poi scrivere nella cella
    • quindi proteggere nuovamente il foglio

    With Sheets("Foglio1")
        .Unprotect "Mike"
        .Range("A1") = TextBox1.Text
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, Password:="Mike"
        .EnableSelection = xlUnlockedCells
    End With
    Unload Me

  4. Infine, dopo aver chiuso la UserForm, possiamo dedicarci all'aggiornamento del file esterno dove scriviamo tre dati:
    il nome di chi ha aperto la cartella e che precedentemente avevamo scritto nella cella A1
    la data odierna
    l'ora attuale
    Perciò:
    • assumiamo il percorso del file sul nostro disco rigido (io ho scelto la stessa cartella del file di Excel)
                  DestFile = ThisWorkbook.Path & "\crono.mik"
    • individuiamo il canale di comunicazione col file di testo
                  FileNum = FreeFile()
    • apriamo il file usando il metodo Append
      (vedi verso la metà dell'articolo "File di testo come DataBase" al paragrafo "Aggiornamento del file di testo")
                  Open DestFile For Append As #FileNum
    • a questo punto possiamo scrivere i nostri dati nel file di testo e chiuderlo
            Print #FileNum, Sheets("Foglio1").Range("A1").Value, Date, Time
            Close #FileNum
  5. Al punto 1) abbiamo impostato la variabile Dove pari alla riga dove è stato trovato il nome cercato. Se la variabile Dove ha il valore 1 (uno) vuol dire che chi ha aperto la cartella di lavoro è il responsabile, per cui è opportuno che almeno lui non abbia alcuna limitazione ed andremo perciò a togliere tutte le protezioni:
                        If Dove = 1 Then
                        Sprotezione   
    'con questa andiamo a chiamare la Sub Sprotezione vista qui sopra.
                        End If

Con questo abbiamo finito. Quello che segue è il codice completo abbrivato al pulsante OK della UserForm. Il codice, per una migliore leggibilità, è dotato di pochi commenti.

 

Private Sub CommandButton1_Click()
Dim Dove, Riga, A, Risposta
Dim Trovato As Boolean
Dim DestFile, FileNum
'durante il confronto se si riscontra una corrispondenza tra il nome inserito o non inserito
'nella casella di testo ed i nomi presenti nel Foglio2 viene posta a True
'la variabile Booleana Trovato e viene memorizzata nella variabile Dove
'il numero di riga dove è stato trovato il nome cercato
'On Error GoTo fine1
Trovato = False
With Sheets("Foglio2")
    Riga = .Range("A1").End(xlDown).Row
    For A = 1 To Riga
        If .Cells(A, 1) = TextBox1.Text Then
            Trovato = True
            Dove = A
            Exit For
        End If
    Next
End With
'se il nome digitato non è presente nella lista la cartella viene chiusa
If Not Trovato Then
    Flag = Flag + 1
    If Flag >= 3 Then
        ThisWorkbook.Close Savechanges:=False
        Exit Sub
    Else
        TextBox1.Text = ""
        TextBox1.SetFocus
        Exit Sub
    End If
End If
With Sheets("Foglio1")
    .Unprotect "Mike"
    .Range("A1") = TextBox1.Text
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, Password:="Mike"
    .EnableSelection = xlUnlockedCells
End With
Unload Me
'nel file di testo "Crono.mik" viene registrato il nome dell'utente, la data e l'ora
DestFile = ThisWorkbook.Path & "\crono.mik"
FileNum = FreeFile()
Open DestFile For Append As #FileNum
Print #FileNum, Sheets("Foglio1").Range("A1").Value, Date, Time
Close #FileNum
'nella cella A1 del Foglio2 (quindi riga 1) c'è il nome o codice del responsabile
'perciò, se il nome digitato nella casella di testo è lo stesso che si trova
'nella cella A1, chi sta aprendo la cartella è il responsabile ed in quanto tale
'ha il potere di aggiungere nuovi utenti
If Dove = 1 Then
    Sprotezione
End If
End Sub

 

 

 

Conclusioni

Con questo chiudo questa parentesi sulle protezioni. Torno a ripetere fino alla noia. Non aspettatevi che con questo il vostro lavoro sia inespugnabile. Quel che ho presentato è solo un modesto aiuto anche e soprattutto contro le nostre distrazioni e potrebbe servire semplicemente per rendere la vita un po' più complicata a chi vuol romperci le uova nel paniere.

 

Perciò senza indugio vi auguro buon lavoro