shapes.onaction in add-in using reference path to macro (Excel VBA)

[MrIfOnly]

Office365
Windows 11

I have a VBA program running as an Excel Add-in which deposits multiple shapes onto the user's worksheet. These shapes (for example, the two shown in the picture below labeled "Quality Check" and Undo Sort) use the .onaction property to run a macro that is located in a standard module in the add-in. Absolutely no code is stored in the user's workbook.

The shapes code is this:

    If shapeexists("QLTYCHK") = False Then
        With ActiveSheet.Shapes.ADDSHAPE(msoShapeRoundedRectangle, Range("K33").Left, Range("K33").Top, 150, 60)
            .Name = "QLTYCHK"
            .OnAction = "QualityCheck"
            .Fill.ForeColor.RGB = RGB(305, 415, 0)
            .Fill.BackColor.RGB = RGB(155, 285, 0)
            .Fill.TwoColorGradient msoGradientHorizontal, 1
            .Fill.Transparency = 0
            With .TextFrame
                .MarginBottom = 3
                .MarginLeft = 10
                .MarginRight = 10
                .MarginTop = 3
                .HorizontalAlignment = xlCenter
                .Characters.Font.Color = vbBlue
                .Characters.Font.Size = 19
                .Characters.Font.Bold = True
                .Characters.Text = "QUALITY CHECK"
            End With
        End With
    End If

This works just fine.
The problem arose from a user sending me a sheet with these shapes which work for him, but when I click on them I get an error message that reads "Sorry, Excel can't open two workbooks with the same name at the same time." followed by another message that reads: "Cannot run the macro "C:\Users\thchi\AppData\Roaming\Microsoft\AddIns\NozzleScheduleAutomation.xlam'!QualityCheck'. The macro may not be available in this workbook or all macros may be disabled."
The issue is that, when the onaction property is set to the shape, it is using the absolute path of the Add-in on the user's PC. So I tried to make this a relative path by trying:

.OnAction = "'%APPDATA%\Roaming\Microsoft\AddIns\NozzleScheduleAutomation.xlam'!QualityCheck"

and even trying to assign "Environ('APPDATA')" to a string variable and tacking it onto the .onaction code, to no avail.

You may have noticed that the above worksheet is opened from an Autocad drawing as an OLE, however my problem is repeatable on plain worksheets not attached to anything. Also, in order to test any variation to the code, I have to create the worksheet with the shapes on one PC and try to run them on a different PC with a different username.

If anybody has any ideas about how to make this work so that the program will run regardless of the PC it is run from, I would be eternally grateful.

TIA,

CJ

 

[RoryA]

Is there a reason for putting shapes on the worksheet rather than using a custom ribbon tab/group created by the add-in? It would avoid this issue.

 

[jkpieterse]

You could add an application event class that responds to opening of workbooks, which then goes through the shapes of the new workbook and updates their OnAction setting to the current add-in incarnation. IIRC you could use the workbook links for this

 

[MrIfOnly]

Is there a reason for putting shapes on the worksheet rather than using a custom ribbon tab/group created by the add-in? It would avoid this issue.

Thanks for the suggestion, Rory. It would avoid the issue, however, I have 9 icons on a custom ribbon tab already and I used the shapes to present certain options if only certain conditions are met. Once the shapes run their macros, they are removed and the are reintroduced only if those conditions are met again. I might consider adding more icons to the ribbon if I could disable/enable them as needed via VBA. I have not looked into that possibility yet.

 

[MrIfOnly]

You could add an application event class that responds to opening of workbooks, which then goes through the shapes of the new workbook and updates their OnAction setting to the current add-in incarnation. IIRC you could use the workbook links for this

This might be the best option. I will try something along these lines tomorrow when my eyes can focus on the monitors again. Thanks Jan!

 

[RoryA]

You can show/hide or enable/disable controls on the ribbon using callbacks.

 

[jkpieterse]

You would use the getEnabled and/or getVisible callbacks to achieve that.

 

[MrIfOnly]

Thank you Rory and Jan for the help. After doing some research into callbacks (this page written by John at The VBA Help was especially helpful) I am certain that you have pointed me down the road I need to follow.

 

[jkpieterse]

There's loads of examples by Ron de Bruin on my website: Ribbon and Quick Access Toolbar pages