PartagerFichier

Retour vers Excel

Partager un fichier

NB : Cette application a été développée sous Excel 2007, Excel s'est amélioré avec les années et actuellement cette surveillance est active lorsque l'on partage un classeur
Pour savoir comment faire suivez ce lien vers la page Microsoft mais ceci reste un excellent exercice VBA

Problème : Partager une application avec plusieurs utilisateurs mais ne leur permettre que de voir certaines informations

• Le chef du personnel (CP) veut partager un fichier Excel avec les 4 sous-chefs de Bureau (SCBx). Ce fichier comporte 5 feuilles de travail, une par département et une dont vous découvrirez l'utilisation.
• Les permissions sont fixées comme ceci
  • CP peut voir et manipuler les 4 feuilles de travail
  • SCB1 voit et manipule uniquement la feuille 1
  • SCB2 voit et manipule uniquement la feuille 2
  • SCB3 voit et manipule uniquement la feuille 3
  • SCB4 voit et manipule uniquement la feuille 4

Première étape

• Créer un UserForm. Pour cela, se placer sur la mention VBAProject puis via le menu Insertion choisir UserForm
  
• Placer ensuite les différents contrôles sur le formulaire comme les boutons d'options(SCB1;SCB2;SCB3;SCB4;CP), un cadre qui regroupe les boutons d'options, un bouton de commande(OK), une zone de texte(MP) pour encoder un mot de passe personnalisé, deux étiquettes. Personnalisez toujours les noms des contrôles en modifiant la propriété Name et leur affichage par la propriété Caption
  

Seconde étape
Que doit effectuer la macro lorsque l'utilisateur presse le bouton OK...Faites un double clic sur le bouton. Dans la fenêtre de code, encodez les instructions suivantes

Private Sub OK_Click()
'Mémorisation du nom de la feuille active
Origine = ActiveSheet.Name
'Recherche de la présence de la feuille Controle
FeuilleControle = 0 'réinitialisation du compteur
For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Controle" Then
        FeuilleControle = 1
    End If
Next i
'Si le classeur ne contient pas de feuille Contrôle ...
If FeuilleControle = 0 Then
'...introduction (création) d'une feuille Controle
    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = "Controle"
End If

'suivant le choix de l'utilisateur, affiche la feuille appropriée
    MonFormulaire.Hide
    If SCB1 = True And MP = "MP1" Then
        Feuil1.Visible = xlSheetVisible
        Feuil2.Visible = xlSheetVeryHidden
        Feuil3.Visible = xlSheetVeryHidden
        Feuil4.Visible = xlSheetVeryHidden
        Feuil5.Visible = xlSheetVeryHidden
        Nom = "SCB1" 'Nom = variable que l'on récupère plus loin dans le code
    ElseIf SCB2 = True And MP = "MP2" Then
        Feuil2.Visible = xlSheetVisible
        Feuil1.Visible = xlSheetVeryHidden
        Feuil3.Visible = xlSheetVeryHidden
        Feuil4.Visible = xlSheetVeryHidden
        Feuil5.Visible = xlSheetVeryHidden
        Nom = "SCB2" 'Nom = variable que l'on récupère plus loin dans le code
    ElseIf SCB3 = True And MP = "MP3" Then
        Feuil3.Visible = xlSheetVisible
        Feuil1.Visible = xlSheetVeryHidden
        Feuil2.Visible = xlSheetVeryHidden
        Feuil4.Visible = xlSheetVeryHidden
        Feuil5.Visible = xlSheetVeryHidden
        Nom = "SCB3" 'Nom = variable que l'on récupère plus loin dans le code
    ElseIf SCB4 = True And MP = "MP4" Then
        Feuil4.Visible = xlSheetVisible
        Feuil1.Visible = xlSheetVeryHidden
        Feuil2.Visible = xlSheetVeryHidden
        Feuil3.Visible = xlSheetVeryHidden
        Feuil5.Visible = xlSheetVeryHidden
        Nom = "SCB4" 'Nom = variable que l'on récupère plus loin dans le code
    ElseIf CP = True And MP = "MP5" Then
        Feuil1.Visible = xlSheetVisible
        Feuil2.Visible = xlSheetVisible
        Feuil3.Visible = xlSheetVisible
        Feuil4.Visible = xlSheetVisible
        Worksheets("Controle").Visible = xlSheetVisible
        Feuil5.Visible = xlSheetVeryHidden
        Nom = "CP" 'Nom = variable que l'on récupère plus loin dans le code
    Else
        Call PasDAutorisation
    End If

'Insertion du nom et de la date dans la première ligne libre de la feuille Controle
Worksheets("Controle").Activate 'Active la feuille appelée Controle
ActiveCell.SpecialCells(xlLastCell).Select 'Sélectionne la dernière cellule remplie
Range("A" & ActiveCell.Row + 1).Select 'Descend d'une ligne
ActiveCell.Value = Nom 'Entre la valeur de la variable Nom
ActiveCell.Interior.ColorIndex = 36 'Met de la couleur à l'intérieur de la cellule
ActiveCell.Font.ColorIndex = 3 'Utilise une couleur pour la police de caractère
ActiveCell.Offset(0, 1).Value = Now() 'Entre la date dans la colonne d'à côté
ActiveCell.Offset(0, 1).Interior.ColorIndex = 36 'Met de la couleur à l'intérieur
ActiveCell.Offset(0, 1).Font.ColorIndex = 3 'Utilise une couleur pour la police de caractère
ActiveCell.Offset(1, 0).Activate 'Descend d'une ligne
'La variable CelluleControle est créée
ActiveWorkbook.Names.Add "CelluleControle", ActiveCell.Address
'La feuille d'origine est activée
'Worksheets(Origine).Activate
Feuil1.Activate 'La feuille 1 est activée

End Sub

J'ai dissocié ici une procédure pour fermer le fichier sans sauvegarde au cas un intrus essaierait d'ouvrir le fichier sans authentification ou pour pouvoir la réutiliser plus tard dans d'autres gestions des erreurs mais vous pourriez l'embarquer dans le Else de la procédure du dessus

Private Sub PasDAutorisation()
    Reponse = MsgBox("Vous n'êtes pas autorisé à utiliser ce fichier", vbOKOnly, "Avertissement")
    Application.ActiveWorkbook.Close False
End Sub

Enfin, Il nous reste à empêcher l'utilisation de la croix X dans le coin supérieur droit du formulaire, car malheureusement elle sera toujours affichée, mais l'on peut utiliser l'événement qui se produit lorsque l'on clique dessus...Faites un double-clic sur votre formulaire et une fenêtre de code s'ouvre, encodez donc cette formule, son action me semble facile à comprendre

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'Pour empêcher l'utilisateur de fermer la fenêtre en appuyant sur la croix
    If CloseMode = vbFormControlMenu Then
        MsgBox "Vous ne pouvez pas fermer le formulaire comme cela"
        Cancel = True
    End If
End Sub

Pour que ce formulaire soit actif au chargement du fichier, nous avons besoin d'une procédure spéciale qui s'intitulera Sub Workbook_open()

'Routine qui s'excécute à l'ouverture du fichier
Sub Workbook_open()
   'Ouverture du formulaire d'entrée
Load MonFormulaire
MonFormulaire.Show
End Sub

Troisième étape : Les différentes protections
Malgré la procédure précédente, l'utilisateur peut échapper ce formulaire d’accueil, en effet lorsque le fichier s'ouvre l'on reçoit l'invite "Activer ou ne pas activer les macros"; protection mise en place pour se protéger des macros virus, louable en soi mais si l'utilisateur choisit de ne pas activer les macros, il charge, accède au fichier et contourne les aspirations du créateur (à savoir limiter l'accès à certaines données)

Première mesure à prendre, jouer sur l'événement "fermeture du fichier", en effet Excel se souvient du dernier affichage actif, nous allons donc créer une feuille vierge qu'une macro activera juste avant que l'on ferme le fichier; ce sera elle qui sera active au prochain chargement…Bien sûr , à cette même occasion nous masquons les autres feuilles

'Routine qui s'excécute à la fermeture du fichier
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Feuil5.Visible = xlSheetVisible
    Feuil1.Visible = xlSheetVeryHidden
    Feuil2.Visible = xlSheetVeryHidden
    Feuil3.Visible = xlSheetVeryHidden
    Feuil4.Visible = xlSheetVeryHidden
    Worksheets("Controle").Visible = xlSheetVeryHidden
End Sub

Remarquez le nom de la macro, voyez aussi que l'on affiche la Feuille 5 AVANT de masquer les autres, en effet Excel ne tolère pas que l'on cache toutes les feuilles. A partir de ce moment, si l'utilisateur désactive les macros, il ne verra que cette feuille vierge...

Avant d'activer la prochaine protection, sauvegardez votre travail mais en passant par les options de sauvegarde pour y définir un mot de passe

Enfin certains tenteraient d'accéder au code VB qui régit les macros; pour éviter cela, activez la protection et le partage de fichier,ceci permettra d'utiliser le fichier sur un réseau en mode multi-utilisateurs mais verrouillera également le code VBA

 

De cette manière l'utilisateur accédera au fichier par un mot de passe général que vous lui communiquerez, ne pourra pas échapper au formulaire d'acceuil, sera obligé de choisir un utilisateur précis, entrera un mot de passe personnel fixé par le créateur en utilisant une zone de texte et dont la propriété "passworchar" est "x".

Quatrième étape : Régler les options de partage
En effet, nous devons nous pencher sur les mises à jour du fichier de manière à ce que tous puissent disposer des informations les plus récentes. Pour cela, ouvrez le menu Outils et suivez les indications sur les vues d'écran comme ci-dessous. L'on regrettera de ne pas pouvoir descendre en dessous des 5 minutes pour une synchronisation parfaite du fichier mais cela est dû à Excel

Les différents mots de passe utilisés dans le fichier fourni en exemple :
Ouverture du fichier : "password" (Bizarrement, dans une nouvelle version d'Excel, il vous sera réclamé 3 fois)
Protection du fichier : "password1" (Pour vous permettre de voir le code VBA)
SCB1 : MP1 (Attention !! en majuscules)
SCB2 : MP2
SCB3 : MP3
SCB4 : MP4
CP : MP5

Télécharger le fichier exemple ExempleUserForm

FIN