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:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Welcome to the forum.

What exactly happens when you run those codes?
 
Upvote 0
Approach 2 and 3 shouldn't run or compile as you posted them, but the first one looks ok. Is the sheet protected?
 
Upvote 0
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:
Upvote 0
When the error occurs, what is the actual value of NewLink?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top