Shape.OnAction Does Not Include Underscore in Sheet Name Causing Error After Copying Workbook

Question

I have a workbook that is used as a template for others. The template document is locked for editing, so the end users have to 'Save As' with the specifics for their project.

This template includes multiple sheets to incorporate all the possible layout options. Since most of the folks I work with are neither experienced nor comfortable with Excel, I have hidden all of the sheets. They first navigate to the BASE_Start sheet where they enter key information to determine which sheets should be unhidden based on the needs of their project. After entering the key information, the users click an Activate button(Form Control) that uses a macro, (BaseTabSelection), to unhide the sheets that are needed for their current project.

When the user tries to use the Activate button in the newly saved workbook, it still refers to the template workbook. To correct this, I have added the following subs (which I did not compile):

Private Sub Worksheet_Activate()
    Call ShapeUpdate
End Sub

Private Sub ShapeUpdate()
Dim Shp As Shape
Dim MacroLink As String
Dim SplitLink As Variant
Dim NewLink As String

For Each Shp In ActiveSheet.Shapes
    MacroLink = Shp.OnAction
    If MacroLink <> "" And InStr(MacroLink, "!") <> 0 Then
        SplitLink = Split(MacroLink, "!")
        NewLink = SplitLink(1)
        If Right(NewLink, 1) = "'" Then NewLink = Left(NewLink, Len(NewLink) - 1)
    End If
Next Shp

End Sub

I have used this code with success in many other workbooks, but it isn't working as expected in this workbook. The sheet name with the Activate button contains an underscore BASE_Start, but the underscore isn't included in the MacroLink variable that is assigned using Shp.OnAction. Code and watch window showing MacroLink was assigned using incorrect tab name

The expected MacroLink variable value: [ActiveWorkbook]!BASE_Start.BaseTabSelection
The actual MacroLink variable value: [ActiveWorkbook]!BASEStart.BaseTabSelection

Does anyone know why the underscore in the sheet name would be ignored/removed in this case?

I have been able to create a workaround to add the underscore using the code below. However, this solution is limited to this workbook because the sheet name is known and shouldn't change.

If InStr(NewLink, "BASEStart") Then NewLink = Replace(NewLink, "BASEStart", "BASE_Start")

Answer 1

The expected MacroLink variable value: [ActiveWorkbook]!BASE_Start.BaseTabSelection The actual MacroLink variable value: [ActiveWorkbook]!BASEStart.BaseTabSelection

This usually happens when the sheet name and the code name are different. I believe that BASE_Start is your sheet name and the code name of the worksheet is BASEStart.

Go to VBE and change the Sheet Codename from there and then try it again :)

I believe this is similar to what it looks now for you.

Change from here