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

grooviness

New Member
Joined
Jul 19, 2017
Messages
3
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.

Rich (BB code):
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,034
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

New Member
Joined
Jul 19, 2017
Messages
3
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,034
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,868
Office Version
2007
Platform
Windows
Only to rule out the problem of the name of the activex control.
Try the following:

Code:
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

New Member
Joined
Jul 19, 2017
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,805
Messages
5,470,888
Members
406,733
Latest member
darzu

This Week's Hot Topics

Top