Vba, Programatically assign a macro to a "Shape" inside shapegroup-CodePudding

Thanks in advance, not sure why this wouldn't work.

I want to assign a macro to each button inside a shape group on load.

Inside Module:

Private Const SideNavName As String = "SideNav"


Public Sub SetSideNavigationOnAllSheets()

    Dim ws As Worksheet
    Dim oShape As Shape
    
    For Each ws In ActiveWorkbook.Sheets
        'check to see if sidenav shape/group exists in sheet
        If Common.ShapeExists(ws, SideNavName) Then
        
            ' get side nav
            For Each oShape In ws.Shapes(SideNavName).GroupItems
                ' only need the nav buttons not container
                If Left(oShape.Name, 3) = "Nav" Then
                    Debug.Print ws.Name, oShape.Name
                    oShape.TextFrame.Characters.Text = "btn 1" ' pull from DB
                    oShape.OnAction = "'" & ActiveWorkbook.Name & "'!FolderSelectorButton"  '  ERRORS OUT HERE
                End If
                '
            Next
        End If
        
    Next
End Sub

Public Sub FolderSelectorButton()
    Debug.Print 1
End Sub

CodePudding user response：

Seems VBA doesn't like setting the OnAction property for Shapes that have been grouped. Solution is to store details of the group, ungroup it, update the OnAction property then re-create the group.

Replace your two lines setting the TextFrame and OnAction of the oShape object with the following:

' save then ungroup the Shapes
Dim oShpGrp As Shape, sShapeNames() As String, i As Long
Set oShpGrp = ws.Shapes(SideNavName)
ReDim sShapeNames(1 To oShpGrp.GroupItems.Count)
For i = 1 To oShpGrp.GroupItems.Count
    sShapeNames(i) = oShpGrp.GroupItems.Item(i).Name
Next i
oShpGrp.Ungroup

' update Shape
oShape.TextFrame.Characters.Text = "btn 1" ' pull from DB
oShape.OnAction = "'" & ActiveWorkbook.Name & "'!FolderSelectorButton"  '  ERRORS OUT HERE

' re-group the Shapes
Set oShpGrp = oShpGrp.Parent.Shapes.Range(sShapeNames).Group
oShpGrp.Name = SideNavName

This assumes that the group is a single-level group (ie it is not a group embedded within another group)