Excel Error when using button made from Custom UI Editor- Cannot run the macro "***". The-CodePudding

I built a series of macros, all of which have worked with the custom ribbon I built using the Custom UI Editor.
I added one more macro and updated my Ribbon with a new button. I test the macro first and it works when run from the developer tab or just running directly from VBA and the sub heading is as follows: "Sub GetDES()".

Once I make it so it works with the button on the ribbon by changing to:

Sub GetDES(control as IRibboncontrol)

I get the "Cannot run macro..." error

    Sub GetDES(control as IRibboncontrol)
    
    'PURPOSE: Get description page and insert into each confirm page
    Dim Fpath As String
    Fpath = ActiveSheet.Range("I6").Value
    With Application.FileDialog(msoFileDialogFilePicker)
            .ButtonName = "Add this DES!"
            .AllowMultiSelect = False
            .InitialFileName = Fpath
            .Filters.Add "All Pictures", "*.*"
            .Show
            If .Show = -1 Then
                Dim img As Object
                Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))
                    img.Left = 75
                    img.Top = shOutput.Range("B40").Top
                    img.Width = 450
            Else
                MsgBox ("Cancelled.")
            End If
    End With
    End Sub

The only thing I can think of is I still have something weird happening when I run it without the Control. When I run it the dialouge picker opens twice. I'm wondering if that is my problem with running it from the button on the ribbon.

When I step through the code, the line

set img=Activesheet.Picture.Insert(.SelectedItems(1))

is what causes the file dialogue picker to open again.

Ideas? Thank you

CodePudding user response：

To make sure the callback is invoked by the Office you may try to assign a sample sub to a button, for example, with a simple MsgBox statement to make sure it works correctly.

Sub GetDES(control as IRibbonControl)
 MsgBox "test"
End Sub

If that works correctly you can try putting your original code. But you may try to avoid shorthand properties like ActiveSheet or With operator. Hope it helps.

CodePudding user response：

I believe I stumbled across my issue. I built a new module that I wrote this code in, I named the module with the same name as the sub. When Testing, I never went to run it from the macro list, I just ran it from the VBA window. When I looked at the Macro's List window, I saw that the new macro I wrote in this workbook was not displaying like the other macros. As you see in the photo, "Confirm Tool-Master_V5.xlsm'!GetDES.GetDES"

I removed that module and took the copy, listed as GetDES3 and renamed it

GetDES(control as IRibboncontrol)

and it runs from my ribbon. I'm not sure why it looked different or if there was something to do with the module name and the sub being named the same that caused it to be like that. so I tested it. I wrote a test sub and named the module test and it did the same thing.

So, Lesson is, don't name your sub and the module with the same name