XL 2013 Textbox en fonction de trois combobox

fatoum

XLDnaute Nouveau
Bonjour! J'ai réellement besoin de votre Aide. J'ai un userform qui comprend un textbox et trois combobox. Le textbox affiche une valeur seulement si les trois combobox sont renseignés. Mon programme fonctionne très bien mais je le trouve très long. n’y aurai t'il pas un moyen très simple de le faire?

je m'excuse de ne pas pouvoir joindre mon fichier il pèse énormément. Je vous remercie d'avance pour votre intérêt.
 

fatoum

XLDnaute Nouveau
VB:
Private Sub ComboBox1_Change()
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M8")

If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M9")

If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M10")

'2018
'2018
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M8")

If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M9")

If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M10")



'2019
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M8")

If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M9")

If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M10")


'2020
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M8")

If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M9")

If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M10")


End Sub

Private Sub ComboBox2_Change()
'2017
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M8")

If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M9")

If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M10")


'2018
'2018
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M8")

If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M9")

If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M10")



'2019
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M8")

If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M9")

If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M10")


'2020
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M8")

If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M9")

If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M10")


End Sub

Private Sub ComboBox3_CLICK()
'2017
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L8")
If ComboBox1 = "2017" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M8")

If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L9")
If ComboBox1 = "2017" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M9")

If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2017").Range("J10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2017").Range("K10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2017").Range("L10")
If ComboBox1 = "2017" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2017").Range("M10")


'2018
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L8")
If ComboBox1 = "2018" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M8")

If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L9")
If ComboBox1 = "2018" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M9")

If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2018").Range("J10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2018").Range("K10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2018").Range("L10")
If ComboBox1 = "2018" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2018").Range("M10")


'2019
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L8")
If ComboBox1 = "2019" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M8")

If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L9")
If ComboBox1 = "2019" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M9")

If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2019").Range("J10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2019").Range("K10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2019").Range("L10")
If ComboBox1 = "2019" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2019").Range("M10")



'2020
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L8")
If ComboBox1 = "2020" And ComboBox2 = "Barka" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M8")

If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L9")
If ComboBox1 = "2020" And ComboBox2 = "Espoir" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M9")

If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G0" Then TextBox1.Text = Sheets("2020").Range("J10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G1" Then TextBox1.Text = Sheets("2020").Range("K10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G2" Then TextBox1.Text = Sheets("2020").Range("L10")
If ComboBox1 = "2020" And ComboBox2 = "FBC6" And ComboBox3.Value = "G3" Then TextBox1.Text = Sheets("2020").Range("M10")



End Sub
 

Robert

XLDnaute Barbatruc
Repose en paix
Bonjour le fil, bonjour forum,

Je te propose le code ci-dessous à la place de ce que as envoyé. Il implique que les 2 premières ComboBoxes soient renseignées avant la troisième :

VB:
Private O As Worksheet
Private LI As Integer
Private COL As Integer

Private Sub UserForm_Initialize()
Me.ComboBox1.List = Application.Transpose(Array("2017", "2018", "2019", "2020"))
Me.ComboBox2.List = Application.Transpose(Array("Barka", "Espoir", "FBC6"))
Me.ComboBox3.List = Application.Transpose(Array("G0", "G1", "G2", "G3"))
End Sub

Private Sub ComboBox1_Change()
Set O = Worksheets(Choose(Me.ComboBox1.ListIndex + 1, "2017", "2018", "2019", "2020"))
End Sub

Private Sub ComboBox2_Change()
LI = Me.ComboBox2.ListIndex + 8
End Sub

Private Sub ComboBox3_Change()
COL = Me.ComboBox3.ListIndex + 10
Me.TextBox1.Text = O.Cells(LI, COL).Value
End Sub
 

fatoum

XLDnaute Nouveau
salut c'est vraiment génial... je vous remercie grandement pour vos réponses. La réponse de JM27 me convient très bien, j'ai pas encore essayer celle de Robert que je trouve également intéressante.
Encore merci pour l'attention que vous avez porter a cette discussion.
 

Robert

XLDnaute Barbatruc
Repose en paix
Bonjour le fil, bonjour le forum,

La liste des éléments d'une ListBox/ComboBox est indéxée de 0 (le premier élément) à X. X étant le nombre d'éléments de la combobox moins 1.
Si la ComboBox6 possède 15 éléments, par exemple, elle sera indexée de 0 à 14. Si tu sélectionnes le 4ème élément de la ComboBox6, la propriété [ListIndex] sera égale à 3. Vu la configuration de tes données, la TextBox1 prendra la valeur de la cellule se trouvant dans la ligne 3 + 8 et, de la même manière, dans la colonne égale à la propriété [ListIndex] de la ComboBox7 + 10, de l'onglet définit dans la ComboBox5...
Est ce plus clair pour toit ?
 

Discussions similaires