XL 2016 Cadrage des nombres dans une ListBox

[Eric_49]

Dans une Listbox multi colonnes, il n'est pas possible de choisir un cadrage individuel par colonne. Tout est à gauche, centré, ou à droite.
Pour une présentation correcte, le texte se cadre en général à gauche et les nombres à droite. Les nombres doivent donc être mises en forme au préalable.

Voici une méthode simple de cadrage, qui se base sur la taille des caractères composant le nombre à afficher. Cette méthode fonctionne quelque soit la police utilisée.

Préalable :

Dans une feuille de calcul, rentrez des données dans les 3 premières colonnes (une 20aine de lignes suffisent pour l'exemple).
Les colonnes 1 et 2 contiendront du texte. La colonne 3 des nombres (en négatif, en positif, petits et grands).

En VB :

Créer un UserForm (taille = moitié de l'écran)
Créer une ListBox (taille = un peu moins large que le formulaire)
Créer un Label sous la ListBox. Il servira pour le formatage
Créer un bouton de commande

Important : La police (font/taille/style) doit être identique pour le Label et pour la ListBox

Le code suivant charge la liste avec les données de la feuille de calcul.
Les montants sont alignés à droite.

Un test est fait sur le contenu de la cellule montant pour éviter d'entrer dans une boucle sans fin si celle-ci est vide (ou égale à un ou plusieurs espaces).

Private Sub CommandButton1_Click()

ListBox1.Clear
ListBox1.ColumnCount = 4  ' Nombre de colonnes (la dernière servira pour visualiser les limites de la colonne 3)
ListBox1.ColumnWidths = "100;100;60;20" ' Taille des colonnes en points
Label1.Visible = False

i1 = 0  ' Nombre de ligne dans ListBox
a$ = "" ' Pour mise en forme des nombres

For i = 1 To 20 ' Charge 20 lignes de la feuille de calcul
  
    ListBox1.AddItem "", i1   ' + 1 ligne en i1
    ListBox1.Column(0, i1) = Cells(i, 1)
    ListBox1.Column(1, i1) = Cells(i, 2)
  
    ' Mise en forme des montants
    a$ = Format(Cells(i, 3), "# ### ##0.00") ' Met la valeur dans a$ avec un format spécifique
    
    If a$<>"" then    ' si a$=vide, on entre dans une boucle sans fin car Label.with renverra
                       ' toujours la même valeur
        Do
            Label1.AutoSize = False ' Pas d'ajustement du label
            Label1.Width = 300      ' largeur à 300 (pour les grands nombres)
            Label1 = a$             ' Place le montant
            Label1.AutoSize = True  ' Taille du label=taille du texte

            If Label1.Width < 50 Then   ' Taille inférieure à la taille de la colonne ? (colonne de 60 mais teste sur 50 pour laisser une marge après)
                a$ = " " & a$           ' Oui : Ajoute un espace devant et boucle
            Else
                Exit Do                 ' Non : Sort
            End If
        Loop
    Else
        a$=" "    ' Force a$ à un espace
    End If

    ListBox1.Column(2, i1) = a$     ' Place le résultat dans la liste
    ListBox1.Column(3, i1) = "|"    ' Caractère droit en dernier pour voir les limites de la colonne précédente

    i1 = i1 + 1 ' Incrémente le compteur de lignes
  
Next i
End Sub

' Pour réaffecter les montants de la ListBox vers une feuille de calcul, il faudra supprimer les espaces.
' Exemple : Cells(1, 4) = Replace(ListBox1.Column(2, 0), " ", "")

 

[CHALET53]

Bonjour,
J'ai testé
Si j'ai un nombre d'enregistrements inférieur à 20 et que je laisse la boucle de 1 à 20, il y a plantage dès lors que la ligne traitée n'est pas renseignée

a+

 

[Eric_49]

Bonjour,
J'ai testé
Si j'ai un nombre d'enregistrements inférieur à 20 et que je laisse la boucle de 1 à 20, il y a plantage dès lors que la ligne traitée n'est pas renseignée

a+

Bonsoir,
Ceci n'est qu'un exemple !! if faut l'adapter à votre code.
On peut placer un test dans la boucle du genre "If cells(i,1)="" then exit do
Bonne soirée

 

[Eric_49]

J'ai modifié le code pour tenir compte des observations. En effet, si la donnée à mettre en forme est vide (ou égale à des espaces), Label1.width renvoie toujours la même valeur. La boucle Do/Loop s'exécute alors sans fin.

 

[job75]

Bonjour Eric_49, CHALET53, le forum,

Utiliser un Label auxiliaire pour ajouter des espaces et cadrer à droite est une bonne méthode.

On peut aussi utiliser une police à chasse fixe comme Courier New ou Consolas :

Private Sub CommandButton1_Click()
Dim i&, x$, maxi%
For i = 1 To 20 'nombre de lignes à adapter
    x = Format(Cells(i, 3), "# ### ##0.00") 'formatage des nombres
    If Len(x) > maxi Then maxi = Len(x) 'détermine le maximum de caractères
Next
With ListBox1
    .Clear
    .ColumnCount = 3  ' Nombre de colonnes
    .ColumnWidths = "100;100" ' Taille des 2 premières colonnes en points
    .Font.Name = "Courier New" 'ou Consolas, police à chasse fixe
    For i = 1 To 20 'nombre de lignes à adapter
        .AddItem Cells(i, 1)
        .List(i - 1, 1) = Cells(i, 2)
        x = Format(Cells(i, 3), "# ### ##0.00")
        x = String(maxi - Len(x), " ") & x
        .List(i - 1, 2) = x
    Next
End With
End Sub

A+

 

[Eric_49]

Bonjour Eric_49, CHALET53, le forum,

Utiliser un Label auxiliaire pour ajouter des espaces et cadrer à droite est une bonne méthode.

On peut aussi utiliser une police à chasse fixe comme Courier New ou Consolas :

Private Sub CommandButton1_Click()
Dim i&, x$, maxi%
For i = 1 To 20 'nombre de lignes à adapter
    x = Format(Cells(i, 3), "# ### ##0.00") 'formatage des nombres
    If Len(x) > maxi Then maxi = Len(x) 'détermine le maximum de caractères
Next
With ListBox1
    .Clear
    .ColumnCount = 3  ' Nombre de colonnes
    .ColumnWidths = "100;100" ' Taille des 2 premières colonnes en points
    .Font.Name = "Courier New" 'ou Consolas, police à chasse fixe
    For i = 1 To 20 'nombre de lignes à adapter
        .AddItem Cells(i, 1)
        .List(i - 1, 1) = Cells(i, 2)
        x = Format(Cells(i, 3), "# ### ##0.00")
        x = String(maxi - Len(x), " ") & x
        .List(i - 1, 2) = x
    Next
End With
End Sub

A+

 

[Eric_49]

Bonjour
Oui bien sûr, mais la présentation est moins belle.
C'est un choix tout personnel.
Bonne journée