Run-time error 438. Object doesn't support this property or method.

[grooviness]

Hello. I was unable to find any leads on this by searching. I have a workbook with a sheet containing several combo boxes. The combo boxes are populated by code in the This Workbook module when the file opens. Some people can open the workbook without problems. Some other people get a "Run-time error 438. Object doesn't support this property or method" error.

The combo-boxes are populated upon opening because some people aren't allowed to make certain selections. The VBA looks at what directory the file is in and decides how to fill the combo-boxes.

This is a long-standing report and I can't figure out why this is happening all of a sudden, and only to some people. When the user clicks "debug" the line I changed to red is highlighted. I've checked to make sure the combo box has the correct name and there's nothing in the list fill range in the properties box. We're on Office 365 (a farily recent change). I've had the user I'm working with set options to trust all macros and trust access to the VBA project module.

Application.ScreenUpdating = False
ActiveWorkbook.EnableAutoRecover = False
 
Dim MyPath As String
 
MyPath = UCase(Application.CommandBars("Web").Controls("Address:").Text)
 
Sheets("MySheet").cboProgram.List = Array("(All)", "aaaa", "bbbb")
Sheets("MySheet").cboProgram.ListIndex = 0

I'd rather not post the entire module of code because I'd have to redact a lot of stuff. It's more of this type of thing, plus some if/else about the file location.

Thanks for any suggestions you have.

 

[RoryA]

This is one of the issues with using activex controls on worksheets. Unless everyone is on exactly the same version of Office and Windows (including the same updates) you can get incompatibilities like that. See for example the problems and related fixes (deleting .exd files for one) here: http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/

 

[grooviness]

Thanks for the direction. Do you have any idea why this would happen with some users who have been upgraded to 365 but not all? Just curious.

 

[RoryA]

Each computer stores .exd files when activex controls have been used on it. If the version of the control information stored in the exd file is not the same as the current installed version of the controls, you can end up with problems. My guess would be that some had older exd files.

If I were you, I would see if it's practical to do away with the use of activex altogether, either by using Form controls instead, or by embedding the controls on a userform rather than in a worksheet.

 

[DanteAmor]

Only to rule out the problem of the name of the activex control.
Try the following:

Private Sub CommandButton1_Click()
  Dim c As OLEObject
  For Each c In Sheets("MySheet").OLEObjects
    If InStr(1, c.Name, "[COLOR=#0000ff]Program[/COLOR]") > 0 Then
      c.Object.List = Array("(All)", "aa", "bbbb", "ccc")
    End If
  Next
End Sub

 

[grooviness]

Thanks to you both. It really isn't practical to change at this point since I have upwards of 30 versions of the same report, each with their little differences. I'll keep that in mind though if I ever have to re-create them. Hopefully our powers that be allow deletion of .exd files without admin rights.