Excel VBA assign onaction to shape

Doug Studebaker

New Member
Joined
Mar 12, 2019
Messages
4
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:
Code:
With ActiveSheet.Shapes("Name.Func")
        .TextFrame.Characters.Caption = "Display"

        .OnAction = "Macro_Name"
End With

Approach 2:
Code:
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:
Code:
shpTemp.OnAction = "'" & "Client-ID Visual VerNum.xlsm" & "'!Show_Average"
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum.

What exactly happens when you run those codes?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 12, 2019
Messages
4

ADVERTISEMENT

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.)
Rich (BB code):
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
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
When the error occurs, what is the actual value of NewLink?
 

Doug Studebaker

New Member
Joined
Mar 12, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,487
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,989
Members
409,553
Latest member
alscno

This Week's Hot Topics

Top