Userform : Selection entre deux Entêtes variables (Combobox)

Version imprimable

20/06/2017, 10h45
coteschweppes

Userform : Selection entre deux Entêtes variables (Combobox)

Bonjour à tous.

Je ne pense pas que mon titre soit très clair donc je vais essayer de l'être le plus possible dans les prochaines lignes.

J'ai un tableau de type

W12 W13 W14 W15 ...

222 -- 839 --

331 34234 -- 342

-- -- 3423 3423

341 -- 342 --

J'aimerai créer une Userform avec deux Combobox renvoyant aux entêtes (Week). Le but étant d'aboutir sur une sortie des informations entre les deux bornes selectionnées dans les Combobox.

(un exemple sera plus clair)
Ex:
Combobox1 = W13
Combobox2 = W15
j'activerai un CommandButton qui devrait me sortir un tableau ou graphique sur:

W13 W14 W15

... 839 ....

34234 ... 342

... 3423 3423

... 342 ....

J'ai fais plusieurs tentative de code, mais je n'y arrive pas.

Pour info, j'ai aussi définit mes plages de données en fonction des entêtes W.
J'ai t'enter une union :

Code:

Application.Union(Range("Test"), Range("Sample")).Select

Mais ça ne sélectionne que les deux plages de données dans les combobox sans ce qu'il y a entre.
EX: Union W12 et W15 ne selectionne pas W13 et W14.

J'espere avoir pu exprimer mon besoin de facon clair, neophyte de la vba, ce n'est pas evident pour moi.

Je vous remercie pour toute réponses ou pistes que vous pourrez m'apporter. :rose::rose:

W12	W13	W14	W15
222	--	839	--
331	34234	--	342
--	--	3423	3423
341	--	342	--

W13	W14	W15
...	839	....
34234	...	342
...	3423	3423
...	342	....

J'ai réussi ma sélection finalement. Fallait juste que je me creuse un peu plus la tête, rien de bien compliqué...
Pourtant je reste persuader que mon code reste du bricolage. Peut-être quelqu'un aurait une solution plus optimiser a me proposer.
Code:

1 2 3 4 5 6 Private Sub CommandButton2_Click() Dim No_colonne1&, No_colonne2 As Integer Sheets("CashFlow").Select No_colonne1 = ComboBox1.ListIndex + 1 No_colonne2 = ComboBox2.ListIndex + 1 ActiveSheet.Range(Cells(2, No_colonne1), Cells(18, No_colonne2)).Select

Bonjour et bienvenue au forum !
Je ne vois pas comment raccourcie encore plus le code... A quoi te sert la sélection ensuite ?
Evite d'utiliser Select/Selection/Activate/ActiveSheet si possible : cela entraîne souvent des problèmes.
Code:

1 2 3 4 5 6 Private Sub CommandButton2_Click() Dim No_colonne1&, No_colonne2 As Integer No_colonne1 = ComboBox1.ListIndex + 1 No_colonne2 = ComboBox2.ListIndex + 1 Sheets("CashFlow").Range(Cells(2, No_colonne1), Cells(18, No_colonne2)).Select En Sub

20/06/2017, 13h13
coteschweppes

Je te remercie.

Ce qui ne me convient pas c'est de référer aux numéros de lignes dans mon code:

Code:

Sheets("CashFlow").Range(Cells(2, No_colonne1), Cells(18, No_colonne2)).Select

J'aurais préféré avoir mes plages de données dynamiques definis pour chaque semaine. Et trouver un moyen de sélectionner tout l'intervalle entre les deux.

--
Par la suite :
Avec un CommandButton1 j'extrait avec un simple copier collé la sélection dans un tableau adjacent.
Avec un CommandButton2 J'utilise la sélection pour construire un graphique combiné qui aura toujours la mm forme. Mais dont les données seront toujours référencées entre les bornes combobox1 et combobox2.
J'en suis actuellement a configurer le commandButton2, qui me donne aussi pas mal de nœuds au cerveau.

PS: Comment fais tu pour voir une section code dans ta réponse.

Merci Riaolle
20/06/2017, 13h31
riaolle

Tu veux copier les colonnes entières sauf la première ligne ? Alors tu peux utiliser ceci :

Code:

Range(Cells(2, No_colonne1), Cells(Rows.Count, No_colonne2)).Copy

Rows.Count donne le nbe de lignes total de la feuillle, donc, au final, tu sélectionnes toutes les lignes.

Pour mettre du code dans ton message : en haut du cadre où tu écris ton message, tu as plusieurs boutons. Parmi ces boutons il y en a un avec un dièze (#). Clique dessus et écris ton code entre les balises qui s'affichent.

Bonjour,

en utilisant un tableau structuré tu peux très facilement travailler sur ton tableau

un tableau structuré (objet ListObject en VBA) possède des zones (titres, données, totaux etc...) et se redimensionne automatiquement quand tu ajoutes des lignes et des colonnes

On peut donc facilement récupérer la liste des en-tête pour les placer dans tes combobox ainsi :
Code:

1 2 3 4 5 6 7 8 Private Sub UserForm_Initialize() Dim Liste() Liste = Application.Transpose(Worksheets("Feuil1").ListObjects(1).HeaderRowRange.Value) With Me .ComboBox1.List = Liste .ComboBox2.List = Liste End With End Sub
Et en définir la plage via la propriété ListColumns de ListObject, qui accepte qu'on utilise le NOM de l'en-tête, sans se soucier du numéro de la colonne
Code:

1 2 3 4 5 6 7 8 9 Private Sub CommandButton1_Click() Dim Plage As Range With Worksheets("Feuil1").ListObjects(1) Set Plage = Range(.ListColumns(Me.ComboBox1.Value).Range, .ListColumns(Me.ComboBox2.Value).Range) End With MsgBox Plage.Address End Sub
Bien sûr, il faut sécuriser la mécanique (si aucun choix dans les combobox, que les choix soient logiques etc...)
Ici j'ai récupérer la zone dans une variable Plage, libre à toi d'en faire un traitement tout autre

22/06/2017, 07h14
Pierre Fauconnier

1 pièce(s) jointe(s)

Salut.

Pour compléter la réponse de Joe, je rappelle que l'on peut travailler directement sur les plages constitutives du tableau structuré, ce qui simplifie encore le code ;)

Pièce jointe 287880

Code:

Debug.Print Application.Sum(Range("t_Prestations[Durée]"))
22/06/2017, 09h25
coteschweppes

Je vous remercie pour toutes vos réponses. J'ai pas mal de boulot en parrallèle en ce moment et j'arrive pas a dégager du temps pour check tout ça. Mais je m'y remet dans peu de temps. Merci à tous. Je reviens vers vous pour vous dire si j'ai bien tout compris!
:rose::rose:

Salut à tous

J'ai une autre question qui vient s'ajouter concernant une autre automatisation de mon tableur.
J'aimerai automatiser la conception d'un graphique combiné.

J'ai utilisé "enregistrer une macro" ,j'ai changé quelques lignes du code pour l'adapter à mon tableau de taille variable.

Il me reste neanmoins quelques soucis...

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
Sub graphique2()
'
' graphique2 Macro
'
 
'
    Range("D25").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("CF_out!$D$25:$R$40")
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.FullSeriesCollection(3).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(3).AxisGroup = 1
    ActiveChart.FullSeriesCollection(4).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(4).AxisGroup = 1
    ActiveChart.FullSeriesCollection(5).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(5).AxisGroup = 1
    ActiveChart.FullSeriesCollection(6).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(6).AxisGroup = 1
    ActiveChart.FullSeriesCollection(7).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(7).AxisGroup = 1
    ActiveChart.FullSeriesCollection(8).ChartType = xlLine
    ActiveChart.FullSeriesCollection(8).AxisGroup = 1
    ActiveChart.FullSeriesCollection(9).ChartType = xlLine
    ActiveChart.FullSeriesCollection(9).AxisGroup = 1
    ActiveChart.FullSeriesCollection(10).ChartType = xlLine
    ActiveChart.FullSeriesCollection(10).AxisGroup = 1
    ActiveChart.FullSeriesCollection(11).ChartType = xlLine
    ActiveChart.FullSeriesCollection(11).AxisGroup = 1
    ActiveChart.FullSeriesCollection(12).ChartType = xlLine
    ActiveChart.FullSeriesCollection(12).AxisGroup = 1
    ActiveChart.FullSeriesCollection(13).ChartType = xlLine
    ActiveChart.FullSeriesCollection(13).AxisGroup = 1
    ActiveChart.FullSeriesCollection(14).ChartType = xlLine
    ActiveChart.FullSeriesCollection(14).AxisGroup = 1
    ActiveChart.FullSeriesCollection(1).IsFiltered = True
    ActiveChart.FullSeriesCollection(4).IsFiltered = True
    ActiveChart.FullSeriesCollection(5).IsFiltered = True
    ActiveChart.FullSeriesCollection(6).IsFiltered = True
    ActiveChart.FullSeriesCollection(7).IsFiltered = True
    ActiveChart.FullSeriesCollection(8).IsFiltered = True
    ActiveChart.FullSeriesCollection(9).IsFiltered = True
    ActiveChart.FullSeriesCollection(10).IsFiltered = True
    ActiveChart.FullSeriesCollection(11).IsFiltered = True
    ActiveChart.FullSeriesCollection(12).IsFiltered = True
    ActiveChart.FullSeriesCollection(13).IsFiltered = True
    Range("L9").Select
 
    ActiveSheet.ChartObjects.Activate ("Graphique 5")
    ActiveSheet.Shapes("Graphique 5").IncrementLeft -210
    ActiveSheet.Shapes("Graphique 5").IncrementTop -168
    ActiveSheet.ChartObjects("Graphique 5").Activate
    ActiveSheet.Shapes("Graphique 5").ScaleWidth 1.8023810149, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Graphique 5").ScaleHeight 1.4603175124, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects("Graphique 5").Activate
    ActiveSheet.ChartObjects("Graphique 5").Activate
    ActiveChart.FullSeriesCollection(3).Select
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 80, 80)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(14).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.400000006
        .Transparency = 0
        .Solid
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent4
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.400000006
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 192, 0)
        .Transparency = 0
    End With
    Range("D12").Select
    ActiveSheet.ChartObjects("Graphique 5").Activate
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Short Terms Liquidity States (betw. W" & TextBox1 & " and W" & TextBox2 & ")"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Short Terms Liquidity States (betw. W" & TextBox1 & " and W" & TextBox2 & ")"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 21).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 21).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    Range("E11").Select
End Sub

Par exemple dans cette partie là, je n'arrive pas a trouver malgrè mes essais un code qui remplacerait ("Graphique 5") à chaque enclenchement de la macro.

Code:

1
2
3
4
5
6
7
8
9
10
   ActiveSheet.ChartObjects.Activate ("Graphique 5")
    ActiveSheet.Shapes("Graphique 5").IncrementLeft -210
    ActiveSheet.Shapes("Graphique 5").IncrementTop -168
    ActiveSheet.ChartObjects("Graphique 5").Activate
    ActiveSheet.Shapes("Graphique 5").ScaleWidth 1.8023810149, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Graphique 5").ScaleHeight 1.4603175124, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects("Graphique 5").Activate
    ActiveSheet.ChartObjects("Graphique 5").Activate

De plus pour la modification de mon Titre de graphique .. Faut il utiliser un INDIRECT?

Code:

ActiveChart.ChartTitle.Text = "Short Terms Liquidity States (betw. W" & TextBox1 & " and W" & TextBox2 & ")"

Je vous remercie pour l'aide :)

23/06/2017, 11h56
riaolle

Citation:

qui remplacerait ("Graphique 5") à chaque enclenchement de la macro.

Remplacer par quoi ?

Citation:

Faut il utiliser un INDIRECT?

Euh.. tu veux faire quoi avec INDIRECT (fonction Excel et pas VBA).