访问ComboBox列表索引，运行时错误“438”，xls vs xlsm?

I have a code that takes values from one excel sheet, and puts them into the same spot in another sheet. This includes ComboBox indexes as well as Cell values. Both spreadsheets have the same structure and the same form elements, but one sheet has a different image as a header.

我有一个代码，它从一个excel表中获取值，并将它们放在另一个表中的相同位置。这包括ComboBox索引和单元格值。这两个电子表格都有相同的结构和相同的表单元素，但是一个表格作为一个标题有不同的图像。

Here's the line that error's out:

这里有一条线，误差是:

    Workbooks(Template).Sheets(Sheet1).ComboBox12.ListIndex = Workbooks(OriginalFile).Sheets(Sheet1).ComboBox12.ListIndex

Template and OrignalFile are filenames, Sheet1 is a worksheet tab name, and ComboBox12 exists as "ComboBox12" in both files.

模板和OrignalFile是文件名，Sheet1是工作表选项卡名称，ComboBox12在两个文件中都以“ComboBox12”的形式存在。

So here's the problem. When I run the script that first opens the two files and then runs a bunch of these commands, it errors out with

这就是问题所在。当我运行脚本时，首先打开两个文件，然后运行一堆这些命令，它会出错。

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

But if I run the line of code while both files are already open, it runs just fine.

但是，如果我在两个文件都已打开的情况下运行这一行代码，它就可以正常运行。

To make things even more interesting, running the code on 97-2003 .xls files works perfectly. And it seems it doesn't make a difference what version the template file is. Only when the original file is a .xlsm version does the code error out. (I can still run after they're both open from VBA window)

为了让事情更有趣，在97-2003中运行代码。xls文件运行得非常好。模板文件的版本似乎没有什么区别。只有当原始文件是.xlsm版本时，代码才会出错。(我仍然可以在它们都从VBA窗口打开后运行)

To prove it was the file version causing the change in behavior I took a .xls file that the code worked fine on and saved it as a .xlsm and re-ran the code with the new filename and it errored out.

为了证明是文件版本导致了行为的改变，我取了一个.xls文件，该文件的代码运行良好，并将其保存为.xlsm，并使用新的文件名重新运行代码，结果出现了错误。

Any ideas?

什么好主意吗?

Is there something that happens after a .xlsm file is opened by excel that my vba macro is missing?

在使用excel打开.xlsm文件后，是否出现了丢失vba宏的情况?

I appreciate any help!!!

我很感谢任何帮助! ! !

1 个解决方案

#1

Reference the control through the OLEObjects collection:

通过OLEObjects集合引用控件:

Workbooks(Template).Sheets(Sheet1).OLEObjects("ComboBox12").Object.ListIndex = Workbooks(OriginalFile).Sheets(Sheet1).OLEObjects("ComboBox12").Object.ListIndex