0

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?

heyhey33
  • 29
  • 8
  • 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 Sep 09 '15 at 15:25

1 Answers1

1

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.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Hi there, ok makes sense. I put the SpellCheckUnlockedCells() into the sheet instead of a regular module. I put the button onto the sheet and assign the macro Sheet2.SpellCheckUnlockedCells(). When I click on OK it will assign main_excel.xlsm!Sheet2.SpellCheckUnlockedCells. When I open the exported sheet, is will link back to the original excel file. – heyhey33 Sep 10 '15 at 09:09
  • BTW: The new file does have the macro included if I check in the VBA editor. However, the sheet is now Sheet8 instead of Sheet2. But that doesn't really matter as long as the button is link to the old file... – heyhey33 Sep 10 '15 at 09:21
  • @heyhey33 You will need to ise an ActiveX command button, not a Form Control button. – Excel Hero Sep 10 '15 at 09:46
  • Perfect! Thanks you made my day ;) – heyhey33 Sep 10 '15 at 13:32
  • I ran into another issue. The password for unprotecting the sheet is plain text in the code. Obviously I can manually protect the VBA code in the editor property windows. However, I need to do this automatically with VBA. Is this possible? – heyhey33 Sep 10 '15 at 14:37
  • @heyhey33 That's a tough one. Read [this](http://stackoverflow.com/questions/17777770/vba-project-password-protect-with-sendkeys-not-working-correctly). In the end, even if you get it working, VBA Project password protection is not secure for someone who knows what they are doing. If you are after a certain degree of difficulty for the end user to retrieve the password over and above clear text in the VBA code, then you could try an encryption approach. Leave the password text in the code, but have the character encrypted and then have the code decrypt before unprotecting the sheet. – Excel Hero Sep 10 '15 at 14:59