0

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

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Erik Otterholt
  • 65
  • 2
  • 10

2 Answers2

2

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.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
  • When I tried to test the callback, with your suggestion, it failed also. That was a good suggestion, I'm still trying to figure out where to go from here. I actually in the UI Editor generated callbacks and copied it directly. I ususally never use active sheet, however in this scenario, I have previously run a macro that creates new worksheets. These created worksheets I need to add an image in once I've pdf'd the pages I delete these newly created worksheets. This is the only time in all of the sub's in this workbook where I use the activesheet property. – Erik Otterholt Jan 30 '23 at 22:25
  • It sounds like Excel can't find the callback or the VBA macro is disabled in the Trust Center. – Eugene Astafiev Jan 30 '23 at 22:42
  • Eugene, Can one macro be disabled while all other macros work? I've not seen that. – Erik Otterholt Jan 31 '23 at 17:08
  • Nope, if other VBA scripts can be run that is not the case. Then make sure the sub is reached by the Office. Do you put the callback to the default VBA module? – Eugene Astafiev Jan 31 '23 at 17:23
  • Thank you Eugene, I believe I found the answer. Thank you so much for taking the time to help. Really appriciate all the people here like you who try to help people! People like me benefit from this community. Cheers!!! – Erik Otterholt Jan 31 '23 at 17:31
  • So, I was on the right avenue, the sub couldn't be found by the Office application, correct? – Eugene Astafiev Jan 31 '23 at 17:38
0

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"
enter image description here

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.

enter image description here

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

Erik Otterholt
  • 65
  • 2
  • 10