FAQ ExcelConsultez toutes les FAQ

Nombre d'auteurs : 46, nombre de questions : 845, dernière mise à jour : 14 octobre 2009 

 
OuvrirSommaireLes feuilles de calcul

Attention, il n'existe pas d'objet Sheet. Un classeur renvoie deux collections, Sheets et Worksheets. La collection Sheets représente l'ensemble des feuilles d'un classeur quelque soit leurs types. La collection Worksheets représente l'ensemble des feuilles de calcul d'un classeur. De fait, on utilise la collection Sheets que lorsqu'on doit manipuler un classeur ayant des feuilles mixtes (graphiques et calculs), généralement pour ajouter une feuille graphique vierge.

Vba
Sélectionnez

ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets.Item(ActiveWorkbook.Sheets.Count), Type:=xlChart
Créé le 14 février 2004  par Bidou

En effet, certains objets ne donnent pas accès aux fonctionnalités Intellisense. Pour contourner le problème, utilisez une variable:

Vba
Sélectionnez

Dim objFeuille As Worksheet
 
Set objFeuille = ActiveWorkbook.ActiveSheet



A partir de là, objFeuille vous fournira ses propriétés / méthodes.

Créé le 14 février 2004  par Bidou

Sélectionnez une cellule dans la feuille.
Maintenez enfoncée la touche Ctrl et utilisez la molette de la souris.

Créé le 9 avril 2007  par SilkyRoad

Target représente la plage des cellules affectées par l'événement. A ce titre, c'est un objet Range. Pour savoir si cette plage contient un élément d'un plage particulière, on teste l'intersection des deux plages.

Vba
Sélectionnez

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect("Target", Range("A1")) Is Nothing Then
        .........
    End If
End Sub
Créé le 14 février 2004  par Bidou

Faites un clic droit sur l'onglet de la feuille.
Choisissez l'option "Visualiser le code".
Affichez la fenêtre "Propriétés" (raccourci clavier F4).
Saisissez dans le champ "ScrollArea": $A$1:$G$50
Fermez l'éditeur VBE.
Vous limitez ainsi l'accès de la ligne 50 et de la colonne G, dans la feuille sélectionnée.

Vous pouvez aussi utiliser la propriété ScrollArea par macro:
La macro suivante empêche également de faire défiler la feuille active au-delà de la ligne 50 et de la colonne G.

Vba
Sélectionnez

Sub LimiteDefilement()
  ActiveSheet.ScrollArea = "A1:G50"
End Sub




Remarque:
Qu'elle soit paramétrée manuellement ou par macro, la propriété ScrollArea est réinitialisée à la fermeture du classeur. A chaque réouverture, cette propriété contient un champ vide. Utilisez l'évènement Workbook_Open si vous souhaitez automatiser la restriction à chaque ouverture du fichier:

Vba
Sélectionnez

Private Sub Workbook_Open()
    Worksheets ("Feuil1").ScrollArea = "A1:G50"
End Sub




Pour réinitialiser l'accès à l'ensemble de la feuille par macro, utilisez:

Vba
Sélectionnez

Sub RetablitDefilement()
  ActiveSheet.ScrollArea = ""
End Sub 



Mis à jour le 13 novembre 2007  par SilkyRoad
Vba
Sélectionnez

Sub test()
    supprimerFeuillesVides ThisWorkbook
End Sub
Vba
Sélectionnez

Sub supprimerFeuillesVides(Wb As Workbook)
    Dim Ws As Worksheet
 
    Application.ScreenUpdating = False
 
    For Each Ws In Wb.Worksheets
        If Ws.UsedRange.Cells.Address = "$A$1" And _
                IsEmpty(Ws.Range("A1")) And Ws.Shapes.Count = 0 Then
 
            Application.DisplayAlerts = False
            If Wb.Worksheets.Count > 1 Then Ws.Delete
            Application.DisplayAlerts = True
 
        End If
    Next Ws
 
    Application.ScreenUpdating = True
 
End Sub
Créé le 11 avril 2007  par SilkyRoad

Utilisez en premier lieu l'aide Excel (F1) très bien documentée.
Vous pouvez aussi consulter le tutoriel sur la description de l'objet Feuille de calcul dans Excel.

Créé le 15 avril 2007  par SilkyRoad

Lorsque votre classeur est constitué de nombreuses feuilles, il est parfois pratique d'afficher la liste des onglets dans un menu contextuel.
Pour obtenir cette liste, faites un clic droit sur la gauche de la barre d'onglets.
Ensuite, sélectionnez le nom d'onglet que vous souhaitez atteindre.

Image non disponible


Il est aussi possible d'afficher ce menu par macro:

Vba
Sélectionnez

Application.CommandBars("Workbook tabs").ShowPopup 400, 100
Créé le 15 avril 2007  par SilkyRoad

Pour déplacer une feuille vers un autre classeur, faites glisser l'onglet dans la fenêtre de ce classeur.
Pour copier la feuille, maintenez la touche Ctrl enfoncée et faites glisser l'onglet.

Une autre solution consiste à faire un clic droit dans l'onglet à déplacer.
Ensuite, sélectionnez l'option Déplacer ou copier.
Sélectionnez le classeur de destination puis la feuille à copier, dans la boîte de dialogue.
Cochez l'option Créer une copie pour dupliquer la feuille dans le classeur de destination.

Créé le 22 avril 2007  par SilkyRoad

La formule générique est :

Vba
Sélectionnez

NumCol = Cells(1, 72).Column
MsgBox IIf(NumCol > 26, Chr(64 + NumCol \ 26) & Chr(64 + NumCol Mod 26), Chr(64 + NumCol))




D'autres possibilités à partir d'Excel2000, Pour une plage nommée :

Vba
Sélectionnez

MsgBox Split(Range("Plage").Address(1, 0), "$")(0)



Pour un objet Range :

Vba
Sélectionnez

Split(MaPlage.Address, "$")(1)
Mis à jour le 7 novembre 2007  par Bidou, Pierre Fauconnier

Cette option n'est disponible qu'à partir de la version Excel 2002.

En mode feuille de calcul: Click droit sur l'onglet >> Couleur d'onglet...


Par le code VBA:

Vba
Sélectionnez

'-- Applique une couleur jaune à l'onglet de la Feuil1 
WorkSheets("Feuil1").Tab.ColorIndex = 6
Créé le 14 février 2004  par SilkyRoad

Les sauts de pages peuvent être verticaux (VPageBreaks) ou horizontaux (HPageBreaks). Seuls les sauts de page manuels (VPageBreaks(i).Type = xlPageBreakManual) peuvent être supprimés.

Cet exemple supprime tous les sauts de pages verticaux dans le 1er onglet.

Vba
Sélectionnez

Dim i As Integer
Dim Wb As Workbook
 
Set Wb = ThisWorkbook
 
MsgBox Wb.Worksheets(1).VPageBreaks.Count
 
For i = Wb.Worksheets(1).VPageBreaks.Count To 1 Step -1
    If Wb.Worksheets(1).VPageBreaks(i).Type = xlPageBreakManual Then _
        Wb.Worksheets(1).VPageBreaks(i).Delete
 
Next




Un exemple pour supprimer tous les sauts de pages manuels (verticaux et horizontaux) dans la feuille:

Vba
Sélectionnez

Worksheets(1).ResetAllPageBreaks
Créé le 20 août 2007  par SilkyRoad

Suivez cette procédure pour personnaliser la numérotation des pages dans une feuille.

Dans la feuille active:
Menu Fichier
Mise en page
Onglet Page
Dans le champ "Commencer la numérotation à:", saisissez la valeur que vous souhaitez utiliser pour la première page.
Si par exemple vous indiquez 2: la première page de la feuille active prendra cette valeur, La deuxième page de la feuille active prendra le numéro 3 ... etc ...

Créé le 2 octobre 2007  par SilkyRoad

Compléter les en-têtes et pieds de pages peut devenir une tâche fastidieuse lorsque le classeur comporte un nombre important d'onglets.
Il est possible d'effectuer, via un bout de code en VBA, cette tâche une seule fois et de l'appliquer automatiquement à l'ensemble des onglets.

Consultez l'article de Fring.

Créé le 26 mai 2008  par Fring

Cette option est disponible à partir d'Excel2002.

Remarque :
Par VBA, Il est nécessaire que "&G" fasse partie de la chaîne de la propriété LeftHeader, afin que l'image s'affiche dans l'en-tête gauche.

Vba
Sélectionnez

Sub insertionImage_EntetePage()
 
    With ActiveSheet.PageSetup.LeftHeaderPicture
        .Filename = _
            "C:\Documents and Settings\michel\dossier\monImage.jpg"
        .Height = 40 ' redéfinit la largeur de l'image
        .Width = 80 ' redéfinit la hauteur de l'image
    End With
 
    'Remarque Il est nécessaire que « &G » fasse partie de la chaîne
    'de la propriété LeftHeader afin que l'image s'affiche dans l'en-tête gauche.
    'info issue de l'aide en ligne Excel.
 
    ActiveSheet.PageSetup.LeftHeader = "&G"
End Sub
Créé le 26 mai 2008  par SilkyRoad

Passez en mode "Mise en page" afin d'accéder à l'en-tête de la feuille.

Image non disponible

Sélectionnez une des trois zones au dessus du quadrillage de la feuille.
Un nouvel onglet "Création/Outils des en-têtes et pieds de page" apparait dans le ruban.
Dans le groupe "En-tête et pied de page", cliquez sur le bouton "En-tête".
Sélectionnez "Page 1 de ?" dans la liste déroulante.
Remarquez que de nombreuses autres options sont disponibles pour personnaliser les en-têtes.
Le champ est ajouté dans l'en-tête et affiche automatiquement les numéros de page sur le nombre total.

Vous pouvez bien entendu effectuer la même opération sur les pieds de page.

Créé le 18 novembre 2008  par SilkyRoad

Si les tableaux sont structurés de manière identique, il est possible d'utiliser la fonction de consolidation d'Excel.

Sélectionnez une feuille vierge.
Utilisez le menu "Données".
Sélectionnez l'option "Consolider".

Image non disponible

Sélectionnez une plage de cellules et cliquez sur le bouton "Ajouter" pour intégrer chaque tableau dans la consolidation.
Indiquez la fonction à appliquer aux données (Somme, Nombre, Moyenne, Max ?etc ?)
Cochez les options "Ligne du haut" et/ou "Colonne de gauche" si vous souhaitez afficher les entêtes de lignes et de colonnes.
Cliquez sur le bouton OK pour valider.
Le tableau consolidé s'affiche dans la cellule active.
Utilisez le bouton "Parcourir" pour récupérer des données externes.


Ce qui pourrait s'écrire de façon équivalente par macro:

Vba
Sélectionnez

Feuil4.Range("B2").Consolidate Sources:=Array("[NomClasseur.xls]Feuil1!R1C1:R10C4", _
        "[NomClasseur.xls]Feuil2!R1C1:R5C4", "[NomClasseur.xls]Feuil3!R1C1:R5C4"), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False




Si vous utilisez Excel2007, cliquez sur l'onglet "Données" dans le ruban.
Cliquez sur le bouton "Consolider" dans le groupe "Outils de données".
La suite de la procédure reste identique aux versions précédentes d'Excel.

Créé le 5 décembre 2007  par SilkyRoad

Il n'est pas possible de protéger une feuille dont le classeur est déjà partagé. La solution suivante enlève le partage provisoirement, le temps de protéger la feuille mais cela signifie que vous perdez tous les autres avantages du partage:

* Les modifications en cours qui n'ont pas été enregistrées par les autres utilisateurs seront perdues.
* L'historique des modifications sera effacé. Vous devrez donc créer préalablement une sauvegarde de l'historique.

Vba
Sélectionnez

Private Sub Workbook_Open()
    'testé sous Excel2007
    Dim Fichier As String
 
    Fichier = ThisWorkbook.FullName
    Application.DisplayAlerts = False
 
    'Protège la feuille "Feuil3" et autorise les modifications uniquement par macro (UserInterfaceOnly:=True)
    With ActiveWorkbook
        .ExclusiveAccess
        .UnprotectSharing '"Password"
        .Worksheets("Feuil3").Protect Contents:=True, UserInterfaceOnly:=True
        .ProtectSharing Filename:=Fichier ', Password:="Password"
    End With
 
    Application.DisplayAlerts = True
End Sub
Créé le 5 décembre 2007  par SilkyRoad

C'est possible en utilisant les bibliothèques "Microsoft ActiveX Data Objects 2.x Library" et "Microsoft ADO Ext. 2.x for DDL and Security":

Vous remarquerez que:
     Le nom des feuilles est suivi du symbole $.
     Les noms sont renvoyés par ordre alphabétique.
     Les noms qui contiennent un espaces sont encadrés par une quote '.
     Les cellules et plages nommées (qui sont considérées comme des tables) sont aussi listées.
     Le nom des feuilles créées dynamiquement (voir le chapitre IV-D) n'ont pas le symbole $.
     Le nom des feuilles créées dynamiquement est renvoyé 2 fois: Par l'onglet et la plage nommée associée.

Vba
Sélectionnez

Sub ListeFeuillesClasseurFerme() 
    Dim XlConnect As Object, XlCatalog As Object 
    Dim Fichier As String, Resultat As String 
    Dim Feuille As Object 
 
    Fichier = "C:\dossier\Nom classeur.xls" 
 
    Set XlConnect = CreateObject("ADODB.Connection") 
    Set XlCatalog = CreateObject("ADOX.Catalog") 
 
    XlConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier & _ 
            ";Extended Properties=Excel 8.0;" 
    Set XlCatalog.ActiveConnection = XlConnect 
 
    For Each Feuille In XlCatalog.Tables 
        Resultat = Resultat & Feuille.Name & vbCrLf 
    Next 
 
    MsgBox Resultat 
End Sub 




Et une fonction pour vérifier si une feuille spécifique existe (renvoie vrai ou faux).

Vba
Sélectionnez

Sub Test()
    MsgBox WorksheetExist("C:\dossier\rapport.xls", "Feuil1")
End Sub
 
 
Function WorksheetExist(XlFile As String, strWsName As String) As Boolean
    Dim XlConnect As Object, XlCatalog As Object
    Dim Feuille As Object
 
    Set XlConnect = CreateObject("ADODB.Connection")
    Set XlCatalog = CreateObject("ADOX.Catalog")
 
    XlConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & XlFile & _
            ";Extended Properties=Excel 8.0;"
    Set XlCatalog.ActiveConnection = XlConnect
 
    On Error Resume Next
    Set Feuille = XlCatalog.Tables(strWsName & "$")
    On Error GoTo 0
 
    If Feuille Is Nothing Then
        WorksheetExist = False
    Else
        WorksheetExist = True
    End If
 
    XlConnect.Close
    Set XlConnect = Nothing
End Function
Créé le 19 février 2008  par SilkyRoad

Lien : Lire et écrire dans les classeurs Excel fermés

Ces exemples effacent le contenu d'une feuille nommée "Feuil1".
En utilisant la bibliothèque "Microsoft ActiveX Data Objects 2.x Library":

Vba
Sélectionnez

Sub EffacerContenuFeuille_ClasseurFerme()
    Dim Cn As ADODB.Connection
    Dim Fichier As String
 
    Fichier = "C:\dossier\NomClasseur.xls"
 
    Set Cn = New ADODB.Connection
    With Cn
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier & _
            ";Extended Properties=Excel 8.0;"
        .Execute "DROP TABLE [Feuil1$]"
        .Close
    End With
 
    Set Cn = Nothing
End Sub




En utilisant la bibliothèque "Microsoft ADO Ext. 2.x for DDL and Security":

Vba
Sélectionnez

Sub EffacerContenuFeuille_ClasseurFerme_V02()
    Dim Cat As ADOX.Catalog
    Dim Fichier As String
 
    Fichier = "C:\dossier\NomClasseur.xls"
 
    Set Cat = New ADOX.Catalog
    Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier & _
            ";Extended Properties=Excel 8.0;"
    Cat.Tables.Delete ("Feuil1$")
 
    Set Cat = Nothing
End Sub
Créé le 19 février 2008  par SilkyRoad

Lien : Lire et écrire dans les classeurs Excel fermés

Cette méthode peut servir à comparer les données contenues dans deux classeurs.

Sous Excel 2007:
Ouvrez les deux classeurs.
Activez les feuilles à comparer dans chaque classeur.
Sélectionnez l'onglet "Affichage".
Cliquez sur le bouton "Afficher côte à côte".
(Si plus de 2 classeurs sont déjà ouverts, Excel affiche une fenêtre pour choisir quel fichier va être comparé avec le classeur actif.)
Le bouton "Défilement synchrone" (qui était jusque là grisé) est maintenant activé.
Les deux feuilles défilent désormais de manière synchrone lorsque vous utilisez la barre de défilement ou la molette de la souris.

Image non disponible


Par défaut, les deux feuilles sont affichées horizontalement.
Cliquez sur le bouton "Réorganiser tout" pour modifier la mise en page des feuilles (par exemple verticalement).

Le bouton "Rétablir la position de la fenêtre" permet de réafficher horizontalement les deux feuilles à comparer.

Nota :
Vous pouvez désactiver provisoirement la fonction de défilement en cliquant sur le bouton "Défilement synchrone".

Créé le 26 mai 2008  par SilkyRoad

Sélectionnez plusieurs colonnes (contigües ou non contigües).
Cliquez sur le bord de l'entête de plus à droite de la sélection.

Image non disponible

Redéfinissez la largeur de la colonne à en gardant le bouton gauche de la souris enfoncée et en la déplaçant horizontalement.

Toutes les colonnes sélectionnées sont redimensionnées à l'identique lorsque vous relâchez la souris.


Le principe est identique pour définir rapidement la hauteur des lignes.

Créé le 26 mai 2008  par SilkyRoad

Utilisez le bouton "Office"
Bouton "Options Excel"
Sélectionnez le menu "Standard"
Dans la partie "Lors de la création de classeurs", modifiez à votre convenance le nombre inscrit dans le champ "Inclure des feuilles" (le nombre de feuilles initial est 3).
Cliquez sur le bouton OK pour valider.

Le nombre maximal de feuilles par défaut est 255. Si vous avez besoin de feuilles supplémentaires vous devrez les insérer manuellement : Sous Excel2007, vous pouvez insérer une nouvelle feuille en cliquant sur le dernier onglet sur la droite dans la barre d'onglets. Vous pouvez également utiliser le raccourci clavier MAJ+F11.

Créé le 20 septembre 2008  par SilkyRoad

Cette option se trouve en bas dans la barre d'état, juste à coté du curseur de zoom.

Le menu possède 3 boutons :
Normal,
Mise en page (telles qu'apparaitront les feuilles lors de l'impression),
Aperçu des sauts de page.

Image non disponible

Créé le 20 septembre 2008  par SilkyRoad

Une solution consiste à insérer une formule matricielle dans cette zone de cellules.
Il s'agit plus d'une protection contre les mauvaises manipulations mais qui sera déjà suffisamment efficace pour la majorité des utilisateurs.

Par exemple, vous disposez d'un tableau dans les colonnes A à E.
Nous allons utiliser les noms de champs dans la première ligne du tableau pour créer une formule matricielle.
Sélectionnez la cellule A1.
Collez la formule suivante dans la cellule (ChampX correspond à chaque nom d'en-tête dans votre tableau).
=TRANSPOSE({"Champ1";"Champ2";"Champ3";"Champ4";"Champ5"})
Sélectionnez la plage A1:E1.
Appuyez sur la touche clavier F2.
Validez la formule matricielle : Ctrl+Maj+Entrée.

Maintenant, un message bloquant s'affiche lorsque vous tentez une insertion ou une suppression entre les colonnes A et E : "impossible de modifier une partie de matrice".

Supprimez la ligne d'en-tête pour annuler la protection.


Nota :
Si vous êtes bloqué dans la matrice, appuyez deux fois sur la touche Echap.

Créé le 18 novembre 2008  par SilkyRoad

Remarque :
Cette solution renvoie une erreur si la colonne A ne contient pas de cellule vide entre les différents enregistrements.

Vba
Sélectionnez

'Supprime la ligne complète si les cellules de
'la colonne A sont vides.
Dim x As Long
 
x = Range("A65536").End(xlUp).Row
Cells.Range("A1:A" & x). _
    SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Créé le 19 février 2009  par Michel_M

Excel dispose de la fonction =CELLULE("nomfichier") qui permet d'afficher le chemin, le nom du classeur et le nom de la feuille contenant la formule.
Il suffit ensuite d'écrire une fonction qui va extraire uniquement le nom de la feuille.

Le classeur doit être préalablement sauvegardé.

Formule
Sélectionnez

=DROITE(CELLULE("nomfichier";A1);NBCAR(CELLULE("nomfichier";A1))-TROUVE("]";CELLULE("nomfichier";A1)))
Créé le 19 février 2009  par SilkyRoad

Effectuez un clic droit sur la barre d'onglets.
Choisissez l'option 'Afficher' dans le menu contextuel.
Sélectionnez la feuille à réafficher dans la boîte de dialogue.
Cliquez sur le bouton OK pour valider.

Créé le 19 février 2009  par SilkyRoad
  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2009 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.