VBA Run-time error ‘91’: Object variable or With block variable not set

Where is wbkTT declared/assigned a value...?

Hi Norie,

If I change it to ThisWorkbook to causes the same error a few lines of code down at the next ActiveWorkbook. If I then change that to "ThisWorkbook" it causes a run-time error 9 - "subscript out of range".

Try removing the file extension from the name when you set the Const (drop the .xlsm)

Hi soberguy, thanks for the response! I just tried it and get the same error

This may just be my ignorance of VBA but I'm wondering if the new workbook generated by...

Please Login or Register  to view this content.

...needs to be referenced/named newTTwbk. Does Excel automatically relate the freshly opened workbook at newTTwbk?

I so far have

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Based on those 3 lines, if Excel doesn't automatically relate the new workbook to newTTwbk then all references to newTTwbk are superfluous, no?

I don't see the point of having one string equal another string. Why not use the same variable? In other words, what's the point of:

Please Login or Register  to view this content.

As to the issue at hand, I'm not really sure what the issue is. With an ActiveWorkbook, the name is returned without the file extension (hence my previous post).

Upon testing the Workbook_Open event, it does expect the file extension to be included with the name.


Walk me through how you're calling this. Does this code just run when you open this particular workbook? Are you opening this workbook from code in another workbook?

Interesting. I can return it with and without the file extension. I'll have to play with that.

Thanks for the help guys, here's the story...

I didn't build or write this program, the person who did is long gone, and I'm trying to wade through it and make it function without errors again.

When I open the Excel file the code listed in my original post runs automatically and is supposed to activate the macros in the RibbonXAddin to add functionality to the program. However, it's getting hung up with the " Run-time error ‘91’: Object variable or With block variable not set ". The Debugger box opens up and if I select "END" the spreadsheet opens but the macros are not activated. If I choose to "DEBUG" the debugger console opens up and highlights the line of code referenced above.

From this point I can select "RUN" and "CONTINUE" or punch F8 through the rest of the code and it steps over the ERROR 91 and walks through the rest of the code without problem and activates the program. I can then use the program as built without problems.

So the real problem is getting hung up when I start the program and that's what I'm mandated to fix because the majority of people who will be using the program will destroy it if they are allowed free access to the debugger console.



I am not sure where or how the newTTwbk = wbkTT line comes into effect and yes it seems like a circular reference.

Norie, the program consists of the 2 files listed in the top of the code.

Please Login or Register  to view this content.

ActiveWorkbook is the .xlsm, opening the .xlsm file automatically runs the .xlam file which contains all the macros that turns it into more than just a spreadsheet.

The code I posted above is in the .xlam file.

The code with the error is listed under TTV2.xlam > Microsoft Excel Objects > ThisWorkbook

It is meant to activate a number of macros that compile data into prebuilt forms

The .xlsm file also has a ThisWorkbook module listed under Microsoft Excel Objects