[VBA] ComboBox Property

I’m raising this up.

Hello,
No, it’s not possible naturally, but you can
See this trick to get it.
See you later
Experience teaches more surely than advice. (André Gide)
If you bang against a pot and it sounds hollow, it doesn’t necessarily mean the pot is empty. ;-)(Confucius)

Hello lermitte and thanks for the reply,

Actually, by giving you this tip, you must have understood that it happens on 2 Combos, but it’s on one (when I choose one of the 3 cuttings, then this same combo loads new values). I’ll paste the code, you’ll surely understand better. And so I suppose it’s enough to place the DropDown function somewhere in the macro, but I can’t find where:

Option Explicit Private Sub ComboBox1_Change() Dim Plage As Range Dim choix As String Set xlBook = Workbooks("10CQ_MERIGNAC_INSEE_2006.xls") Select Case ComboBox1.Value Case Is = "Découpages INSEE" ComboBox1.Clear With xlBook.Sheets("Données") Set Plage = .Range("B1:B29") End With ComboBox1.List = Plage.Value Case Is = "Découpages Conseil de Quartier" ComboBox1.Clear With xlBook.Sheets("Données") Set Plage = .Range("B31:B42") End With ComboBox1.List = Plage.Value Case Is = "Découpages Mérignac/CUB/Gironde" ComboBox1.Clear With xlBook.Sheets("Données") Set Plage = .Range("B44:B48") End With ComboBox1.List = Plage.Value Case Is = "Retour" ComboBox1.Clear With xlBook.Sheets("Données") Set Plage = .Range("A1:A3") End With ComboBox1.List = Plage.Value End Select choixcombo1 = ComboBox1.Value End Sub

Hello, firstly you put far too much code lines to do what you want..

Select Case ComboBox1.Value Case Is = "Découpages INSEE" ComboBox1.Clear With xlBook.Sheets("Données") Set Plage = .Range("B1:B29") End With ComboBox1.List = Plage.Value Case Is .....

would be better replaced by...

 With xlBook.Sheets("Données") Select Case ComboBox1.Value Case Is = "Découpages INSEE" ComboBox1.RowSource = .Range("B1:B29") Case Is .....

And indeed, the code should indicate it before the End Sub I didn't test it; if there's a problem, you'll tell me and I'll look later. Note: When you receive a modification in the Combo1, is it the Combo1 you modify... Is that right?? See you later Experience teaches more surely than advice. (André Gide) If you hit a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)

* Yes, that's exactly it: "When you receive a modification in Combo1 it's Combo1 that you modify"

* By replacing my code with RowSource (which would indeed lighten things), it gives me a "type mismatch" on the line

ComboBox1.RowSource = .Range("B1:B29")

* Then ComboBox1.DropDown doesn't work either just before the End Sub

Can you put the code you have in the combo event?
--
Experience teaches more reliably than advice. (André Gide)
If you hit a pot and it sounds hollow, it doesn't necessarily mean the pot is empty. ;-)(Confucius)

Option Explicit Private Sub ComboBox1_Change() Dim Plage As Range Dim choix As String Set xlBook = Workbooks("10CQ_MERIGNAC_INSEE_" & Annee & ".xls") With xlBook.Sheets("Données") Select Case ComboBox1.Value Case Is = "Découpages INSEE" ComboBox1.RowSource = .Range("B1:B29") Case Is = "Découpages Conseil de Quartier" ComboBox1.RowSource = .Range("B31:B42") Case Is = "Découpages Mérignac/CUB/Gironde" ComboBox1.RowSource = .Range("B44:B48") Case Is = "Retour" ComboBox1.RowSource = .Range("A1:A3") End Select End With choixcombo1 = ComboBox1.Value ComboBox1.DropDown End Sub

Private Sub UserForm_Initialize() Dim Plage As Range Set xlBook = Workbooks("10CQ_MERIGNAC_INSEE_" & Annee & ".xls") With xlBook.Sheets("Données") Set Plage = .Range("A1:A3") End With ComboBox1.List = Plage.Value ComboBox2.List = Plage.Value End Sub

I made a mistake in the shortening...

 With xlBook.Sheets("Données") Select Case ComboBox1.Value Case Is = "Découpages INSEE" ComboBox1.List= .Range("B1:B29").value Case Is .....

Regarding opening the combo, it doesn’t work if you want to reopen the same combo.
It still seems odd to me that it’s that one you reset.
And in your selection of the "Case Is" you put 4 while you only have 3 data items
Another remark: do you put the same data in Combo1 and 2?
See you
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-)(Confucius)

Contrary to what I said in my previous post, it works normally even if we reopen the same Combo, you misled me with your question Where do I put the code whereas it's quite clear in the tutorial.
That doesn't prevent me from thinking that you program in a funny way.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you hit a pot and it sounds hollow, it's not necessarily the pot that is empty. ;-)(Confucius)

It's mainly that I program almost autodidactically, so the structures must be heavy, yes!

Indeed, my combos 1 and 2 are identical (because the user must make 2 choices), which is why the changes made on each combo affect the same one.

For the history of Case Is 4 (Case Is Return), it's that in each 2nd data series (after making one of the 3 choices), there is at the end of the list a "Return", to be able to go back to the initial choice of the 3.

In this story, you have no ideas where to place this DropDown (even though it’s shown in my code above, it doesn’t operate)? Because the tutorial is not adaptable to my case, right?

No way, it's still about explaining!!! Bon, I will still expand.. Add the following code at the end of YOUR code

 Private Sub ComboBox1_Change() .... .... choixcombo1 = ComboBox1.Value ComboBox1.SetFocus SendKeys "^(F4)" End Sub

Then under End Sub you add...

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 16 Then ComboBox1.DropDown End If End Sub

I hope this time it will be clearer. :D A+ -- L'expérience instruit plus sûrement que le conseil. (André Gide) Si tu te cognes à un pot et que ça sonne creux, c'est pas forcément le pot qui est vide. ;-)(Confucius)