VBA No Longer Enabled for XLA or XLAM

[Opv]

I recently (and reluctantly) upgraded from Office 2000 to Office 2007. the MyStuff.xla addin I had been using worked consistently and flawlessly until I upgraded to Office 2007. Afterward, my xla would load and show up in References but it stopped automatically creating the reference link for newly created workbooks. I noticed when I would go to a regular Module in my xla and attempt to run a simple subroutine, I would get an error message that VBA is not enabled on this project (referring to my xla file). I double checked my general Excel settings several times and VBA is enabled within the Excel application itself (I can run subs from other workbooks.)

I tried deleting the xla and recreating an xlam file and I got it to work immediately after creating the document and saving it as an xlam file. However, when I closed Excel and cranked the application back up, the MyStuff.xlam is loaded, but it does not show up in my list of References. I've manually added the reference, repeated the process of saving, closing and cranking Excel back up, but got the same results. I am also now getting the VBA not enabled error on the MyStuff.xlam file as well.

Is there anything obvious I am overlooking?

[snb]

Is any reference indicated as missing in the list of references in the VBEditor ?

[Opv]

There wasn't the first time I checked. Prior to reading your reply I had opened on of my workbooks which contained a link to the original xla file. When I went back in to check for a missing reference, now the original xla reference is shown as missing. I removed the link to the old xla file, saved and closed my workbook, then created a new, blank workbook and the missing reference is now gone. However, when I attempt to select my new xlam reference to link it to a workbook, I receive a popup that says the requested type library is not a vba project.

Thanks

[Opv]

I have no clue how I ended up with two replies. I don't seem to have a way to delete the duplicate entry. Sorry about that.

[Opv]

I don't know what I did that resolved the situation but it appears to now be working. I went through numerous instances of turning off all macros, to enabling all macros, to trusting access to the VBA project object module. I tried a variety of combinations. i was by the fact that when all macros are disabled I could still open a new workbook and create/run macros, yet when I enabled all macros and they loaded my xlam file, macros would run from a newly created workbook but not from my xlam file. I finally read some obscure note to check my trusted places. When I checked that list, i noted that it did not include the "C:\users\user\appdata\roaming\microsoft\addins" folder. I added it and was finally able to load my addin and actually run code from it. I guess I should be satisfied with that; however, it is frustrating not being able to understand why it is working now and wasn't working earlier.

[Paul_Hossler]

When I checked that list, i noted that it did not include the "C:\users\user\appdata\roaming\microsoft\addins" folder. I added it and was finally able to load my addin and actually run code from it. I guess I should be satisfied with that; however, it is frustrating not being able to understand why it is working now and wasn't working earlier.


Probably because Excel didn't trust you until you made that a Trusted Location. It might be because you upgraded from 2000 to 2007 and things were a lot less restricted back then

[Opv]

Thanks.