Load combobox with range of another sheet

Question

I have one combobox in one sheet named extraçao with a combobox,

and I have written this code:

  Sub Validar_Idades()

  Dim aba1 As Worksheet
  Set aba1 = Sheets("IDADE")

  Dim aba2 As Worksheet
  Set aba2 = Sheets("EXTRAÇÃO")

  aba2.ComboBox1.Clear
  aba2.ComboBox1.List = aba1.range("A2:A" & aba1.range("A" &         aba1.Rows.Count).End(xlUp).Row).Value

End Sub

Why don't I see combobox1?

Answer 1

There is an important distinction between Worksheet objects and Sheet objects:

• Worksheet can contain only data (in cells)

• Sheet can contain data, and other objects like Charts, combo boxes, list boxes, etc

• To declare a Worksheet variable use Worksheet type

• To declare a Sheet variable use its CodeName, that appears before the name in brackets:

.

Try one of the 2 options bellow

Option Explicit

Sub Validar_Idades()

    Dim aba1 As Worksheet           'Worksheet contain only data (no objects)
    Set aba1 = Worksheets("IDADE")

    'To declare a variable of type Sheet you need to use its "CodeName"

    Dim aba2 As Sheet2              'Sheet can contain data and other objects
    Set aba2 = Sheets("EXTRAÇÃO")   'like ListBoxes, Charts, ComboBoxes, etc

    aba2.ComboBox1.Clear
    aba2.ComboBox1.List = aba1.Range("A2:A" & _
                                aba1.Range("A" & aba1.Rows.Count).End(xlUp).Row).Value
End Sub

---

Sub Validar_Idades2()

    'or simply use its code name instead of a variable

    Sheet2.ComboBox1.Clear
    Sheet2.ComboBox1.List = Sheet1.Range("A2:A" & _
                                Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row).Value
End Sub

Answer 2

The Worksheet class does not have a combobox1 property - since of course not every worksheet has that control on it - which is why the compiler objects to your code. In addition to using the codename, as Paul Bica suggested, (though there is no generic Sheet object - each sheet is effectively its own class), you could simply declare your variable as Object rather than Worksheet so the code is late bound and the compiler won't object. Alternatively, you can access the control through the OLEObjects property of the Worksheet class:

aba2.OLEObjects("ComboBox1").Object.List = aba1.Range("A2:A" & _
                                aba1.Range("A" & aba1.Rows.Count).End(xlUp).Row).Value