Sommaire
Excel Perfectionnement
Excel Graphique
Divers Excel
Excel Vba début
Complément VBA
Utilitaires
Gestion
(philosophie)
Gestion
(techniques)
Liens
Utilitaires en Vrac
Gfc Windows

écrivrez-moi !














 

 

Classeurs

 

Contrôler l'activation des macros

Afin d'éviter la mauvaise utilisation d'un classeur qui contient des macros par la désactivation des macros au chargement du classeur (alerte macro), cette procédure fait un contrôle l'état du classeur pendant les phases de l'enregistrement et de l'ouverture.Si les macros ne sont pas activées, seule, la feuille Test avec le messsage que le classeur est inactif, apparaît.
Pour cela, nous utilisons les évènements Workbook_BeforeClose (avant la fermeture) et Workbook_Open (à l'ouverture) de ThisWorkBook.

A la fermeture :

Macro Commentaire
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim Feuille As Worksheet
For Each Feuille In ThisWorkbook.Worksheets
Feuille.Visible = xlSheetVeryHidden
Next
With Sheets("Test")
.Visible = True
.Activate
End With
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
ThisWorkbook.Save
ThisWorkbook.Close
End Sub


'Désactivation du rafraichissement de l'écran
'Passe à la ligne suivante si erreur détectée
'Déclaration de variable comme sheet
'Test pour toutes les feuilles
'Met les feuilles en "Très" cachées

'Avec la feuille Test
'Mise en visible
'Activée

'Avec la fenêtre (ou classeur), ôte : 'quadrillage, barres de défilement, onglets...

 

'Sauvegarde le Classeur
'Ferme le classeur

Note : Seule, la feuille Test sera visible à l'ouverture - la sauvegarde est automatique

A l'ouverture :

Macro Commentaire
Private Sub Workbook_Open()
On Error Resume Next
Dim Feuille As Worksheet
For Each Feuille In ThisWorkbook.Worksheets
Feuille.Visible = True
Next
Sheets("Début").Activate
Sheets("Test").Visible = xlSheetVeryHidden
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
End Sub

'Passe à la ligne suivante si erreur détectée
'Déclaration de variable comme sheet
'Test pour toutes les feuilles
'Rend visible les feuilles

'Active la feuille début
'Met la feuille Test en "Très" cachée
Avec la fenêtre (ou classeur), met : 'quadrillage, barres de défilement, onglets...

 

Note : Cache la feuille et remet le classeur en état. Il convient également de protéger le projet VBA par un mot de passe :
Dans L'éditeur VBA , clic droit sur le projet (petit fenêtre en haut à gauche) - Propriétés - Protection
télécharger le classeur SansMacro.xls

début                                         Sommaire

Enregistrement le nom d'un classeur à partir de la valeur d'une cellule ou d'une date

 

Problème : Une année est contenue dans une cellule du classeur actif et s'appelle "annee". Comment enregistrer ce classeur dans le répertoire de C\:Mes Documents sous le nom Rapport_AAAA; les AAAA correspondent au contenu de la cellule "annee"

Macro Commentaires
Sub EnregCellule()
On Error Resume Next
If Range("annee") = Empty Or Err <> 0 Then


MsgBox "Saisissez l'année"
Else
ActiveWorkbook.SaveAs Filename:= _
"C:\Mes Documents\Rapport_" & Range("annee") & ".xls"
End If
End Sub

'Si erreur, on continue
'Contrôle du remplissage de la cellule année ou si la cellule nommée "annee" existe bien
'Message d'alerte
'Sinon
'Sauvegarde sous le nom voulu
Note : Le contrôle de cohérence On Error Resume Next et Err <> 0  et la concaténation de chaine de caractère avec le contenu de la cellule "annee"

Variante : Nous voulons enregistrer le classeur sous la forme Rapport_062001 (c. a. d. MMAAAA) cette date étant définie par la date du jour.

Macro Commentaires
Sub EnregCellule2()
On Error Resume Next

ActiveWorkbook.SaveAs Filename:= _
"C:\Mes Documents\Rapport_" & _   Evaluate("=TEXT(TODAY(),""MMYYYY"")") & ".xls"
End Sub

'Si erreur, on continue pour éviter un bug si le fichier existe déjà.
'Sauvegarde sous le nom voulu
Note : =TEXT(TODAY(),""MMYYYY"") correspond sous Excel à
=TEXTE(AJOURDHUI();"MMAAAA") et la méthode Evaluate permet de récupérer le résultat.

début                                         Sommaire

 

Ouvrir un fichier via une boîte dialogue (inputbox)

 

Problème : Ouvrir un fichier dont le nom sera saisi dans une boîte de dialogue

Macro Commentaires
Sub OuvertureFichier()
Dim Fichier As String

On Error Resume Next
Fichier = InputBox("Indiquez le le fichier de transfert")

Workbooks.Open Filename:="C:\Mes Documents\" & _  Fichier & ".xls"
If Err <> 0 Then
MsgBox "fichier " & Fichier & " introuvable !"
End
End If
'Suite procédure....
End Sub

'déclaration variable en chaine de caractères
'Si erreur, on continue
'Récupération du nom saisi dans la boîte de dialogue dans la variable année
'Ouverture du fichier

'Si erreur d'exécution, alors
'Message d'alerte
'Fin procédure
Note : L'intérêt est d'utiliser une variable texte pour le chemin complet du fichier. Le End n'est utile que s'il y a une suite à la procédure. Le gestionnaire d'erreur permet le contrôle de la saisie

début                                         Sommaire

 

Durée et historique des ouvertures et fermetures d'un fichier

 

Problème : Dans une Feuil4 masquée, nous voulons suivre les heures d'ouvertures et de fermeture de notre classeur. En A1 : Jour( format jj/mm/aaaa sur la colonne), B1 : Ouverture (format hh:mm:ss), C1 : Fermeture (format hh:mm:ss) D1 : Durée (format [h]:mm:ss)
A l'ouverture : dans le module ThisWorkbook

Macro Commentaires
Private Sub Workbook_Open()

Sheets("Feuil4").Rows("2:2").Insert
Sheets("Feuil4").Cells(2, 1) = Now
Sheets("Feuil4").Cells(2, 2) = Time
End Sub
'Utilisation de l'évenement  open (ouverture du classeur)
'Insertion d'une ligne
' en A2, le jour
'en B2, l'heure d'ouverture

A la fermeture : dans le module ThisWorkbook

Macro Commentaires
Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not IsEmpty(Sheets("Feuil4").Cells(2, 1)) Then
Sheets("Feuil4").Cells(2, 3) = Time
Sheets("Feuil4").Cells(2, 4) = Sheets("Feuil4")._
Cells(2, 3).Value - Sheets("Feuil4").Cells(2, 2).Value
End If
End Sub
'Utilisation de l'évenement  BeforeClose (avant la fermeture du classeur)
'Test si A2 est non vide
' en C2, l'heure
'en D2, heure de fermeture-heure d'ouverture
Note : l'insertion de ligne 2 (Sheets("Feuil4").Rows("2:2").Insert). Le fait que la feuil4 soit masquée est indifférente à la procédure et n'ayant pas de sélection de feuille et classique application.screenupdating=false n'est pas nécessaire.
classeur exemple : duree.xls

début                                         Sommaire

Fermer un classeur sans sauvegarder les modifications ou sauvegarder sans message d'alerte

 

Problème : Fermer un classeur sans sauvegarder les modifications

Macro Commentaires
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

'Evenement avant la fermeture
'Le classeur est dit saugardé


'Evenement avant enregistrement

'désactivation de l'action d'enregistrer

 

Note : L'évement Fermeture est acceptée. Mais, au préalable,  le classeur est considéré comme sauvegardé (saved) - Il convient si on ne veut pas d'enregistrement intempestifs de désactiver l'action d'enregistrer. Ces 2 procédures se trouvent dans le module ThisWorkbook

Problème : Sauvegarder un classeur sans message d'alerte : "voulez-vous enregistrer..."

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
'Evenement avant la fermeture
'Enregistrement du classeur
Note : L'évement Fermeture est acceptée. Mais, au préalable, soit le classeur est enregistré (save) donc le message d'alerte n'apparaîtra plus.

début                                         Sommaire

 

Protéger - Déprotéger un classeur

 

Problème : Déprotéger un classeur

Macro Commentaires
Sub Deprotection()
If ActiveWorkbook.ProtectStructure = True Or  _  ActiveWorkbook.ProtectWindows = True Then _  ActiveWorkbook.Unprotect
End Sub

'Test si le classeur dispose du propriété de protection à Vrai, et déprotection

 

Note : Unprotect (méthode) ProtectStructure (propriété ayant un état : vrai - faux)

Problème : Faire la bascule protection - déprotection

Sub ProtectDeprotect()
On Error Resume Next
ActiveWorkbook.Protect
If Err <> 0 Then ActiveWorkbook.Unprotect
End Sub

'Gestionaire d'erreur enclenché
'Protection du classeur
'Si erreur, déprotection
Note : Si une méthode (ici Protect) déclenche, on fait l'autre

début                                         Sommaire

Mettre le chemin complet du classeur dans le pied de page de toutes les feuilles

 

Macro Commentaires
Sub EntetePiedPageClasseur()
Dim Text As String, Feuille As Worksheet
texte = Application.StatusBar
Application.StatusBar = "Macro en cours d'exécution...Patientez"
Application.ScreenUpdating = False
For Each Feuille In Worksheets
Feuille.PageSetup.CenterFooter = _
ThisWorkbook.FullName
Next
Application.StatusBar = texte
End Sub

'Déclaration de variables
'Récupération du text du le barre d'état
'Nouveau Message dans la barre d'état

'Désactivation du rafraichissement de l'écran
'Boucle sur chaque feuille du classeur
'Mise en place du pied de page central avec le nom et   ' chemin du classeur

'Remise en état de la barre d'état
Note : cette macro est assez lente, nous verrons plus tard comment l'accélerer
A retenir : Le nom et chemin du classeur : ThisWorkbook.FullName, le message dans la barre d'état : Application.StatusBar

début                                         Sommaire

 

Faire un retour à la ligne dans un pied de page

 

Macro Commentaires
Sub RetourLigne()
Dim Info As String
Info = ActiveWorkbook.FullName & _  Chr(13) & "imprimez le " & Date & Chr(13) & "à " & Time
ActiveSheet.PageSetup.LeftFooter = _   "&""Tahoma,regular""&5" & Info
End Sub

'Déclaration de variables (chaîne de caractères)
'Création des informations du Pied de page : nom et chemin complet, retour ligne, date d'impression, retour ligne et heure
'affectation des informations au pied page gauche en modifiant la police
Note : Chr(13) ou Chr(10) sont les codes ASCII permettant le saut de ligne  - la police peut également être modifiée par VBA

début                                         Sommaire

Mesurez votre audience