Combobox inside a multipage inside a frame on a worksheet

[Ploos]

Hi all,

I have a worksheet (sheet1) and it has a frame (frame1). Inside the frame there is a multipage (multipage1) en inside that multipage, on page1 there is a combobox (combobox1). I would like to know the right way to tell VBA how to fill that combobox using a list that's on sheet2.
I know how to do this with regular comboboxes and with comboboxes in frames. But I don't know how to include the multipage.

Please can someone help me??

Kind regards,
Sophie

 

[RoryA]

You wouldn't possible know how to adress a commandbutton in that same multipage in that frame on that worksheet? :S Double clicking leads to a "Frame1_click()" which I don't want. I'm sure it can be easy.. but I don't see it.

You can't write a click event for a control that isn't the one directly embedded in the worksheet. I'd strongly suggest you either use an actual userform, or rethink your entire approach to use Form controls and not a multipage.

 

[pijaku]

Hi Rory,

One year latter, sorry to be late!... And sorry for my poor English§ (I'm French )

You can't write a click event for a control that isn't the one directly embedded in the worksheet

Yes we can !
There is a simple example with a Frame which contains 2 controls (CommandButton + ComboBox).

In Design Mode, insert this code into the Sheet's Module :

[COLOR=#0000FF]Option[/COLOR][COLOR=#0000FF]Explicit[/COLOR] 
[COLOR=#0000FF]Private [/COLOR][COLOR=#0000FF]WithEvents[/COLOR][COLOR=#333333] Combo1 [/COLOR][COLOR=#0000FF]As[/COLOR][COLOR=#333333] MSForms.ComboBox
[/COLOR][COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]WithEvents[/COLOR] Bouton1 [COLOR=#0000FF]As[/COLOR] MSForms.CommandButton
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] Bouton1_Click()
    MsgBox Combo1.[COLOR=#0000FF]Text[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] Combo1_Change()
    Range([COLOR=#FF0000]"A1"[/COLOR]) = Combo1.[COLOR=#0000FF]Text[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] Worksheet_Activate()
    [COLOR=#0000FF]With[/COLOR] Frame1
        .Controls([COLOR=#FF0000]"Combobox1"[/COLOR]).List = Range([COLOR=#FF0000]"F1:F5"[/COLOR]).Value
        [COLOR=#0000FF]Set[/COLOR] Combo1 = .Controls([COLOR=#FF0000]"Combobox1"[/COLOR])
        [COLOR=#0000FF]Set[/COLOR] Bouton1 = .Controls([COLOR=#FF0000]"CommandButton1"[/COLOR])
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]With[/COLOR] 
[COLOR=#0000FF]End [/COLOR][COLOR=#0000FF]Sub[/COLOR]

Or, if you want create the Frame and it's controls at Run Time, insert this code into the ThisWorkbook's Module :

[COLOR=#0000FF]Option[/COLOR][COLOR=#0000FF]Explicit[/COLOR] 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]WithEvents[/COLOR] Combo1 [COLOR=#0000FF]As[/COLOR] MSForms.ComboBox
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]WithEvents[/COLOR] Bouton1 [COLOR=#0000FF]As[/COLOR] MSForms.CommandButton
 
[COLOR=#0000FF]Private[/COLOR] Wsh [COLOR=#0000FF]As[/COLOR] Worksheet
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Const[/COLOR] strSheetName [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]String[/COLOR] = [COLOR=#FF0000]"Feuil1"[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Const[/COLOR] strFrameName [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]String[/COLOR] = [COLOR=#FF0000]"MyFramePerso"[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Const[/COLOR] strComboName [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]String[/COLOR] = [COLOR=#FF0000]"MyComboPerso"[/COLOR]
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Const[/COLOR] strButtName [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]String[/COLOR] = [COLOR=#FF0000]"MyButtonPerso"[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] Bouton1_Click()
    MsgBox Combo1.[COLOR=#0000FF]Text[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] Combo1_Click()
    Range([COLOR=#FF0000]"A1"[/COLOR]) = Combo1.[COLOR=#0000FF]Text[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] Workbook_BeforeClose(Cancel [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Boolean[/COLOR])
[COLOR=#0000FF]On[/COLOR] [COLOR=#0000FF]Error[/COLOR] [COLOR=#0000FF]Resume[/COLOR] [COLOR=#0000FF]Next[/COLOR]
    Wsh.Shapes.Range(Array(strFrameName)).Delete
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] Workbook_Open()
[COLOR=#0000FF]Dim[/COLOR] Frm [COLOR=#0000FF]As[/COLOR] OLEObject
    [COLOR=#0000FF]Set[/COLOR] Wsh = Worksheets(strSheetName)
    InsertFrame Frm, Range([COLOR=#FF0000]"F20"[/COLOR]), [COLOR=#CC66CC]200[/COLOR], [COLOR=#CC66CC]100[/COLOR]
    InsertControlsInFrame Frm
    AffectVariables
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] InsertFrame(F [COLOR=#0000FF]As[/COLOR] OLEObject, rngCell [COLOR=#0000FF]As[/COLOR] Range, W [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Single[/COLOR], H [COLOR=#0000FF]As[/COLOR] [COLOR=#0000FF]Single[/COLOR])
    [COLOR=#0000FF]Set[/COLOR] F = Wsh.OLEObjects.Add([COLOR=#FF0000]"Forms.Frame.1"[/COLOR])
    [COLOR=#0000FF]With[/COLOR] F
        .Name = strFrameName
        .Height = H
        .Width = W
        .Left = rngCell.Left
        .Top = rngCell.Top
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]With[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] InsertControlsInFrame(F [COLOR=#0000FF]As[/COLOR] OLEObject)
    [COLOR=#0000FF]With[/COLOR] F
        [COLOR=#0000FF]With[/COLOR] .Object.Add([COLOR=#FF0000]"Forms.ComboBox.1"[/COLOR])
            .Name = strComboName
            .Top = [COLOR=#CC66CC]15[/COLOR]
            .Left = [COLOR=#CC66CC]30[/COLOR]
            .Height = [COLOR=#CC66CC]20[/COLOR]
            .Width = [COLOR=#CC66CC]75[/COLOR]
            .Object.Font.Name = [COLOR=#FF0000]"Arial"[/COLOR]
            .Object.Font.Size = [COLOR=#CC66CC]12[/COLOR]
            .Object.AddItem [COLOR=#FF0000]"Ananas"[/COLOR]
            .Object.AddItem [COLOR=#FF0000]"Pomme"[/COLOR]
            .Object.AddItem [COLOR=#FF0000]"Poire"[/COLOR]
        [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]With[/COLOR]
        [COLOR=#0000FF]With[/COLOR] .Object.Add([COLOR=#FF0000]"Forms.CommandButton.1"[/COLOR])
            .Name = strButtName
            .Top = [COLOR=#CC66CC]45[/COLOR]
            .Left = [COLOR=#CC66CC]30[/COLOR]
            .Height = [COLOR=#CC66CC]20[/COLOR]
            .Width = [COLOR=#CC66CC]75[/COLOR]
            .Object.Font.Name = [COLOR=#FF0000]"Arial"[/COLOR]
            .Object.Font.Size = [COLOR=#CC66CC]12[/COLOR]
            .Object.Caption = [COLOR=#FF0000]"BOUTON3"[/COLOR]
        [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]With[/COLOR]
        .Verb
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]With[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]
 
[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] AffectVariables()
    [COLOR=#0000FF]Set[/COLOR] Combo1 = Wsh.OLEObjects(strFrameName).Object.Controls(strComboName)
    [COLOR=#0000FF]Set[/COLOR] Bouton1 = Wsh.OLEObjects(strFrameName).Object.Controls(strButtName) 
[COLOR=#0000FF]End [/COLOR][COLOR=#0000FF]Sub[/COLOR]