Excel VBA assign onaction to shape

[Doug Studebaker]

I have created a tool that presents charet views based on which shape or combination of shapes is selected by user. Each shape is a toggle that turns a chart element on or off. The tool is replicated for multiple data sets. The shapes will not accept an onaction assignment. The problem is (apparently) that during development and now use, the workbook was copied and renamed over and over and the shapes' properties don't permit assignment or re-assignment of macros from the old workbook to new ones.

I have tried several approaches none of which has worked.

Approach 1:

With ActiveSheet.Shapes("Name.Func")
        .TextFrame.Characters.Caption = "Display"

        .OnAction = "Macro_Name"
End With

Approach 2:

ActiveSheet.Shapes.Range(Array("Group_Name")).Select
    ActiveSheet.Shapes.Range(Array("Name.Func")).Select
        Selection.OnAction = "'" & Client-ID Visual VerNum.xlsm  & "'!Show_Average"

Approach 3:

shpTemp.OnAction = "'" & "Client-ID Visual VerNum.xlsm" & "'!Show_Average"

 

[RoryA]

Welcome to the forum.

What exactly happens when you run those codes?

 

[Doug Studebaker]

Welcome to the forum.

What exactly happens when you run those codes?

I get the error message:
"Run-time error '1004':
Application-defined or object-defined error"

 

[RoryA]

Approach 2 and 3 shouldn't run or compile as you posted them, but the first one looks ok. Is the sheet protected?

 

[Doug Studebaker]

Approach 2 and 3 shouldn't run or compile as you posted them, but the first one looks ok. Is the sheet protected?

The sheet is not protected. It seems to be a problem with shape properties but I'm out of my depth. I tried to remove archaic assignments using the code below but I couldn't get it to work and fixing it is beyond my ken. (Please excuse the mixed metaphors, an indication of my frustration.)

Sub ShapeMacroLink_RemoveWorkbookRef()
'PURPOSE: Remove an external workbook reference from all shapes triggering macros: Source: www.TheSpreadsheetGuru.com


Dim shp As Shape
Dim MacroLink As String
Dim SplitLink As Variant
Dim NewLink As String


'Loop through each shape in worksheet
  For Each shp In ActiveSheet.Shapes
  
    'Grab current macro link (if available)
      MacroLink = shp.OnAction
    
    'Determine if shape was linking to a macro
      If MacroLink <> "" And InStr(MacroLink, "!") <> 0 Then
        'Split Macro Link at the exclaimation mark (store in Array)
          SplitLink = split(MacroLink, "!")
        
        'Pull text occurring after exclaimation mark
          NewLink = SplitLink(1)
        
        'Remove any straggling apostrophes from workbook name
            If Right(NewLink, 1) = "'" Then
              NewLink = Left(NewLink, Len(NewLink) - 1)
            End If
        
        'Apply New Link
          shp.OnAction = NewLink
      End If
  
  Next shp
   
End Sub

 

[RoryA]

When the error occurs, what is the actual value of NewLink?

 

[Doug Studebaker]

When the error occurs, what is the actual value of NewLink?

At the risk of showing my ineptitude, I don't understand your question. The shapes (colored coded rounded rectangles) are toggles which show or hide chart elements. When clicked they show an element (e.g. average) and the rectangle changes color and is assigned onaction hide element. Then when clicked again the element is hidden, the shape color changed and onaction is changed to show element. But onaction assignment causes an error.

 

[RoryA]

When you run the code, an error occurs. At that point, you should have an option to Debug. If you click that and hover the cursor over the word NewLink in the code, you should see a tooltip with the value of that variable in it.