adding macro in ribbon via customUIEditor

[Axelle]

Dear Mr. Excel,

I made a customized ribbon in excel, where a number of standard buttons have been added for my dashboard.
Now I have written a macro that I also want to add in the customized ribbon.

In CustomUIEditor I added:

                <!-- BUTTON: Macro-->
                <group 
                    id="Macro" 
                    label="Save as offer">
                    <button 
                        id="CopySave" 
                        label="CopySave" 
                        imageMso="AcceptInvitation" 
                        size="large" 
                        onAction="ModCopySave" 
                        enabled="true"
                        screentip="Start macro"/>
                </group>

In de module for the ribbon I added:

'Callback for CopySave onAction
Sub ModCopySave(control As IRibbonControl)
    Dim wb As Workbook

    Set wb = Workbooks.Add
    ThisWorkbook.Activate
    ActiveSheet.Copy Before:=wb.Sheets(1)
    wb.Activate
    wb.SaveAs "file:///C:\Users\admin\Documents\Vedrova%20Black%20belt%20Toepassingen\Offer.xlsx"

End Sub

Now, If I click on this button in the ribbon, I get the error: "cannot run the macro the macro may not be available in this workbook"

What do I do wrong?

Many thanks in advance for your help.

 

[RoryA]

Welcome to the forum.

Which module of the workbook is the ModCopySave routine in?

 

[Axelle]

Hi, Thanks for your quick reply.
This module has not been added to a workbook (is this different from a worksheet?)
I assumed that this macro (behind the button in the customized ribbon) would take the active sheet each time to save it, as a new Excel file.
I also added this macro to the Quick Access Toolbar and it does work there .. so a new file is created and saved from the current active sheet.

 

[RoryA]

Where is the macro then? It needs to be in the workbook whose Customui you are editing.

 

[Axelle]

Hi,

I wanted to send you a printscreen, but apparently that did not work out...

I added 2 modules.
- 1 module is the macro: ModCopySave (to save the active sheet in a seperate excel file).
- And module 2 is for the customized Ribbon: ModCustRib.

In (module2) ModCustRib I have added:

'Callback for CopySave onAction
Sub ModCopySave(control As IRibbonControl)
Dim wb As Workbook

Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy Before:=wb.Sheets(1)
wb.Activate
wb.SaveAs "file:///C:\Users\admin\Documents\Vedrova%20Black%20belt%20Toepassingen\Offer.xlsx"

End Sub

This is the same as in (module 1) ModCopySave.

First I wrote:

Sub ModCopySave(control As IRibbonControl)
Application.Run "ModCopySace"
End SubBut that didn't work either. So then I wrote the entire macro from module 1 (ModCopySave) in module 2 (ModCustRib).

 

[RoryA]

You should have one module and do not call it ModCopySave. In that module, all you need is:

'Callback for CopySave onAction
Sub ModCopySave(control As IRibbonControl)
Dim wb As Workbook

Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy Before:=wb.Sheets(1)
wb.Activate
wb.SaveAs "file:///C:\Users\admin\Documents\Vedrova%20Black%20belt%20Toepassingen\Offer.xlsx"

End Sub

Make sure you do not have any other routines called ModCopySave in the workbook.

 

[Axelle]

Thank you so much!

 

[RoryA]

Glad to help.