Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1678
  • Last Modified:

Error 91 when invalidating control for Office 2007 customized ribbon

I have a small excel add-in that I wrote with a Dynamic Menu.  The idea is to create a 'permanent' set of favorites that people can set on their individual machines.  

The available selections on the menu are ...
  - Add spreadsheet to Favorites
 - Manage Favorites
 - or select one of many spreadsheets that have already been added as favorites, which will then open that spreadsheet.

When you Add a spreadsheet to favorites, it populates a worksheet in the addin with the Short Description of the spreadsheet and the full path and filename of the spreadsheet.    The last thing it does is invalidates the dynamic menu control so that when the control is selected, it will be re-loaded, the new spreadsheet will appear in the list.  

The good news is that this add-in works on my machine.  The bad news is that it does not work on my co-worker's machine.  I am getting a run-time error '91' .. 'Object variable or With block variable not set'.  I have attached screenshots below.

Please let me know if you need more info.

Thanks,

sdwalker
clip-image001.jpg
clip-image002.jpg
clip-image003.jpg
0
sdwalker
Asked:
sdwalker
  • 7
  • 6
2 Solutions
 
Rory ArchibaldCommented:
How and where do you initialize the rxIRibbonUI variable?
0
 
sdwalkerAuthor Commented:
Thanks for the question.  

rxIRibbonUI is initialized per image below.

sdwalker
ee1.jpg
0
 
sdwalkerAuthor Commented:
That is .. it's just dimensioned as a Public Variable in one of the modules.

sdwalker
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rory ArchibaldCommented:
That doesn't show me how it is initialised, only how it is declared. I need to see the code that actually assigns an IRibbonUI object to the variable.
Given that it's a public variable, it's possible that an error has cleared its value when your other user is testing it.
0
 
sdwalkerAuthor Commented:
Sorry about that.  Here is the initialization and I've included also included the xlam for your perusal.  You will need to remove the .jpg from the end of the file.

Thanks,

sdwalker
ee2.jpg
xlFavorites.zip
0
 
Rory ArchibaldCommented:
Sorry - I didn't seem to get a notification of your last response. I've had a look and the add-in works fine for me, and I can't see anything wrong with the code.
Does it always break at the same place for your coworker and does he/she have any other add-ins loaded? Also, if you check the option to display add-in user interface errors on his machine, do you get any additional errors?
0
 
sdwalkerAuthor Commented:
Sorry rorya, we've upgraded to Office 2007 at work now and I've been supporting our production spreadsheets.  I finally got installed on 3 co-workers machines.  Two of them break at the exact same spot, while the 3rd one works, albeit is very clunky.  You have to actually close Excel and re-open it to see the Favorites that you've added.  

It is not this way on my machine at all.  

Any ideas?

Thanks,

sdwalker
0
 
Joanne M. OrzechManager, Document Services CenterCommented:
I doubt if I can help you much, but I did find this article which states, in part:

The object is a valid object, but it wasn't set because the object library
in which it is described hasn't been selected in the References dialog box.
Select the object library in the Add References dialog box.

http://www.daniweb.com/forums/thread51526.html
0
 
Rory ArchibaldCommented:
To be honest, I'm baffled at the moment. I tried it on two machines at home and it worked perfectly on both. Only thing I can think of is that both my machines were clean installs, not upgrades. What OS are your machines running?
Rory
0
 
sdwalkerAuthor Commented:
Thanks for both of your inputs.  I believe I finally figured out the issue(s).

1 - I created three separate ribbons via three separate addins and I named them all rxIRibbonUI.  I think that was the major issue and was causing confusion to Excel when I would try to invalidate the control in the Favorites addin.  

2 - I was rebuilding the listbox in the Initiate event instead of the Activate event, which caused me to sometimes miss new spreadsheets that I had added.  

3 - (I'm not sure if this was really a problem or not), one of the subroutines in which I was invalidating the control was a Private subroutine instead of Public.  In hindsight, I don't think this was a problem.

The underlying issue was that if any event triggered an error, most of the subsequent processing stopped working because the public variable rxIRibbonUI (renamed rxMyFaveRibbonUI) was no longer instantiated (it was empty).  

All that having been said, I am tempted to accept rorya's answer as the solution because of the effort he placed into it, but I don't know what the proper etiquette is.  I'm open to feedback.  Should I ...
(a) accept rorya's answer
(b) accept my own answer
(c) delete the question

I don't think (c) is right because this might be helpful to someone in the future.  I am leaning toward multiple solutions between me and rorya.

Thoughts?

Thanks!!

sdwalker
0
 
sdwalkerAuthor Commented:
Also, rorya, if you think the completed addin would be useful to you, I'll send it to you.  Just tell me the best way to do it.

sdwalker
0
 
Rory ArchibaldCommented:
I'm not sure I added much, if anything, to the solution, so I don't want any points, but I'd agree it's worth keeping in the knowledgebase. I'm fine with you accepting your own answer, though I would like to see the finished product if that's OK with you! :)
Rory
0
 
sdwalkerAuthor Commented:
Certainly, rory.  I actually attempted to give you 100 points for your efforts in helping me, but it wouldn't let me do that but still accept my own solution.  I have emailed the final product to you.  Please let me know if you don't receive it soon.

Thanks again!

sdwalker
0
 
Rory ArchibaldCommented:
Got it, thanks. Will take a look when I get a chance!
Rory
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now