Copying and Pasting A Range of Cells Containing Grouped Shapes With Assigned Macros

[Ark68]

I have reached a complicated point in my project, so complicated that not only do I know where to begin, but I fear I won't be able to explain it throughly, so my apologies. I hope someone is willing to patiently guide me. I have provided access to a sample workbook for reference (without code).

With a range of cells on my worksheet (T9:AT9), I have 6 grouped shapes - a rounded rectangle (srv_btn) with a textbox (srv_tb) superimposed over it - that act as interactive buttons. Lets refer to this range as the "working range". These groups (buttons) are named in sequential order as "btn_serv_#" where number is unique between 1 and six. In my worksheet initiation code, using a loop of 1-6, each button is formatted to it's default state, and it's previously assigned macro is stripped.

Sub reset_svr_buttons()
    With ws_gui
        For sbtn = 1 To 7
            ssbtn = "btn_srv_" & sbtn
            Set shp = .Shapes(ssbtn)
            With shp
                .OnAction = "" 'removes macro assignments
            End With
        Next sbtn
        'change rectange line colour
        For sbtn = 1 To 7
            'Stop
            ssbtn = "srv_btn_" & sbtn
            Set shp = .Shapes(ssbtn)
            With shp
                .Fill.ForeColor.RGB = vbWhite
                .Line.Weight = 0.25
                .Line.ForeColor.RGB = RGB(209, 239, 250)
            End With
        'change font colour
            .Shapes("srv_tb_" & sbtn).TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(209, 239, 250)
        Next sbtn
    End With
End Sub

Within my code, the user can click on a button, and code will then format it to it's "selected" state, and the appropriate macro for the button is assigned. The formatting and macro assignment is done with a common procedure based on variables carried over from unique button click code.

Sub btn_pl()
    'Stop
    Dim srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean
    srvtext = "PL"
    serv_btn = "srv_btn_1"
    If ws_gui.Shapes(serv_btn).Fill.ForeColor = vbWhite Then
        ui1 = MsgBox("Was this just spot plowing?", vbQuestion + vbYesNo)
        If ui1 = vbNo Then
            srvsel = "PL "
        Else
            srvsel = "[PL] "
        End If
    Else
        sp = InStr(serv_str, srvtext)
        sp = Mid(serv_str, sp - 1, 1)
        If sp = "[" Then
            srvsel = "[PL] "
        Else
            srvsel = "PL "
        End If
    End If
    btn_svcformat srvtext, srvsel, serv_btn, ultrigger
    
End Sub
Sub btn_bl()
    'Stop
    Dim srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean
    srvtext = "BL"
    serv_btn = "srv_btn_2"
    If ws_gui.Shapes(serv_btn).Fill.ForeColor = vbWhite Then
        ui1 = MsgBox("Was this just spot blowing?", vbQuestion + vbYesNo)
        If ui1 = vbNo Then
            srvsel = "BL "
        Else
            srvsel = "[BL] "
        End If
    Else
        sp = InStr(serv_str, srvtext)
        sp = Mid(serv_str, sp - 1, 1)
        If sp = "[" Then
            srvsel = "[BL] "
        Else
            srvsel = "BL "
        End If
    End If
    btn_svcformat srvtext, srvsel, serv_btn, ultrigger
End Sub

Sub btn_wd()
    'Stop
    Dim srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean
    srvtext = "WD"
    serv_btn = "srv_btn_3"
    If ws_gui.Shapes(serv_btn).Fill.ForeColor = vbWhite Then
        ui1 = MsgBox("Was this just spot widening?", vbQuestion + vbYesNo)
        If ui1 = vbNo Then
            srvsel = "WD "
        Else
            srvsel = "[WD] "
        End If
    Else
        sp = InStr(serv_str, srvtext)
        sp = Mid(serv_str, sp - 1, 1)
        If sp = "[" Then
            srvsel = "[WD] "
        Else
            srvsel = "WD "
        End If
    End If
    btn_svcformat srvtext, srvsel, serv_btn, ultrigger
End Sub
Sub btn_st()
    'Stop
    Dim srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean
    srvtext = "ST"
    serv_btn = "srv_btn_4"
    If ws_gui.Shapes(serv_btn).Fill.ForeColor = vbWhite Then
        ui1 = MsgBox("Was this just spot salting?", vbQuestion + vbYesNo)
        If ui1 = vbNo Then
            srvsel = "ST "
        Else
            srvsel = "[ST] "
        End If
    Else
        sp = InStr(serv_str, srvtext)
        sp = Mid(serv_str, sp - 1, 1)
        If sp = "[" Then
            srvsel = "[ST] "
        Else
            srvsel = "ST "
        End If
    End If
    btn_svcformat srvtext, srvsel, serv_btn, ultrigger
End Sub
Sub btn_sd()
    'Stop
    Dim srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean
    srvtext = "SD"
    serv_btn = "srv_btn_5"
    If ws_gui.Shapes(serv_btn).Fill.ForeColor = vbWhite Then
        ui1 = MsgBox("Was this just spot sanding?", vbQuestion + vbYesNo)
        If ui1 = vbNo Then
            srvsel = "SD "
        Else
            srvsel = "[SD] "
        End If
    Else
        sp = InStr(serv_str, srvtext)
        sp = Mid(serv_str, sp - 1, 1)
        If sp = "[" Then
            srvsel = "[SD] "
        Else
            srvsel = "SD "
        End If
    End If
    btn_svcformat srvtext, srvsel, serv_btn, ultrigger
End Sub
Sub btn_hs()
    'Stop
    Dim srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean
    srvtext = "Hand Shovel"
    srvsel = "HS "
    serv_btn = "srv_btn_6"
    btn_svcformat srvtext, srvsel, serv_btn, ultrigger
End Sub
Sub btn_pt()
    Stop
    Dim srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean
    srvtext = "Patrol"
    srvsel = "PT "
    serv_btn = "srv_btn_7"
    btn_svcformat srvtext, srvsel, serv_btn, ultrigger
End Sub

Sub btn_svcformat(ByRef srvtext As String, srvsel As String, serv_btn As String, ultrigger As Boolean)
    mbevents = False
    'Stop
    ws_gui.Unprotect
    If ws_gui.Shapes(serv_btn).Fill.ForeColor = vbWhite Then
        'MsgBox wttext
        With ws_gui.Shapes(serv_btn)
            .Fill.ForeColor.RGB = RGB(207, 244, 234)  'grey-blue
            .Line.Weight = 0.75
            .Line.ForeColor.RGB = RGB(112, 163, 192) 'Palette C4R4
        End With
        serv_str = serv_str & srvsel
        If InStr(srvsel, "ST") Then
            With ws_gui.Range("AE9:AG9")
                .Interior.Color = RGB(216, 241, 234)
                .Locked = False
            End With
        End If
        If InStr(srvsel, "SD") Then
            With ws_gui.Range("AH9:AJ9")
                .Interior.Color = RGB(216, 241, 234)
                .Locked = False
            End With
        End If
        ultrigger = True
    Else
        'MsgBox "Default"
        With ws_gui.Shapes(serv_btn)
            .Fill.ForeColor.RGB = vbWhite
            .Line.Weight = 0.25
            .Line.ForeColor.RGB = vbBlack
            If Len(serv_str) > 0 Then
                ix = Len(srvsel) - 1 'length of button value8
                str_ix = Left(srvsel, ix)
                sp = InStr(serv_str, str_ix)
                str_d = Mid(serv_str, sp + ix, 1)
                str_d2 = str_ix & str_d
                serv_str = Replace(serv_str, str_d2, "")
                serv_str = Replace(serv_str, "  ", " ")
                If serv_str = " " Then serv_str = ""
            End If
        End With
        If InStr(srvsel, "ST") Then
            With ws_gui
                .Range("AH9") = 0
                .Range("AI9") = 0
                With .Range("AH9:AJ9")
                    .Interior.Color = RGB(223, 227, 229)
                    .Locked = True
                End With
            End With
        End If
        If InStr(srvsel, "SD") Then
            With ws_gui
                .Range("AE") = 0
                .Range("AF") = 0
                With .Range("AE9:AG9")
                    .Interior.Color = RGB(223, 227, 229)
                    .Locked = True
                End With
            End With
        End If
        ultrigger = False
    End If
    
    With ws_gui
        '.Unprotect
        .Range("AJ24") = serv_str
        'MsgBox wthr_str & " (" & Len(wthr_str) & ")"
        .Protect
    End With
    mbevents = True
End Sub

With just one working range (working range 1), this code, although not pretty or efficient, works OK. The anticipated problem comes not when I wish to to be able to copy the working range with its buttons and paste it as a new working range (working range 2 - 12). If I copy and paste traditionally, the shape names I believe copy over identically as well. So now, as an example, btn_srv_1 is duplicated. If I were to do this 11 times, I would have 12 instances of btn_srv_1.

I need to figure out a vba solution of copying and pasting and renaming each of it's components uniquely. I still need access to the previous buttons as they were originally presented. But I'm not sure how to use code to identify and apply common macros to a growing list of buttons.

One idea I had was to copy and paste the remaining working ranges (12 in total), manually rename all 77 grouped objects and their components uniquely, perhaps btn_srv_1-1, btn_serv_2-1 etc, and then "hide" the unused working ranges until a need to expose them. But this would mean then I need to write an additional 77 unique macros each referencing a unique "serv_btn" variable. I think that would work basically, but I would love to learn how to streamline it.

Thank you all in advance for reading this, and pondering over a solution and sharing your ideas. I am grateful.