2

I'm trying to create my first add-in with Custom UI based on this guidance. (As I don't have Visual Studio I'm working with VBA; Excel 2013 in Windows 7)

For testing I've just created one sub in a module:

Sub test()
    MsgBox "test"
End Sub

As described in the guidance, I've created CustomUI folder with CustomUI.xml in it. CustomUI.xml's content: 

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="TestTab" label="Test tab" >
        <group id="TestGroup" label="Test group" >
          <button id="test" visible="true" size="large" 
                  label="Test Sub" 
                  onAction="test" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Also added
https://msdn.microsoft.com/en-us/library/office/gg597509(v=office.14).aspx
to .rels as described.

Now I've the add-in loading correctly, the only issue that clicking on the newly created ribbon item I'm keep getting below error message (of course the macro works correctly when I start it from VBA editor):
enter image description here

What I'm doing wrong?

Community
  • 1
  • 1
Máté Juhász
  • 2,197
  • 1
  • 19
  • 40

2 Answers2

2

Once you have incorporated Public Sub test(control As IRibbonControl) as suggetsed Here and if you are still getting the error then I believe that there is some other code in one of the open Workbooks/Add-In which is interfering with this code.

  1. Close all other open workbooks
  2. Check for any other installed Add-Ins.
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
1

Add ' control As IRibbonControl ' as argument to your subroutine.

For example if the name of your subroutine is ' test ', then

Sub test(control As IRibbonControl)
    'your code goes here
End Sub
Arun Thomas
  • 805
  • 1
  • 12
  • 21
  • thanks for the suggestion. I've tried it, but still has the same issue:( (I've restarted excel after edit if that matters) – Máté Juhász Aug 03 '16 at 20:47
  • 1
    this could be because you are having your code in a private module [ eg : ThisWorkbook or Sheet1 ] Try inserting a new module and insert the code there. This will solve the issue. When i inserted the code in ' ThisWorkbook ' module it gave me the error but not when put in a new module (public module; say 'Module1') – Arun Thomas Aug 03 '16 at 21:36
  • It was already in a "module", the issue was having also another workbook with a sub called "test". Thanks again for your effort! – Máté Juhász Aug 04 '16 at 03:58