Mettre en forme la zone d’un nom

Bonjour à tous,

Voici une petite vidéo et un fichier qui permet de mettre en forme la zone correspondant à un nom.

Le principe :
- utiliser une mise en forme conditionnelle pour tester si la cellule est comprise entre les min et max des lignes et des colonnes du nom.
- La formule est celle-ci. Le min est facultatif, mais je l'ai laissé, car il est assez pédagogique :
=ET(
LIGNE()>=MIN(LIGNE(Nom));
LIGNE()< =MAX(LIGNE(Nom)); COLONNE()>=MIN(COLONNE(Nom));
COLONNE()< =MAX(COLONNE(Nom)))
2 limites :
- la mise en forme conditionnelle est appliquée à l'ensemble de la feuille, et donc des modifications (par exemple, un glisser déplacer d'une zone) peut affecter cette mise en forme conditionnelle.
- le nom doit correspondre à une seule zone rectangulaire (pas de sélections multiples).

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Petite variation sur le double clic pour inverser des valeurs

Suite au commentaire de Patrick, voici un petit code qui permet d'inverser les valeurs de la colonne 2 vers la 3 et inversement, en double cliquant sur une des colonnes :

Vous pouvez télécharger le fichier ici

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Definition des variables
    Dim arrInverseValeurs As Variant
    ReDim arrInverseValeurs(1 To 1, 1 To 2) As Variant
    'on peut aussi ajouter ici une verification, par exemple
    Dim iColonneGauche As Integer
    Dim iColonneDroite As Integer
    iColonneGauche = 2
    iColonneDroite = 3
    'on verifie qu'on est sur les bonnes colonnes
    If ActiveCell.Column <> iColonneGauche And ActiveCell.Column <> iColonneDroite Then Exit Sub
    'on stocke les valeurs
    arrInverseValeurs(1, 1) = Cells(ActiveCell.Row, iColonneDroite).Value
    arrInverseValeurs(1, 2) = Cells(ActiveCell.Row, iColonneGauche).Value
    'on rapatrie les donnees avec les valeurs inversees
    'l'utilisation du tableau permet de gagner du temps
    'en n'écrivant qu'une seule fois les valeurs dans la feuille
    Range(Cells(ActiveCell.Row, iColonneGauche), Cells(ActiveCell.Row, iColonneDroite)).Value = arrInverseValeurs
    'on sort de la formule
    Cancel = True
End Sub

A bientôt.

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Inverser deux valeurs avec un double clic

Bonjour à tous,

Voici un petit code qui permet d'inverser les valeurs de la cellule active avec la cellule à droite de celle-ci.

J'ai du l'utiliser lors d'un traitement semi-automatique d'extrait de compte. Parfois, le débit et le crédit étaient inversés et je voulais rapidement inverser les données. Un autre avantage est que la formule n'est pas perdue. Un couper-coller aurait remplacé la formule par un #REF.

Voici donc un fichier exemple :
cliquez ici pour télécharger

Et le code:
Vous noterez l'utilisation de l'array (variable tableau : arrInverseValeurs) qui permet de ne coller les données qu'une seule fois, et donc d'économiser un recalcul automatique.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Definition des variables
    Dim arrInverseValeurs As Variant
    ReDim arrInverseValeurs(1 To 1, 1 To 2) As Variant
    'on peut aussi ajouter ici une verification, par exemple
    If ActiveCell.Column <> 2 Or ActiveCell.Row <= 7 Then Exit Sub
    'on stocke les valeurs
    arrInverseValeurs(1, 1) = Cells(ActiveCell.Row, ActiveCell.Column + 1).Value
    arrInverseValeurs(1, 2) = Cells(ActiveCell.Row, ActiveCell.Column).Value
    'on rapatrie les donnees avec les valeurs inversees
    'l'utilisation du tableau permet de gagner du temps
    'en n'écrivant qu'une seule fois les valeurs dans la feuille
    Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column + 1)).Value = arrInverseValeurs
    'on sort de la formule
    Cancel = True
End Sub
PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Créer une image liée, une autre méthode

Bonjour,

Vous connaissez surement la méthode qui permet de créer une image liée à une cellule :
1/ Copier la cellule
2/ Choisir Collage image liée (le dernier bouton en bas à droite de coller)
3/ vous pouvez ensuite déplacer l'image qui a été positionné directement sur la cellule.

Voici une autre manière de faire suggérée par Claude S.
1/ Créer une zone de texte
2/ Sélectionner une plage de cellules
3/ Sélectionner le bord de la zone de texte (curseur en croix)
4/ Coller
5/ Une zone de texte contenant l'image de la plage a été créée au-dessus de la plage sélectionnée.

Sauf erreur, je pense qu'on arrive au même résultat.
La deuxième solution me semble moins rapide, mais j'ai l'impression qu'il y a des utilisations potentielles non soupçonnées... Des idées ?

A bientôt

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Rechercher les liaisons externes liées à une validation

Bonjour à tous,

La présence de liens externes est un problème assez courant et assez très très sioux.

Il faut en général:
- faire une recherche dans l'ensemble du classeur pour un "[" ou un ".xl" (CTRL+F, et choisir de chercher sur tout le classeur)
- vérifier les noms : onglet Formules, Gestionnaire de noms
- vérifier les mises en forme conditionnelle : pour un prochain article.
- vérifier les validations

Voici donc une petite macro qui va permettre de parcourir l'ensemble des validations de données d'un classeur pour identifier celles qui pourraient contenir des liaisons externes.

Une fois la macro exécutée, une nouvelle feuille est ajoutée au fichier.

Vous noterez que j'utilise des variables tableaux pour stocker le résultat. Ça n'est pas nécessaire, mais cela accélère grandement l'exécution du code, donc c'est une bonne pratique au final, surtout si vous avez des dizaines de milliers de lignes.

Option Explicit

Sub TrouveLiensExternesValidation()
    'Definition des variables
    Dim rgeCell As Range
    Dim sDvForm As String
    Dim counter As Integer
    Dim wksResult As Worksheet
    Dim wks As Worksheet
    Dim arrNomFeuille As Variant
    Dim arrAdresseCellule As Variant
    Dim arrFormule As Variant
    ReDim arrNomFeuille(1 To 1) As Variant
    ReDim arrAdresseCellule(1 To 1) As Variant
    ReDim arrFormule(1 To 1) As Variant
    Dim arrResult As Variant
    
    Dim lRow As Long
    Dim lRowResultat As Long
    'masquer l'actualisation
    Application.ScreenUpdating = False
    'compteur
    lRow = 1
    'on boucle sur toutes les feuilles
    For Each wks In ActiveWorkbook.Worksheets
        wks.Visible = xlSheetVisible
        'on boucle sur toutes les cellules de la feuille
        For Each rgeCell In wks.UsedRange.Cells
            'On reprend la formule
            On Error Resume Next
            sDvForm = ""
            sDvForm = rgeCell.Validation.Formula1
            On Error GoTo 0
            'il y a plusieurs options. On peut tester la presence d'un "[" ou bien du ".xl"
            If InStr(1, sDvForm, ".xl") > 0 Then 'on trouve un ".xl"
                lRow = lRow + 1
                'on peut faire un Preserve, car une seule dimension dans le tableau
                ReDim Preserve arrNomFeuille(1 To lRow) As Variant
                ReDim Preserve arrAdresseCellule(1 To lRow) As Variant
                ReDim Preserve arrFormule(1 To lRow) As Variant
                'on stocke les donnees
                arrNomFeuille(lRow) = wks.Name
                arrAdresseCellule(lRow) = rgeCell.Address
                arrFormule(lRow) = "'" & sDvForm
            End If
        Next rgeCell
    Next wks
    'on rapatrie les resultats dans la feuille
    'On revient en A1
    If lRow <> 0 Then
        'creer la feuille de resultat
        Set wksResult = ActiveWorkbook.Sheets.Add(before:=ThisWorkbook.Sheets(1))
        'wksResult.Name = "external links"
        ReDim arrResult(1 To lRow, 1 To 3) As Variant
        arrResult(1, 1) = "Nom de la feuille"
        arrResult(1, 2) = "Adresse de la cellule"
        arrResult(1, 3) = "Formule"
        For lRowResultat = 2 To UBound(arrNomFeuille, 1)
            arrResult(lRowResultat, 1) = arrNomFeuille(lRowResultat)
            arrResult(lRowResultat, 2) = arrAdresseCellule(lRowResultat)
            arrResult(lRowResultat, 3) = arrFormule(lRowResultat)
        Next lRowResultat
        wksResult.Range("A1:C" & UBound(arrResult, 1)).Value = arrResult
    End If
    Application.ScreenUpdating = True
End Sub

Source d'inspiration : http://dailydoseofexcel.com/archives/2009/05/04/finding-external-links-in-data-validation/#comment-1045713

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !