Search code examples
excelvbamethodscomboboxmember

ActiveX ComboBox method or data member not found


I have a combobox (called userBox) within a sheet called Home. It has one of the options selected, let's say "User A". All I'm trying to do is assign "User A" to string usr, but I keep getting the compile error:

Method or data member not found

Sub fixPls()
Dim row As Integer, col As Integer, usr As String, tbl As String, found  As Boolean, k As Integer, payType As String
Set wb = ThisWorkbook
Set ws = wb.Sheets("Home")
ws.Activate

Application.DisplayAlerts = False
Application.ScreenUpdating = False

lastRow = Range("B16").End(xlUp).row
usr = ws.userBox.Value
tbl = ws.tblBox.Value
payType = ws.tpBox.Value
....

EDIT: I tried a dummy program in a new workbook, and it worked. Using an activeX comboBox, why is it different?

Sub blah()
Dim rly As String
rly = Sheets(1).ComboBox1.Value
ThisWorkbook.Sheets(1).Cells(1, 10) = rly 
End Sub

Cell J1 returns the value selecting in the comboBox.


Solution

  • If it's an ActiveX control, you need to use the OLEObjects collection to access it:

    Debug.Print ws.OLEObjects("userBox").Object.Text