[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

macro runs twice with called from ribbon

Posted on 2011-05-04
12
Medium Priority
?
1,778 Views
Last Modified: 2012-08-13
Hello,

I've added a custom ribbon to my xlsm file using the custom UI editer app (Custom UI editor app). For some reason when I click the button the ribbon it runs my macro but only the "msgbox" code fires and it fires twice followed by an odd error message that just say "400" no text.  Even if I comment out all my code expect one msgbox line I get the same results.

Has anyone seen this before? Below is the xml code to create the ribbon.

Thanks
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="customTab" label="Monthly" insertAfterMso="Developer">
                <group id="customGroup" label="Montly">
                    <button id="customButton1" label="Create Instructor Summary" size="large" onAction="modUtilities.CreateNewMonthly()"  />
                </group>

            </tab>
        </tabs>
    </ribbon>
</customUI>

Open in new window

0
Comment
Question by:eshurak
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35693140
Can you provide a copy of the sub:

modUtilities.CreateNewMonthly

Chris
0
 
LVL 3

Author Comment

by:eshurak
ID: 35693804
Hi Chris,

Even when I change the code to:

Sub CreateNewMonthly()
Debug.Print "test"
ActiveWorkbook.ActiveSheet.Range("A1") = Now()
End Sub

I get the same results, so I think the problem is in the xml.  I forgot to mentioned that it does not stop at breakpoints when run from the ribbon.  And to reiterate the code runs fine when run from the vb editor.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35711061
Not really familiar with using XML for Excel, but doesn't error 400 usually mean web page not found?
(Just a shot in the dark...)
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.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35711086
eshurak: Seems like Chris requested you for something :)

Could you please show us the code for "modUtilities.CreateNewMonthly()"

XML seems fine to me.

Sid
0
 
LVL 3

Author Comment

by:eshurak
ID: 35722900
Rspahitz, thanks there's not really any web pages involved, but it is XML so that might have caused that.  I've attached an image of the error as it is unique.

Sid, yes the code was requested but as I stated earlier even when I simplify the code in " modUtilities.CreateNewMonthly()" I get the same results.  Even if I comment out all code I still get the error message.

Sub CreateNewMonthly()
Debug.Print "test"
ActiveWorkbook.ActiveSheet.Range("A1") = Now()
End Sub


error.bmp
0
 
LVL 3

Author Comment

by:eshurak
ID: 35723465
Hi,

I've attached my workbook in case there's something that I forgot to mention.
Instructor-Summary-Report-Templa.xlsm
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35724372
Can you run it directly in Excel, without the XML interface?
If so, then the problem is in the XML communication somewhere (which is what I suspect.)
I.E. I don't think the problem is VBA but trying to establish a connection to the VBA.

0
 
LVL 3

Author Comment

by:eshurak
ID: 35724466
Rspahitz - Exactly.  Yes, when I run the code directly from vb editor it runs fine.  The problem only occurs when the code is called from the ribbon.  The problem is definitely in xml/ribbon.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35730011
You have two problems:

1. Remove the parentheses from the onAction property:
onAction="modUtilities.CreateNewMonthly"

2. Change the declaration of your sub:


Sub CreateNewMonthly(control As IRibbonControl)

Regards,
Rory
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35730050
PS Here's the amended file.
Instructor-Summary-Report-Templa.xlsm
0
 
LVL 3

Author Comment

by:eshurak
ID: 35730773
Roya, you rock.  I tried differenct varations of that but I never got it right.  Thank you!!
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question