Export sheet including VBA and a command button

Question

I need to save several Excel sheets to separate files. The sheets are protected and locked, however I want to make spellcheck available. This is possible with VBA using a small routine to unlock>spellcheck>relock

Sub SpellCheck()
ActiveSheet.Unprotect
Cells.CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=1033
ActiveSheet.Protect
End Sub()

Now I placed command button on the sheets I want to export and assigned my spell check macro. I save the files with vba as XLSM

Sheets("exportsheet").SaveAs Filename:="mysheet.xlsm", FileFormat:=52

If I click on the button in the newly saved file, the macro is linked to the original source excel which will open. The assigned macro link looks something like this: original_excel.xlsm!spellCheck()

How can I export a sheet including the VBA code which is assigned to a command button in a way that the macro is not assigned to the original workbook.

Any thoughts on that?

I suspect that the SpellCheck() subroutine is placed in a standard code module? When you save that particular sheet as a new workbook, Excel has no choice but to link back to that procedure in the standard code module. What happens if you instead place the SpellCheck() routine in the worksheet module and then save that sheet as a new file? — Excel Hero

Excel Hero Excel Hero · Accepted Answer · 2015-09-09T15:38:09

If you want the worksheet to be self contained after you export it from the workbook, make it self contained to start with.

Place all routines that are accessed from the sheet into that sheet's code module (instead of into a shared standard code module).

This way the sheet has no dependencies and will be self contained once exported to a new workbook.

Export sheet including VBA and a command button

1 Answers