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.
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.
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)
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.
Any ideas?
Is there something that happens after a .xlsm file is opened by excel that my vba macro is missing?
I appreciate any help!!!
Reference the control through the OLEObjects collection:
Workbooks(Template).Sheets(Sheet1).OLEObjects("ComboBox12").Object.ListIndex = Workbooks(OriginalFile).Sheets(Sheet1).OLEObjects("ComboBox12").Object.ListIndex