Using worksheet variable instead of explicit sheet name ,throw “Compile error: Method or data member-CodePudding

I am basically need to list a sheet ComboBox when I open the workbook.
If I used a variable instead of sheet name ,I got this error:

Compile error: Method or data member not found

Private Sub Workbook_Open()
 
    ThisWorkbook.Sheets(2).Activate
 
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets(2)
 
    sh.ComboBox1.List = sh.Range("D3:D10").Value    'this cause Compile error
 
    ThisWorkbook.Sheets(2).ComboBox1.List = sh.Range("D3:D10").Value  'this works correctly
 
End Sub

So, How to use a variable sh instead of explicitly sheet name?

CodePudding user response：

Either declare it as Object - since the Worksheet class does not have a Combobox1 property, or using the sheet codename - e.g. Dim sh as Sheet2.

Dim sh As Object
Set sh = ThisWorkbook.Sheets(2)
sh.ComboBox1.List = sh.Range("D3:D10").Value

or:

Dim sh As Sheet2
Set sh = ThisWorkbook.Sheets(2)
sh.ComboBox1.List = sh.Range("D3:D10").Value

Alternatively, keep it as a Worksheet variable and use its OLEObjects property to refer to the control by name:

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(2)

sh.OLEObjects("ComboBox1").Object.List = sh.Range("D3:D10").Value