How to Assign a Macro to a Form Button, Using VBA

June 5, 2023

I am attempting to create a "Export Button" the button currently creates a new workbook with specific sheets from the existing one, unhides them and inside one of these sheets contains a button. This button is named Export Email Button. It is macro-assigned to the current workbook using a sheet that is always copied/exported, this macro is named ExportEmail() Once I copy these sheets, select the button and change the OnAction, nothing seems to be updating.

Sheets(mySheets).Copy
ActiveWorkbook.Worksheets("ExportEmail").Visible = xlSheetVisible

Dim ExternalLinks As Variant
Dim x As Long
'Purpose: To break external links
ExternalLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
For x = 1 To UBound(ExternalLinks)
    ActiveWorkbook.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x

'Here the issue lays
ActiveWorkbook.Worksheets("ExportEmail").Buttons("Export Email Button").OnAction = "NewMacro"
'New button is assigned to "'Names Update Master.xlsm'!NewMacro"

ActiveWorkbook.SaveAs myFolder & NewName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close savechanges:=False

UPDATE

' "'" & NewName & ".xlsm'" - This should return "'June 2023.xlsm'", but instead turns "'C:/June 2023.xlsm'"
ActiveWorkbook.Worksheets("ExportEmail").Buttons("Export Email Button").OnAction = "'" & NewName & ".xlsm'!Sheet4.ExportEmail"

>Solution :

If you want to assign the macro to the new created workbook, add the workbook-name to the macro, separated with an exclamation mark. It doesn’t matter if you later save the file with a different name, Excel will update the OnAction-method automatically.

However, I see another issue here: When you create a new workbook by copying a sheet, only the macro within the sheet module is copied. So if you want to copy the NewMacro-routine, you should put it into the sheet module of ExportEmail. In that case, you need to call it by preceding the codeName of the sheet (plus a dot). Note that the codename of a sheet is not the sheet name itself.

Try the following (note that I have used variables for the new workbook and -sheet).

ThisWorkbook.Sheets(mySheets).Copy
Dim newWB as Workbook, newWS as Worksheet
set newWB = ActiveWorkbook
set newWS = newWB.Sheets(1)
newWS.Visible = xlSheetVisible

Dim ExternalLinks As Variant
Dim x As Long
'Purpose: To break external links
ExternalLinks = newWB.LinkSources(Type:=xlLinkTypeExcelLinks)
For x = 1 To UBound(ExternalLinks)
    NewWB.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x

' This should result in something like "Book2!Sheet1.NewMacro"
newWs.Buttons("Export Email Button").OnAction = newWB.Name & "!" & newWS.codeName & ".NewMacro"

newWB.SaveAs myFolder & NewName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
newWB.Close savechanges:=False