Discussion :

[Philippe Tulliez]

Bonjour,

Si comme moi, vous considérez que la mise à jour de la propriété ColumnWith d'un contrôle ListBox ou ComboBox vous fait perdre beaucoup de temps, voici une procédure d'adaptation automatique de cette propriété en fonction des colonnes de la liste de données définies dans la propriété RowSource de ce même contrôle (Autrement dit la largeur des colonnes de la feuille d'excel).

Il suffit d'agrandir ou diminuer la largeur des colonnes de votre feuille pour adapter automatiquement les colonnes de votre ListBox ou ComboBox
Code de la procédure InitListOrder (présente dans le UserForm)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub InitListOrder()
 Dim tcol() As String, c As Long, tw As Double
 With Me.lstBox
 .RowSource = "'" & rngData.Worksheet.Name & "'!" & rngData.Address
 .ColumnCount = rngData.Columns.Count ' Nombre de colonnes dans ListBox
 .ColumnHeads = True ' Titre
  ReDim tcol(.ColumnCount - 1)
  For c = 1 To .ColumnCount ' Calcul largeur des colonne et le place dans un string par concaténation
   tcol(c - 1) = CStr(rngData.Cells(1, c).Width): tw = tw + tcol(c - 1)
  Next
 .TextAlign = fmTextAlignLeft: .ColumnWidths = Join(tcol, ";"): .ListIndex = 0
 DoEvents
 .Width = tw + UBound(tcol) + 2 ' Rectifie la largeur du ListBox
 Me.Width = Abs((Me.Width < .Width)) * .Width + 20 ' Modifie la largeur du UserForm
 End With
End Sub

Code UserForm_Initialize qui appelle la procédure InitListOrder

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Private Sub UserForm_Initialize()
 Set shtTest = ThisWorkbook.ActiveSheet
 Set rngData = shtTest.Range("A1").CurrentRegion
 Version = shtMenu.Range("B15")
 With Me: .lbl1.Caption = rngData.Cells(1, 1): .lbl2.Caption = rngData.Cells(1, 2): End With ' Label 1 & 2
 With rngData ' Rectifie rngData pour ne pas prendre en compte la ligne d'en-tête
  Set rngData = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
 End With
 InitListOrder ' Initialisation du Contrôle ListBox (lstBox)
 With Me
 .Caption = Version & Space(10) & "Liste des données : " & shtTest.Name
 .Width = .lstBox.Left + .lstBox.Width + 20 ' Rectification de la largeur du UserForm
 End With
End Sub

Le classeur de démonstration contient plusieurs feuilles à visualiser.
Malgré les nombreux tests réalisés et le soin apporté à sa conception il est possible qu'il subsiste un bug. Merci de me le signaler.

[graphikris]

Bonjour Philippe,

Ta contribution est excellente. Par contre, je voudrais y apporter une modification.
En effet, ma feuille Excel va de la colonne A jusqu'à la colonne X.
Le Userform étant plus large que mon écran, je suis obligé de le déplacer avec la souris, ce qui n'est pas très pratique.

Parmi toutes ces colonnes, il y en a certaines que je ne voudrais pas faire apparaître dans la listbox du Userform.
Ces colonnes sont : B, I, J, U, V, W et X.

Pour ma part, je n'ai pas une LstBox mais une ComboBox

Comment faire ?

Je bloque également sur une ligne de ton code que j'ai remplacé mais ça ne fonctionne pas, j'ai un msg me disant : Variable non définie.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
'Set shtTest = ThisWorkbook.ActiveSheet
 Set shtTest = Sheets("Fiche de Progres").Select

Pour ma part, j'ai un Userform avec un CommandButton, ce qui me permet d'ouvrir le Userform que tu as nommé : ufListBoxAutoWidth et qui correspond pour moi au UserForm nommé : UserForm_FP_BdD

Cordialement
Graphikris.

[Philippe Tulliez]

Bonjour Graphikris,

Ta contribution est excellente

Merci pour ton appréciation.

Pour ma part, je n'ai pas une LstBox mais une ComboBox
Comment faire ?

N'ayant eu aucune réaction depuis la publication de cette contribution, je n'avais pas vu l'intérêt à présenter les nouvelles versions.

J'ai publié sur mon blog en octobre 2015 une nouvelle version qui gère aussi bien un ListBox qu'un ComboBox
Cette nouvelle version se nomme AutomaticSizeColumns

Le Userform étant plus large que mon écran, je suis obligé de le déplacer avec la souris, ce qui n'est pas très pratique.

J'ai écrit une nouvelle version (version 2) qui adapte automatiquement la largeur du UserForm si on passe la valeur TRUE à l'argument AutoFormWidth

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Function AutomaticSizeColumns(ctrlList As MSForms.Control, _
                                 oRng As Range, _
                                 Optional AutoFormWidth As Boolean) As Boolean
 ' Author : http://philippe.tulliez.be
 ' Date   : 20/02/2017
 ' Version 2.0 (v 1.0 - 14/10/2015)
 ' Calcul de la propriété ColumnWidths d'un contrôle ListBox ou ComboBox
 '        en fonction de la largeur des colonnes d'une plage de cellules
 '        La propriété ColumnCount du contrôle est également calculée par la fonction qui dépend de la largeur de la liste des données (oRng)
 ' Arguments
 ' ctrlList       (obj Ctrl)  ' Nom du contrôl ListBox ou ComboBox
 ' oRng           (obj Range) ' Plage utilisée pour la liste
 ' [AutoFormWidth] (Boolean) ' Rectifie la taille du UserForm (False par défaut)
 ' Upgrade
 ' v 2 - 20/02/2017 - Ajouté un argument optionel pour adapter la largeur du formulaire parent à la taille du contrôle
 Dim tcol() As String, c As Integer, tw As Double
 
 With ctrlList
  .ColumnCount = oRng.Columns.Count
  ReDim tcol(.ColumnCount - 1)
  For c = 1 To .ColumnCount ' Calcul largeur des colonnes et le place dans un string par concaténation
   tcol(c - 1) = CStr(oRng.Cells(1, c).Width) * 0.85: tw = tw + tcol(c - 1)
  Next
  DoEvents
 .Width = tw + UBound(tcol) + 2 ' Rectifie la largeur du ListBox ou ComboBox
 .ColumnWidths = Join(tcol, ";")
 End With
 ' Rectifie la largeur du USERFORM parent du contrôle
 If AutoFormWidth Then
  With ctrlList
  .Parent.Width = .Left + .Width + 20
  End With
 End If
 AutomaticSizeColumns = True
End Function

Exemple de procédure. La ligne qui invoque la fonction est la septième
Les arguments :Me.lstBox nom du contrôle ListBox), rngData est la plage des données à afficher, True pour indiquer que la largeur du UserForm doit être adaptée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub InitListOrder()
 ' Initialisation du ListBox nommé lstBox
 With Me.lstBox
 .RowSource = rngData.Address(external:=True)
 .ColumnHeads = True ' Titre
 .TextAlign = fmTextAlignLeft: .ListIndex = 0
 AutomaticSizeColumns Me.lstBox, rngData, True  ', Restriction:="-3"
 End With
End Sub

Parmi toutes ces colonnes, il y en a certaines que je ne voudrais pas faire apparaître dans la listbox du Userform.
Ces colonnes sont : B, I, J, U, V, W et X.

Je viens d'apporter une nouvelle mise à jour de la fonction (version 3) qui permet de décider quels sont les colonnes que l'on affiche ou pas à l'aide d'un argument optionnel.

Je publierai dans cette contribution la toute nouvelle version de la fonction dès que je l'aurai publiée sur mon site qui pour l'instant est en cours de transfert et ne me permet pas donc d'effectuer des modifications

[patricktoulon]

bonjour philippe

peut être faudrait il y ajouter les deux mode de source

rowsource et/ou plage quand on utilise pas rowssource
après l'adaptation du width du userform n'est a mon avis pas une bonne idée
il est préférable d'avoir les scroll dans les combo et/ou listboxs et le userform dispos dans l'écran ne serait ce pour avoir a dispos les éventuels autres controls

[graphikris]

Bonjour Philippe et merci à toi.

Par contre je ne comprends plus trop bien. J'ai telechargé sur ton blog, le fichier exemple mais le code que tu mets dans ton actuel post n'est pas le meme.

Pourrais tu mettre un fichier exemple par rapport a ton actuel post ?

En te remerciant.

[Philippe Tulliez]

Bonjour Graphikris,

J'ai telechargé sur ton blog, le fichier exemple mais le code que tu mets dans ton actuel post n'est pas le meme

La version que j'ai publiée ici est la version 2 qui n'est pas encore visible sur mon blog pour les raisons expliquées dans ma réponse.
Mon classeur exemple est en cours de modification pour la version 3 et je ne souhaite pas le rendre publique tant que je ne l'ai pas publiée sur mon blog

[graphikris]

Ok je comprend mieux.
Dans l'attente de cette nlle version.

Cordialement.