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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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/
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top