Excel VBA: Limiting Workbook to local Macro

Question

I've written a code that duplicates two spreadsheets to a new workbook, with macros imbedded into the sheets, however, the buttons that call the macros are still calling the original workbook, and not the duplicated macros in the new workbook. This new workbook will be sent to other people who do not have access to the original, so my question is, is there a way to automatically update the buttons to call the macro in the new workbook, and how would I go about doing this?

The export code is shown below:

Sub ExportPropWB()
    Dim a As String, b As String, c As String, Mainbook As Workbook, info As Range, FileExtStr As String, FileFormatNum As Long, foldername As String, datestring As String, Newbook As Workbook, info1 As Range, info2 As Range, paste1 As Range, paste2 As Range
    Set Mainbook = Application.ThisWorkbook
    a = Mainbook.Worksheets("Application").Range("Client")
    b = Mainbook.Worksheets("Application").Range("ProjectName")
    datestring = Format(Now, "yyyy-mm-dd")
    c = datestring & " - " & a & " Proposal " & b
    foldername = Mainbook.Path & "\proposal\"

    Application.ScreenUpdating = False

    Mainbook.Worksheets("Equipment").Copy
    Set Newbook = Application.ActiveWorkbook

    Set info1 = Mainbook.Worksheets("equipment").Range("A1:AU343")
    Set paste1 = Newbook.Worksheets("equipment").Range("A1:AU343")

    paste1.Value = info1.Value

    Mainbook.Worksheets("Proposal").Copy after:=Newbook.Worksheets("Equipment")

    Set info2 = Mainbook.Worksheets("Proposal").Range("A1:AG1480")
    Set paste2 = Newbook.Worksheets("Proposal").Range("A1:AG1480")

    paste2.Value = info2.Value

    FileExtStr = ".xlsm"
    xFile = foldername & c & " " & FileExtStr
    Newbook.SaveAs Filename:=xFile, FileFormat:=52

    Application.ScreenUpdating = True  
End Sub

and the code the button is calling is as follows:

Sub HidePage02()
    With ThisWorkbook.Worksheets("Proposal").Shapes("Hide2").TextFrame2.TextRange.Characters
        If .Text = "Hide Page 2" Then
            .Text = "Show Page 2"
            Rows("75:148").Hidden = True
            ThisWorkbook.Worksheets("Proposal").Shapes("BHIP2").Visible = False
            Thisworkbook.worksheets("Proposal").Range("Page2") = "Yes"
        Else
            .Text = "Hide Page 2"
            With ThisWorkbook.Worksheets("Proposal").Shapes("Hide2")
                Rows("75:148").Hidden = False
                Thisworkbook.worksheets("Proposal").Range("Page2") = "no"
                ThisWorkbook.Worksheets("Proposal").Shapes("BHIP2").Visible = True
            End With
        End If
    End With
End Sub

Thank you for your time and assistance.

ThisWorkbook refers to the workbook containing the macro so you need to change the reference or add a parameter to your sub. — SJR
SJR: The code should only be referring to the workbook that contains it, the issue is that when the sheets are copied, the buttons are still trying to access the macro in the original workbook, even though those macros are duplicated into the new book. — GWG
OK I see now. Are both workbooks open when you run the code? If not, can you close the original and check what is going on then? — SJR
When I close the original book and click the button on the new book, it reopens the original book and activates the button there — GWG
I think what happens that your button does not change the assignment to the original macro - try reassigning it the macro in the new workbook (using OnAction). Shout if you get stuck with that. — SJR

GWG GWG · Accepted Answer · 2017-05-11T14:08:03

Answer provided by SRJ:

With ThisWorkbook.Worksheets("Proposal")
    .Shapes("Hide2").OnAction = "Sheet22.HidePage02"
End With

This code updates the shape to the new macro, where Proposal is the worksheet, Hide2 is the name of the object, and "Sheet22.HidePage02" is the local macro name

Excel VBA: Limiting Workbook to local Macro

1 Answers