Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    533

    Unanswered: CommandBars OnAction Property Fails

    I have a sub procedure that creats a custom menubar with sub menu items on file open. I use this to provide a custom menu for several users. It works great and I have tested it on several computers. I tried it on another users computer today and get this error:

    Run-time error '-2147467259 (80004005)':
    Method 'OnAction' of object'_CommandBarButton' failed

    This is the code where the fail occurs:
    Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
    SubMenuItem.Caption = Caption
    SubMenuItem.OnAction = PositionOrMacro ''''Fails on this line''''


    The Menu script is based on the menuMaker script form j-walk.com Creating Custom Menus

    I already did an extensive web search to look for the possible cause but did not find an answer I can pin to this problem. I believe it is something with the users Excel application setup. Maybe a conflict, missing reference, or very obscure setting that is causing the error. Not a code problem because it works on everyother computer I've installed it on.

    If you have any ideas please post.
    Attached Thumbnails Attached Thumbnails menuError.jpg  
    Last edited by savbill; 12-20-05 at 22:06.
    ~

    Bill

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, Bill.

    As I noted in my PM, I wonder if there is a Reference Library that is missing. It would require comparing the Reference Libraries of a computer that works with one that does not. The line with "mso..." makes me think this might be a problem.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Thanks for the suggestion Shades. This is something I'll check when I can get some time on this PC. I was also thinking there could be a add-in loading that could be effecting it.
    ~

    Bill

  4. #4
    Join Date
    Feb 2004
    Posts
    533

    Lightbulb ANSWER: All VB Coders need to know this

    I found the answer to this problem after much testing and a long search for an answer. I found several examples of people having this problem and lots of misleading suggestions that did not provide the answer to the real cause of the problem.

    To Recap the problem was VB code would execute without a problem on some computers and would not work on others. When a code file was opened and the macro ran to load custom menus it failed at the OnAction Property of the CommandBars setting where the code would assign an action to a Menu item. This produced a Run-Time error with this error message.

    Run-time error '-2147467259 (80004005)':
    Method 'OnAction' of object '_commandBarButton' failed


    The resolution to this error is incredibly simple, well at least once I discovered the answer. It is almost unbelievable that the answer is not better documented on MSDN and in more forums dealing with Excel VB.

    Now to get to the answer, this was a program used by several users in our office on different PCs. When the program was opened the first time it required the user to click the Macro Warning message to "Allow" Macros to run. Once setup the program was to start automatically when excel started by writing a shortcut in the Excel Startup folder. However many users were still going to the file on the server and opening then clicking "Disable" on the Macro Warning Message Dialog.

    This is the cause of the problem and the key to the Answer. When the "Disable Macros" is selected on the Macro Warning message it adds the file name to the Excel "List of Disabled Components" Making a copy of the file or opening it from a different source would permit the program to work as it was not identified as the same component, but the file name would still be retained in the list of disabled components. The file must be cleared from the List of Disabled components in order for it to run.

    To access the list of disabled components you first have to find it and it is not in an obvious location. Open the "Help" Menu then select "About Microsoft Excel". On the About Microsoft Excel Dialog in the lower right corner is a button "Disabled Items..." Clicking this button opens the "Disabled Items" List. If you see your VB Code file name in this list, it is registered as a Disabled Item and some CB commands may not execute. Select the name of your file and click the "Enable" button. This will remove the item from the Disabled Items list and allow it to function as designed on the users PC.

    After removing the file from the disabled items list, you must educate your users on opening and running your code files. At least the next time they report having this problem you will know what to check.
    Attached Thumbnails Attached Thumbnails ExcelError.jpg  
    ~

    Bill

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Who would have thought? Thanks, Bill. Once it is revealed, it is "simple".
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •